Why isn't the Error File for Bulk Insert working?

Question

Thanks for the feedback.

H‌ere is my table that I made to force errors. But it doesn't input anything unless all the columns are 255. These are close to the right column sizes‌. This is the staging table. I expect an error file to be created but none are created.

The message i get is:

Msg 8152, Level 16, State 14, Line 3 String or binary data would be truncated. The statement has been terminated. ‌

drop TABLE VendorUpc  
CREATE TABLE [dbo].[VendorUpc](
    [ID] [bigint] NOT NULL Identity(1,1) Primary key,
    [VendorNumber] [varchar](8) NULL,
    [VendorName] [varchar](50) NULL,
    [ItemNumber] [varchar](50) NULL,
    [ProductDescription] [varchar](900) NULL, 
    [UOM] [varchar](255) NULL, ---?
    [UPC] [varchar](255) NULL ---? 
) ON [PRIMARY] GO

select * from dbo.VendorUpc

T‌his is the Bulk Insert script ‌

truncate table VendorUpc

insert into VendorUpcg
(
    -- ID - This column value is auto-generated
    VendorNumber,
    VendorName,
    ItemNumber,
    ProductDescription,
    UOM,
    UPC    
)
select
    b.VendorNumber,
    b.VendorName,
    b.ItemNumber,
    b.ProductDescription,
    b.UOMs,
    b.UPCs    
from openrowset
(
    bulk 'F:\Data\UPC\Master_File.txt',
    formatfile = 'F:\Data\UPC\Format.xml', 
    errorfile = 'F:\Data\UPC\bulk_insert_BadData.txt', 
    firstrow = 1
) as b

H‌ere is the format file:

<?xml version="1.0" encoding="utf-8"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
  <FIELD ID ="1" xsi:type="CharTerm" TERMINATOR='|' />
  <FIELD ID ="2" xsi:type="CharTerm" TERMINATOR='|' />
  <FIELD ID ="3" xsi:type="CharTerm" TERMINATOR='|'/>
  <FIELD ID ="4" xsi:type="CharTerm" TERMINATOR='|'/>
  <FIELD ID ="5" xsi:type="CharTerm" TERMINATOR='|'/>
  <FIELD ID ="6" xsi:type="CharTerm" TERMINATOR='\n'/> 
</RECORD>
<ROW>
  <COLUMN SOURCE="1" NAME="VendorNumber" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="VendorName" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="ItemNumber" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="ProductDescription" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="UOMs" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="6" NAME="UPCs" xsi:type="SQLVARYCHAR"/>
</ROW> 
</BCPFORMAT> ‌

‌‌‌‌ S‌ample Data:

WT1900|TrueWater|600|"3/4"" Anti-siphon body less union"|Piece|843687114345
WT1900|TrueWater|601|"3/4"" Anti-siphon body with union"|Piece|843687114383
WT1900|TrueWater|602|"1"" Anti-siphon body less union"|Piece|843687114369
WT1900|TrueWater|603|"1"" Anti-siphon body with union"|Piece|843687114406
SUN333|Sunny Bay|604|"3/4"" AVB only"|Piece|843687122029
SUN333|Sunny Bay|605|"1"" AVB only"|Piece|843687122036
SUN333|Sunny Bay|606|"1-1/4"" AV & AVB"|Piece|843687122043
SUN333|Sunny Bay|607|"1-1/2"" AV & AVB"|Piece|843687122050
HD9319|Home Decor|608|"2"" AV & AVB"|Piece|843687122067
HD9319|Home Decor|609|"3/4"" AVU only"|Piece|843687122074

I don't see field terminators specified. Field 4 seems to be using them — user1443098, Sep 04 '18 at 17:02
Aren't the field terminators specified in the xml Format file? I'm not sure I understand. Thanks! — Jacob Pressures, Sep 04 '18 at 17:50
Ah yes. googled a bit more. you can't do what you want with bcp. see https://stackoverflow.com/questions/12902110/bulk-insert-correctly-quoted-csv-file-in-sql-server — user1443098, Sep 04 '18 at 18:41
I read you article you linked. I don't get your point @user1443098. I'm not using quotation marks for column terminators. When I remove the quotes, I get the same error. — Jacob Pressures, Sep 04 '18 at 19:02

score 0 · Answer 1 · answered Sep 05 '18 at 12:37

0

You are using quoted text. e.g.

"3/4"" Anti-siphon body less union"

Bulk copy doesn't handle that properly.

answered Sep 05 '18 at 12:37

user1443098

6,487
5
38
67

What difference does quoted text make? I'm not assuming the text is quoted. Also i removed the quotes and it still doesn't work. The only time i works is when i set everything to VARCHAR(255). That works with quoted text. So if the quotation marks alone is the problem, I don't see it. Can you explain more? Can you try this? As i've said, i removed the quotes and it doesn't work. It does with with quotes if it is VARCHAR(255) – Jacob Pressures Sep 05 '18 at 13:37
All the links assume I'm using quotation marks are a delimiter. The quotes are irrelevant to my delimitation. I'm using pipes instead. – Jacob Pressures Sep 05 '18 at 13:46
Interesting comment here: https://stackoverflow.com/questions/15161088/bulk-insert-failed-bulk-load-data-conversion-error-truncation regarding the row terminator. don't know if it applies in your case. but if it did, it might explain the error – user1443098 Sep 05 '18 at 15:50
Thanks again! My issue is a truncation issue. So let me do some research on that. The issue with the last link is that the guy had his lash going in the wrong direction. He had a forward slash rather than a backslash. – Jacob Pressures Sep 05 '18 at 16:40
The part I'm curious about is \n vs \r\n – user1443098 Sep 05 '18 at 17:00

score 0 · Answer 2 · answered Sep 06 '18 at 13:56

The issue was that I needed to add MAX_LENGTH to my format file.

Now i acts weird on the first line even though i say start on the first row. I'm not sure why it sends some of my files to the error log. If i move them around it no longer sends them to the error log. Its weird. But this part is fixed.

I hope this helps someone in the future.

<?xml version="1.0" encoding="utf-8"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" 
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <RECORD>
     <FIELD ID ="1" xsi:type="CharTerm" MAX_LENGTH ="8" TERMINATOR='|' />
     <FIELD ID ="2" xsi:type="CharTerm" MAX_LENGTH ="50" TERMINATOR='|' />
     <FIELD ID ="3" xsi:type="CharTerm" MAX_LENGTH ="50" TERMINATOR='|'/>
     <FIELD ID ="4" xsi:type="CharTerm" MAX_LENGTH ="900" TERMINATOR='|'/>
     <FIELD ID ="5" xsi:type="CharTerm" MAX_LENGTH ="255" TERMINATOR='|'/>
     <FIELD ID ="6" xsi:type="CharTerm" MAX_LENGTH ="255" TERMINATOR='\n'/> 
  </RECORD>
  <ROW>
    <COLUMN SOURCE="1" NAME="VendorNumber" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="2" NAME="VendorName" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="3" NAME="ItemNumber" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="4" NAME="ProductDescription" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="5" NAME="UOMs" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="6" NAME="UPCs" xsi:type="SQLVARYCHAR"/>
  </ROW>  
</BCPFORMAT>

Why isn't the Error File for Bulk Insert working?

2 Answers2