42

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?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Markus O'Reilly
  • 811
  • 3
  • 9
  • 15

4 Answers4

62

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!

Cain
  • 918
  • 6
  • 9
46

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

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • See also https://stackoverflow.com/a/35391521/ - I had to change \n to 0x0a to get it work (nobody knows why) – M.M Apr 29 '22 at 05:14
1
BULK INSERT dbo.temp 

FROM 'c:\temp\file.txt' --- path file in db server 

WITH 

  (
     ROWTERMINATOR ='\n'
  )

it work for me but save as by editplus to ansi encoding for multilanguage

Hadi
  • 36,233
  • 13
  • 65
  • 124
udorb b
  • 135
  • 3
0

if you want to read the file into a table at one time you should use BULK INSERT. ON the other hand if you preffer to parse the file line by line to make your own checks, you should take a look at this web: https://www.simple-talk.com/sql/t-sql-programming/reading-and-writing-files-in-sql-server-using-t-sql/ It is possible that you need to activate your xp_cmdshell or other OLE Automation features. Simple Google it and the script will appear. Hope to be useful.

Rodrigo
  • 1