Reading a text file with SQL Server

Question

I am trying to read in a text file from an SQL query (SQL Server 2005) but am not having any luck at all. I've tried various things with EXEC and xp_cmdshell, but all aren't working. This is the general way I've tried to approach this:

CREATE TABLE temp (data varchar(2000));
INSERT temp EXEC master.dbo.xp_cmdshell 'type file.txt';

I then try to select data from the temp table. I've searched around a lot and I can't tell what I'm getting wrong. Help?

Answer 1

Do you need to do this once, or as part of normal database operation (ie, in response to a trigger, scheduled event, etc)?

Either way, you're better off creating an SSIS package.

1. In SQL Management Studio, right-click the database.
2. Select Tasks|Import Data...
3. Follow the wizard instructions. Select "flat text file" as the provider when prompted.

I haven't used dbo.xp_cmdshell (per your example), but I imagine that capturing the output is the issue.

Answer 2

What does your text file look like?? Each line a record?

You'll have to check out the BULK INSERT statement - that should look something like:

BULK INSERT dbo.YourTableName
FROM 'D:\directory\YourFileName.csv'
WITH
(
  CODEPAGE = '1252',
  FIELDTERMINATOR = ';',
  CHECK_CONSTRAINTS
) 

Here, in my case, I'm importing a CSV file - but you should be able to import a text file just as well.

From the MSDN docs - here's a sample that hopefully works for a text file with one field per row:

BULK INSERT dbo.temp 
   FROM 'c:\temp\file.txt'
   WITH 
      (
         ROWTERMINATOR ='\n'
      )

Seems to work just fine in my test environment :-)