Need Bulk Insert help

Hi all,
I am not sure I post this in the correct forum or not, if not please reguide me to the correct forum. I have data files created from streamwriter using tab for field terminator and line break from streamwriter. Then I try to use Bulk Insert to load the data file into a table with format file using bcp command. Then I received the following error:

Msg 4863, Level 16, State 4, Line 1
Bulk load data conversion error (truncation) for row 1, column 1 (codeNum).
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

the bulk insert query is as follows:

BULK insert revrpt_staging_2.dbo.outgoing
from '<data file path>\<filename>.data'
WITH (
FIELDTERMINATOR = '\t',
FIRSTROW = 1,
FORMATFILE='<format file path>\format1.fmt',
ROWTERMINATOR = '\r\n',
KEEPIDENTITY,
KEEPNULLS
);

using format file as shown below:
9.0
11
1 SQLINT 1 4 "\t" 2 codeNum ""
2 SQLINT 1 4 "\t" 3 code2Num ""
3 SQLINT 1 4 "\t" 4 code3Num ""
4 SQLINT 1 4 "\t" 5 code4Num ""
5 SQLNCHAR 2 30 "\t" 6 messageId SQL_Latin1_General_CP1_CI_AS
6 SQLNCHAR 2 24 "\t" 7 phone SQL_Latin1_General_CP1_CI_AS
7 SQLNCHAR 8 0 "\t" 8 message SQL_Latin1_General_CP1_CI_AS
8 SQLDATETIME 1 8 "\t" 9 recDateTime ""
9 SQLDECIMAL 1 19 "\t" 10 chargeAmt ""
10 SQLNCHAR 2 4 "\t" 11 eStatus SQL_Latin1_General_CP1_CI_AS
11 SQLNCHAR 2 60 "\r\n" 12 dStatus SQL_Latin1_General_CP1_CI_AS

below is 1 row of the data:
1000 4 12345 0 8EDBDEBF 10111111111 Free msg. Call us at xxxxxxxx, Mon - Fri, 10am-5pm 04/25/2006 17:04:45 0 0 1
Anyone have any idea what is wrong? and how should I go about fixing it? Please help. Thanks in advance.
Daren

I believe you should set prefix length field to 0, as you don't have a prefix:

1 SQLINT 0 4 "\t" 2 codeNum ""

BTW, Books Online "Using Format Files" article is a good read on this matter.

|||Hi Yaroslave,
Sorry to bother you, but could you redirect me to the exact link that explains the meaning of the format files, like explain 1 represents the column number declared, SQLINT represents the type of the column, 0 represent the prefix, etc. for the following example:

1 SQLINT 0 4 "\t" 2 codeNum ""

And one more thing, I changed the prefix to 0 for all the rest and rerun the command, I got an error on the 4th one, which is code4Num the data for it is 12345. Any idea what is wrong with it?
Daren
|||Hi Yaroslave,
the exact error for it is "Bulk load data conversion error (overflow)".
Daren
|||Hi Yaroslave,
sorry to bother you, but it still cannot insert into the table, the following errors pop up, I have no idea what to do with it. The error is as follows:
Msg 4866, Level 16, State 7, Line 1

The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 1
The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 1
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

please help. Thanks in advance.
Daren
|||

It does seem like the bulk insert is having objections about the separators in the file. Are you sure that you have tabs in there?

fwiw, I've always used another strategy regarding formatfiles. (though only with bcp, but I believe that it'd work the same with bulk insert)

The idea is that no matter the datatypes in the destination table, the source is just a plain ascii file, so specify all columns in the format file as SQLCHAR, all prefixes as zero, and the length as the actual charachter length, not the bytelength for the datatype. (ie a datetime becomes 26 instead of 8 etc)

An easy method to create the formatfile, is to use bcp without specifying the -c and -t parameters. You'll then be prompted for the format on each column, 4 prompts on each.

For the first, if the suggestion is anything else than [char], type in 'char' at the prompt, if it is [char], press enter.
For the second, always type 0 (zero)
For the third, press <enter>, always accept the suggested length
For the fourth, type your delimiter. (ie /t)

Repeat for all columns in the table until the last one, where you enter the rowdelimiter at the last (4th) prompt.

Save the file when prompted.

When this work is done, you have a formatfile that you can use, should there be any typos in it (could happen =;o) it's easy to just open it and edit where necessary.

I've used this method since 6.x days, and it has never failed. (ie all CHAR types, all prefixes as zero, suggested lenght, and the appropriate delimeter)

Hope it helps some.

=;o)
/Kenneth

|||Hi Kenneth,
I tried using the method you said, I use the following command:

bcp <tablename> format test.fmt -T

first prompt: char
second prompt: 0
third prompt: <press enter>
fourth prompt: \t for all except last which is \r\n

but the following error came out:
SQLState = HY000, NativeError = 0
Error = [Microsoft][SQL Native Client]Format file could not be opened. Invalid n
ame specified or access denied.
what do I do about it? Thanks in advance.
Daren
|||Sorry Kenneth, I fixed my previous post with the following command:

bcp <tablename> format nul -T -f test.fmt

I try to bulk insert the data file into the table using this format file, the following error came up for a nvarchar column:

Msg 4864, Level 16, State 1, Line 1

Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 1, column 2 (<column name>).

Any idea on this? Thanks again for helping me out.
Daren
|||Sorry Kenneth,
My mistake again, I use unicode file instead of ascii that bring up the error I posted previously. I change to use ascii file then it works! Thanks again Kenneth, you saved me a lot of time wondering around.
Daren
|||

Glad it worked out for you. =:o)

/Kenneth