Annoying behavior importing csv file into SQL Server

Question

Annoying behavior importing csv file into SQL Server

query_squidier

Ten Centuries

Points: 1390
More actions
July 14, 2005 at 11:53 am

#88929

Hi all.
This is driving me absolutely insane.
I have a comma-delimited text file. The first row is a header row and there are no spaces around each comma. However, starting with the first data row, each comma contains multiple spaces around it.
I cannot import this file completely/successfully.
If I use BULK INSERT with FIELDTERMINATOR = ' , ' I'm able to import all of the data except the first data row. Someone in the past told me this was because of the lack of spaces around the commas in the header row versus the data rows. Completely infuriating.
If I use INSERT INTO SELECT FROM FROM OPENROWSET('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=C:\;', 'select * from test.csv')z SQL Server sets the data type of each field before attempting to insert the data into a precreated table. One very annoying side effect of this is that I have a one character column (discounting the stupid spaces around each comma) that has mostly integer values except for one character "I". SQL Server data types this field on import to an INT (even though I explicitly set it to VARCHAR) and sets to NULL any values for this column that contain "I".
EXEC master..xp_cmdshell has the same behavior as BULK INSERT.
I'm about to scream. I do not want to use DTS to accomplish this import since I'd have to somehow pass it the name of the file via parameter(s).

This should not be this difficult. Somebody point out Bill Gates so I can glare at 'im. HELP!

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

query_squidier Ten Centuries Points: 1390 More actions · Answer 1

Also, is there any method you guys know of to strip just the header row from a text file? If I could do that (from an xp_cmdshell from within SQL Server) the INSERT INTO SELECT FROM OPENROWSET works.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 2

Have you tried adding FIRSTROW=2 to the BULK INSERT command? I don't have a system to try it on, but it might skip the header.

And then again, I might be wrong ...
David Webb

query_squidier Ten Centuries Points: 1390 More actions · Answer 3

That works great skipping the header row, but it also skips row 2 (the first data row). The import then starts on row 3.

David Webb-CDS SSCoach Points: 17398 More actions · Answer 4

Deja Vu!

This question has been asked here before. Here's a link to a thread with a script to strip the header line off a file.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=19&messageid=105041

HTH

And then again, I might be wrong ...
David Webb

query_squidier Ten Centuries Points: 1390 More actions · Answer 5

Thanks, Dave (do you mind if I call you Dave? )

I found a 16kb executable that allows for copying a file beginning at a specific point in the file. This'll work for me.

http://www.softtreetech.com/24x7/archive/44.htm

Thanks!

*kicks silly SQL Server* Shouldn't have to do this!

David Webb-CDS SSCoach Points: 17398 More actions · Answer 6

I agree. The 'FIRSTROW' setting should take care of this. I know it works in DTS, but it seems to have caused multiple people grief trying to do it the easy way.

And then again, I might be wrong ...
David Webb