BULK Insert problem with DateTime field

I am trying to perform a bulk insert in SQL Query analyzer. This is the Schema for my table:

Taxonomic_Units Table:

INT 4 tsn
CHAR 1 unit_ind1
CHAR 35 unit_name1
CHAR 1 unit_ind2
CHAR 34 unit_name2
CHAR 7 unit_ind3
CHAR 35 unit_name3
CHAR 7 unit_ind4
CHAR 35 unit_name4
CHAR 1 unnamed_taxon_ind
CHAR 12 usage
CHAR 50 unaccept_reason
CHAR 40 credibility_rtng
CHAR 10 completeness_rtng
CHAR 7 currency_rating
SMALLINT 2 phylo_sort_seq
DATETIME 8 initial_time_stamp
INT 4 parent_tsn
INT 4 taxon_author_id
INT 4 hybrid_author_id
SMALLINT 2 kingdom_id
SMALLINT 2 rank_id
DATETIME 4 update_date
CHAR 3 uncertain_prnt_ind

I use the following SQL Statement to BULK INSERT:

BULK INSERT itis.taxonomic_units
FROM '<dir path to input file>/taxonomic_units.txt'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n',
KEEPIDENTITY,
KEEPNULLS

)

Here is a sample of a row that I get an error when it is processed through the above BULK INSERT statement:

50||Bacteria||||||||invalid||No review; untreated NODC data|unknown|unknown||1996-06-13 14:51:08.0||||1|10|07/29/1996||

The error is:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).

Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.

llzamboni wrote:

Server: Msg 4864, Level 16, State 1, Line 1
Bulk insert data conversion error (type mismatch) for row 1, column 17 (initial_time_stamp).

Just to make things easier on anyone who tries to help me solve this problem, the field that causes my Bulk insert statement to choke contains the data: "1996-06-13 14:51:08.0". Why is this happening? Any thoughts on how to solve it? I have been scouring help articles all day with no resolution to this problem.

For me, that works just fine...

SELECT CAST('1996-06-13 14:51:08.0' AS DATETIME)

Are you *sure* that row is the problem?

|||

It seems like BULK INSERT is having difficulties with the (sort of) malformed date 1996-06-13 14:51:08.0

If you remove the last .0 or add two zeroes so it becomes .000 then BULK INSERT will insert the row.

However, if you instead use BCP, no changes are needed.
Apparently, BCP isn't as cranky as BULK INSERT in this case.

/Kenneth

bulk insert problem - invalid collation name

Hi,
I keep getting an error when trying to bulk insert a flat file from
query analyzer using a format file.
Here is a sample of the flat file:
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxx",14,"KK","Inside
Twin, Shower","","","",993,"009"
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxxxx",14,"LL","Inside
Twin, Shower","","","",993,"004"
"N435",2004-08-31,"BHX","Palma","PMI","Mediterranean","xxxxxxxxxxxxxxxxxx",14,"MM","Inside
Twin, Shower","","","",993,"001"
Here is my format file:
8.0
16
1 SQLINT 0 4 "" 1 ID ""
2 SQLCHAR 0 255 "\"," 2
Cruise ""
3 SQLCHAR 0 255 ",\"" 3
Departure_Date ""
4 SQLCHAR 0 255 "\",\"" 4
Airport ""
5 SQLCHAR 0 255 "\",\"" 5
Resort ""
6 SQLCHAR 0 255 "\",\"" 6
Resort_Code ""
7 SQLCHAR 0 255 "\",\"" 7
Region ""
8 SQLCHAR 0 255 "\"," 8
Voyage_name ""
9 SQLCHAR 0 255 ",\"" 9 Duration ""
10 SQLCHAR 0 255 "\",\"" 10 Cabin_Grade
""
11 SQLCHAR 0 255 "\",\"" 11 Cabin_Type ""
12 SQLCHAR 0 255 "\",\"" 12 Hotel_Name ""
13 SQLCHAR 0 255 "\",\"" 13 Tour_definition
""
14 SQLCHAR 0 255 "\"," 14 Hotel_Grade ""
15 SQLCHAR 0 255 ",\"" 15 Price ""
16 SQLCHAR 0 255 "\"\n" 16 Available_Quantity
""
The first column id is not present in the flat file but even if i dont
include this column i still get errors. Any help appreciated.
AllyAlison,
I believe that it is saying that it does not like the collation of "".
Try replacing the "" collation with nothing at all if you want to try that
approach. Or include a collation such as SQL_Latin1_General_Cp1_CI_AS.
Note that the collation is not placed inside of quotes.
Russell Fields
> Here is my format file:
> 8.0
> 16
> 1 SQLINT 0 4 "" 1 ID
> 2 SQLCHAR 0 255 "\"," 2
> Cruise SQL_Latin1_General_Cp1_CI_ASsql

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

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