CSV header rows - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

SSIS and Import/Export (2005)

CSV header rows

Author

Topic

hazydavy
Starting Member

3 Posts

Posted - 2008-05-30 : 19:46:31

Hello.

I am seeing some behaviour I don't understand. I can't believe it hasn't been discussed here before, but my searches have returned nothing.

Here it is.

I have a CSV file. Its first rows look like this:

# 3_results/ABC.csv
# Created from data/clean/
# OS Unix, host smac.local, user ge
col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13
38, 2008-05-05 14:35:05, 92,0,0,FFF,1,0,1,0,1,0,0
41, 2008-05-05 14:35:10, 4,1,0,GGG,1,0,0,1,0,0,0
75, 2008-05-05 14:35:26, 2,1,0,HHH,1,0,0,1,0,0,0
183, 2008-05-05 14:35:26, 4,1,0,III,0,0,0,0,0,1,0
347, 2008-05-05 14:35:28, 11,0,0,JJJ,0,0,0,0,0,1,0
365, 2008-05-05 14:35:28, 0,1,0,KKK,0,0,0,1,0,0,0
41, 2008-05-05 14:35:29, 18,0,0,LLL,1,0,0,1,0,0,0
286, 2008-05-05 14:35:30, 3,1,0,MMM,0,0,0,0,1,0,0

So, there are three lines of miscellaneous information, one line of column names, and the data to be imported begins on row 5.

So I import with this:

BULK INSERT tableName FROM 'file.csv'
WITH (FIELDTERMINATOR = ',',FIRSTROW = 5)

And the first line to be imported is the one beginning with 183!

This does what I want:

BULK INSERT tableName FROM 'file.csv'
WITH (FIELDTERMINATOR = ',',FIRSTROW = 2)

I can insert as many lines starting with # as I want and it works fine.

What is going on?

Thanks.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2008-05-30 : 23:47:34

It sounds like your CSV file doesn't have proper end of line characters, so it thinks line 183 is line 5. Have you viewed it with a hex editor?

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Database maintenance routines:
http://weblogs.sqlteam.com/tarad/archive/2004/07/02/1705.aspx

hazydavy
Starting Member

3 Posts

Posted - 2008-05-31 : 16:32:48

Yes, I thought about that. All lines end with hex 0D 0A characters, which is the normal MS-DOS Carriage Return / Line Feed sequence.

Further information: The header lines need not begin with # in order to be ignored.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-01 : 02:56:55

Have you tried with using FORMAT FILE option for BULK INSERT?

hazydavy
Starting Member

3 Posts

Posted - 2008-06-03 : 01:31:56

I have not used the FORMATFILE option. I guess I could, but it seems like overkill (and a maintenance hassle) to me.

What bugs me is that the solution seems to have an undocumented behaviour. Has no one else observed this?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources