Reading A Text File With Sql Server

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 workin

Solution 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!

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

Solution 3:

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.

Solution 4:

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