question

SmitaKundargi-4533 avatar image
0 Votes"
SmitaKundargi-4533 asked EchoLiu-msft edited

Bulk Insert using format file the row number of incorrect record not displayed

I need to use a bulk insert using format file. On incorrect data file I get the error - "There will be truncation error" But if I use bulk insert without the format file I get the exact row number of incorrect records. Why using a format file I dont get the row number of the incorrect rows.

Example: file1.txt has incorrect rows

BULK INSERT table1
FROM 'D:\data\file1.txt'
WITH
( FORMATFILE = 'D:\data\file1.fmt',
DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
Error - Msg 8152, Level 16, State 14, Line 1 String or binary data would be truncated. The statement has been terminated.

Without format file -

BULK INSERT table1
FROM 'D:\data\file1.txt'
WITH
( DATAFILETYPE = 'char',
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'
);
Msg 4863, Level 16, State 1, Line 1 Bulk load data conversion error (truncation) for row 2, column 5

sql-server-transact-sql
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

It seems that your format file does not match the data in the file.

To help you further we need:
1) The CREATE TABLE statement for the table.
2) A sample data, big enough to illustrate the problem.
3) The format file.

Note that we want the data file as an attachment, as we need to see the exact bytes.

0 Votes 0 ·
TomPhillips-1744 avatar image
1 Vote"
TomPhillips-1744 answered SmitaKundargi-4533 commented

You didn't post D:\data\file1.fmt. The results you are getting are almost always related to not properly defining the row terminator in the format file.

These are ignored when using a format file:
FIELDTERMINATOR = '|',
ROWTERMINATOR = '\n'

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thank you. Yes my format file does have ROWTERMINATOR = '\n' and delimiter | .. How do we work our way around it. ?

0 Votes 0 ·
SmitaKundargi-4533 avatar image
0 Votes"
SmitaKundargi-4533 answered

The format file looks fine, as I was able to import records with correct file. I am unable to share as attachment, sorry about it.

I observed incase of incorrect data, it skipped the incorrect record and loaded other records. This is not what I expect. I want helpful error like which row failed which column.

CREATE TABLE [table1](
[col1] [char](4) NOT NULL,
[col2] [varchar](2) NULL,
[col3] [varchar](30) NULL,
[col4] [varchar](4) NULL,
[col5] [varchar](12) NULL,
[col6] [char](1) NULL
) ON [PRIMARY]
GO

format file
9.0
6
1 SQLCHAR 0 200 "|" 1 col1 SQL_Latin1_General_CP1_CI_AS
2 SQLCHAR 0 200 "|" 2 col2 SQL_Latin1_General_CP1_CI_AS
3 SQLCHAR 0 200 "|" 3 col3 SQL_Latin1_General_CP1_CI_AS
4 SQLCHAR 0 200 "|" 4 col4 SQL_Latin1_General_CP1_CI_AS
5 SQLCHAR 0 300 "|" 5 col5 SQL_Latin1_General_CP1_CI_AS
6 SQLCHAR 0 200 "\r\n" 6 col6 SQL_Latin1_General_CP1_CI_AS

Able to load data : working fine

111|AA|sample text 1|O|N|
222||sample text 2|S|N|

Incorrect data
111|AA|sample text 1|O|N|
222||sample text 2|S|N|
333|BBdddddddddd|sample text 3|O|N|

Query used :


BULK INSERT [table1] FROM 'D:\correctdata.TXT'
WITH

 ( FORMATFILE = 'D:\file1.FMT',
 DATAFILETYPE    = 'char', 
    FIELDTERMINATOR = '|', 
    ROWTERMINATOR   = '\n' 
 );
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

ErlandSommarskog avatar image
1 Vote"
ErlandSommarskog answered

I observed incase of incorrect data, it skipped the incorrect record and loaded other records. This is not what I expect. I want helpful error like which row failed which column.

Unfortunately, the diagnostics from the bulk-load tools are often very poor.

However, in this case, when the only problem is that the value of a field is too long there are some chances. You can try the ERRORFILE option. With this option, you don't get one file - you get two. If you say: ERRORFILE = 'C:\temp\myerrors.txt', you will also get C:\temp\myerrors.txt.Error.txt, with detailed information about the errors.

But watch out - if any of these file exist when you run your BULK INSERT command, you get an error because of that!

You should also be aware of the MAXERRORS option which controls how many errors in the file you accept. The default here is 10.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Please try:

 BULK INSERT table1
 FROM 'D:\data\file1.txt'
 WITH
 ( DATAFILETYPE = 'char',
 FIELDTERMINATOR = ' |',
 ROWTERMINATOR = ' \n'
 );

Regards
Echo


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.