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

User · Answer

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

User · Answer

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

User · Answer

Just discovered this   SELECT   FROM OPENROWSET BULK N  lt PATH TO FILE gt    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

User · Answer

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  -

[sql-server] Reading a text file with SQL Server

Examples related to sql-server

Examples related to sql-server-2005

Examples related to xp-cmdshell