October 14, 2009 at 11:37 am
when importing a CSV file ino SQL using a stored procedure with the following code:
SELECT *
INTO theImportTable
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=D:\;Extensions=CSV;',
'SELECT * FROM CSVFile.csv')
It makes the connection and reads the file but does not parse any of the fields. The result set is one column instead of 10 and the contents of the one column is all NULL values.
I have been searching Google trying to find a similiar issue and was unable to see any examples of the problem.
Any help would be greatly appreciated.
October 14, 2009 at 11:49 am
That looks correct. Have you verified that the file is correctly formatted?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
October 14, 2009 at 11:59 am
What type of file are you using? I know I had problems in the past, when trying to read tab delimited files, so here's what I've come up with:
I read somewhere that you need a schema file to read correctly and separate in column names:
SET @CMD = 'ECHO [' + @File + '] > ' + @Directory + 'Schema.ini'
EXEC MASTER.dbo.xp_CmdShell @CMD
SET @CMD = 'ECHO ColNameHeader=true >> ' + @Directory + 'Schema.ini'
EXEC MASTER.dbo.xp_CmdShell @CMD
SET @CMD = 'ECHO Format=TabDelimited >> ' + @Directory + 'Schema.ini'
EXEC MASTER.dbo.xp_CmdShell @CMD
SET @CMD = 'ECHO MaxScanRows=0 >> ' + @Directory + 'Schema.ini'
EXEC MASTER.dbo.xp_CmdShell @CMD
SET @CMD = 'ECHO CharacterSet=ANSI >> ' + @Directory + 'Schema.ini'
EXEC MASTER.dbo.xp_CmdShell @CMD
SET @CMD = 'SELECT *
INTO GICSPFImportMergeFile
FROM OPENROWSET(
''MSDASQL'',
''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=' + @Directory + ';'',
''SELECT *
FROM ' + @File + ''') '
PRINT @CMD
EXEC sp_executeSQL @CMD
I do mostly what you do, but I always create the schema.ini file in the same directory, I think the provider needs it to work correctly.
I gave you a sample you will have to fix for your needs, but it should get you going.
Hope that helps,
Cheers,
J-F
October 14, 2009 at 12:03 pm
Would this work?
BULK INSERT CSVTest
FROM 'c:\csvtest.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = ''
)
GO
----------------------------------------------------------------------------
Sacramento SQL Server users group - http://sac.sqlpass.org
Follow me on Twitter - @SQLDCH
----------------------------------------------------------------------------
October 14, 2009 at 12:10 pm
That was my original try was to use bulk insert, the previous note is where I need to go with the file because the bulk insert fails with the CSV format without a schema.
October 14, 2009 at 12:20 pm
This works for me:
SELECT * INTO Dbo.ICSV
FROM
OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DEFAULTDIR=F:\Testdata\;',
'SELECT * FROM CSVFile.csv')
SELECT * FROM dbo.ICSV
As far as I can tell all I did to your code that was meaningful was to eliminate the "Extensions=CSV;',". What I consider not meaningful was to change the location of the file to be imported to my hard drive location
October 15, 2009 at 3:28 pm
Thanks for the information that I liked the approach and got my process to work.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply