var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: Question regarding bulk insert with format file
I have a test.csv file which containing the following:
I'm using the tst.txt file as the format file:
1 SQLCHAR 0 6 ",\"" 1 empid SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 0 50 "\"," 2 name SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 0 10 "\r\n" 3 phone SQL_Latin1_General_Cp437_BIN
Now when I ran the following command in sql query analyzer:
BULK INSERT test
FIRSTROW = 2,
FORMATFILE = 'c:\tst.txt'
Only the last two rows got inserted. The first row below the header was ignored.
I've tried with different files, it all gave me the same result. Anybody seen this before? What am I missing?
FIRSTROW=1 ? did you try that ?
Yes. Then I got column mismatch error.
If I change column header Name to "Name", then everythign works fine.
So using format file, the bulk insert utility will read the column header and first row as line 1.
Is there a work around for this?
BULK INSERT [test]
WITH (FIRSTROW = 2, FIELDTERMINATOR = ',')
Did you try this instead of format file
Yes. If I don't use a format file, then the double quote field won't be inserted correctly.
Last edited by JustLearnSQL; 02-05-09 at 15:40.
did you try with BCP from xp_cmdshell or command line
BCP databasename..test in c:\test.txt -T -q -t, -c -Sservername -F2
may also have to add -r \n option too.