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

Just discovered this:

SELECT * FROM OPENROWSET(BULK N'<PATH_TO_FILE>', SINGLE_CLOB) AS Contents 

It'll pull in the contents of the file as varchar(max). Replace SINGLE_CLOB with:

SINGLE_NCLOB for nvarchar(max) SINGLE_BLOB for varbinary(max)

Thanks to http://www.mssqltips.com/sqlservertip/1643/using-openrowset-to-read-large-files-into-sql-server/ for this!

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 :-)