Reading a text file with SQL Server

sql-server, sql-server-2005, xp-cmdshell

Solution

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

Problem

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?

Original source

Related problems