Bulk Insert Problem

Hi

I am trying to do a bulk insert using Query Analyzer for SQL 2000.

I have a Comma delimited file and a format file that i genrated using bcp.exe

the CSV file looks like this

08009700000,23,01,2007,23012007,16:28:09,01413412066,01,FreeConnect,00:00:05,0:05,0.083,0.002,2006,X
08009700000,24,01,2007,24012007,12:51:55,01413412066,01,FreeConnect,00:00:14,0:14,0.233,0.006,2006,X
08009700000,24,01,2007,24012007,12:52:28,01413412066,01,FreeConnect,00:00:10,0:10,0.167,0.004,2006,X

the format file look like this

8.0
15
1 SQLNCHAR 2 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 2 510 "," 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 2 510 "," 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 2 510 "," 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 2 510 "," 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 2 510 "," 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 2 510 "," 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 2 510 "," 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 2 510 "," 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 2 510 "," 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 2 510 "," 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 2 510 "," 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 2 510 "," 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 2 510 "," 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 2 510 "," 15 Field15 Latin1_General_CI_AS

and the T-SQL code that i am using is

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt')

i have also tried

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r\n')

and

bulk insert RawCallData from 'C:\Temp\85005313_870.csv'
with
(FORMATFILE ='c:\Temp\yourcomms.fmt',ROWTERMINATOR = '\r')

what ever i do i get the get the follow error message

Server: Msg 4866, Level 17, State 66, Line 1
Bulk Insert fails. Column is too long in the data file for row 1, column 1. Make sure the field terminator and row terminator are specified correctly.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

can any one help ?

I haven't read through your post completely, because I'm too busy today. But...

In Enterprise Manager (or Management Studio), use the 'Import Data' menu entry to go through a wizard to import your data. Use the option to save the package, or in MgtStudio, script it out. Then you can re-use the package/script when you need to. But hopefully you will find the answer to your problem when you step through the wizard and it tells you useful information like "I can't find the end of the line 10 lines in...".

Actually - having skimmed through, it sounds like it can't find the commas properly, or else your columns are too short. Probably the first though, because I imagine you would've noticed if your columns were short. The wizard should really help you here.

Hope this helps,

Rob|||

hi and thanks for the reply.

I have been playing around with this and i have now got round the error that i was getting.

The problem seems to be in the format file that i am using, which is odd cause it was genarated using the bcp.

I found 2 problems in this file

the first which caused the error about the first column being two long was the column prefix was set to 2, when i changed this to 0 for each column the error went away.

then then got a new error about unexpected end of line, I solved this buy removing the comma on the last line of the format file and replaceing it with \r\n

so my format file now looks like this

8.0

15
1 SQLNCHAR 0 510 "," 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 "," 2 Field2 Latin1_General_CI_AS

...

15 SQLNCHAR 0 510 "\r\n" 15 Field15 Latin1_General_CI_AS

it now imports all the data, however it also seems to corrupt the data, if i do a select statement in QA the data looks like it is in the wrong font as it is all white empty boxes.

any one know why this happens ?

|||Dagz,

You got two of three issues answered, it looks like (changing the 2 to 0 and changing the final field terminator). If your file is Unicode, though, which I'm guessing it is from the SQLNCHAR in the format file, I think you need to specify the field terminators differently. Try this as the format file (or try SQLCHAR instead of SQLNCHAR, if your file is not a Unicode file):

8.0
15
1 SQLNCHAR 0 510 ",\x00" 1 Field1 Latin1_General_CI_AS
2 SQLNCHAR 0 510 ",\x00" 2 Field2 Latin1_General_CI_AS
3 SQLNCHAR 0 510 ",\x00" 3 Field3 Latin1_General_CI_AS
4 SQLNCHAR 0 510 ",\x00" 4 Field4 Latin1_General_CI_AS
5 SQLNCHAR 0 510 ",\x00" 5 Field5 Latin1_General_CI_AS
6 SQLNCHAR 0 510 ",\x00" 6 Field6 Latin1_General_CI_AS
7 SQLNCHAR 0 510 ",\x00" 7 Field7 Latin1_General_CI_AS
8 SQLNCHAR 0 510 ",\x00" 8 Field8 Latin1_General_CI_AS
9 SQLNCHAR 0 510 ",\x00" 9 Field9 Latin1_General_CI_AS
10 SQLNCHAR 0 510 ",\x00" 10 Field10 Latin1_General_CI_AS
11 SQLNCHAR 0 510 ",\x00" 11 Field11 Latin1_General_CI_AS
12 SQLNCHAR 0 510 ",\x00" 12 Field12 Latin1_General_CI_AS
13 SQLNCHAR 0 510 ",\x00" 13 Field13 Latin1_General_CI_AS
14 SQLNCHAR 0 510 ",\x00" 14 Field14 Latin1_General_CI_AS
15 SQLNCHAR 0 510 "\x0D\x00\x0A\x00" 15 Field15 Latin1_General_CI_AS

Steve Kass
http://www.stevekass.com
sql