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?
This question is related to
sql-server
sql-server-2005
xp-cmdshell
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
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!
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.
Source: Stackoverflow.com