BULK INSERT problem (Network Steve Forum)

BULK INSERT problem

I have created a XML format file and am getting the following error.

Bulk load data conversion error (truncation) for row 1, column 7 (linememo1).

The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.

Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

Below is my bulk insert code.

BULK INSERT CorpBilling.dbo.BillingImports
FROM '\\lifedev2012\Data\Everyone\BILLMEIMPORT\EPISYSFEED.TXT'
WITH (FORMATFILE='\\lifedev2012\Data\Everyone\BILLMEIMPORT\blimport.fmt'
, KEEPNULLS, ROWTERMINATOR='\n\r', FIELDTERMINATOR='')

Below is my format XML file.

<?xml version="1.0"?>
<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="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NativeFixed" LENGTH="8"/>
  <FIELD ID="6" xsi:type="NativeFixed" LENGTH="11"/>
  <FIELD ID="7" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="pgrpcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="pitmcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="corpnbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="cunbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="qty" xsi:type="SQLDECIMAL" PRECISION="8" SCALE="1"/>
  <COLUMN SOURCE="6" NAME="uprc" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="4"/>
  <COLUMN SOURCE="7" NAME="linememo1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="linememo2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="linememo3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

The import file is a fixed length file of 150 characters plus CrLf at the end of each line. Below is the table definition for the table I am trying to import into.

CREATE TABLE [dbo].[BillingImports](
	[pgrpcd] [varchar](4) NULL,
	[pitmcd] [varchar](4) NULL,
	[corpnbr] [varchar](9) NULL,
	[cunbr] [varchar](9) NULL,
	[qty] [decimal](8, 1) NULL,
	[uprc] [decimal](11, 4) NULL,
	[linememo1] [nvarchar](35) NULL,
	[linememo2] [nvarchar](35) NULL,
	[linememo3] [nvarchar](35) NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[BillingImports] ADD  CONSTRAINT [DF_BillingImports_qty]  DEFAULT ((0)) FOR [qty]
GO

ALTER TABLE [dbo].[BillingImports] ADD  CONSTRAINT [DF_BillingImports_uprc]  DEFAULT ((0)) FOR [uprc]
GO

March 31st, 2015 11:29am

I think your problem is the CRLF. Add this as a tenth and final two-character field that you don't import. BULK INSERT reads a binary stream, so when it has read the first record, it will think that the CRLF that follows is part of the first field of the second record, and then it goes downhill from there.

Free Windows Admin Tool Kit Click here and download it now

March 31st, 2015 6:14pm

Made the change you suggested (see below) but gave me the same error. What else can I try and what else would help you figure this out? Thanks.

<?xml version="1.0"?>
<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="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NativeFixed" LENGTH="8"/>
  <FIELD ID="6" xsi:type="NativeFixed" LENGTH="11"/>
  <FIELD ID="7" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="CharFixed" LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="pgrpcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="pitmcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="corpnbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="cunbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="qty" xsi:type="SQLDECIMAL" PRECISION="8" SCALE="1"/>
  <COLUMN SOURCE="6" NAME="uprc" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="4"/>
  <COLUMN SOURCE="7" NAME="linememo1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="linememo2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="linememo3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Edited by David Chase89 8 hours 35 minutes ago

March 31st, 2015 6:28pm

p.s. I used the bcp utility to generate the format file.

Free Windows Admin Tool Kit Click here and download it now

March 31st, 2015 6:31pm

I think I am getting closer. It was failing on the 3 nvarchar fields at length=35. I changed them to length=70 (due to nvarchar?) and now those columns are not failing. However, I am now getting the error below.

Invalid data for type "numeric".

March 31st, 2015 6:39pm

Hi David

My guess is that your data is not in fix length and by using NCharFixed 70 you actually combine several columns data into one. therefore the data that should be numeric is now include chars that are not numbers, and you get Invalid data for type "numeric"

Please post the data file EPISYSFEED.TXT that you try to load so we could test it (at least several

Free Windows Admin Tool Kit Click here and download it now

March 31st, 2015 6:55pm

Below are the first several rows of the txt file.

42157154075083214         00000030                                                                                                                    
24017065075083214         00000001                                                                                                                    
42157154075083243         00000010                                                                                                                    
24017065075083243         00000005                                                                                                                    
46017058091881717         00000004                                                                                                                    
46107296091881717         00000004                                                                                                                    
42157154091881717         00000078                                                                                                                    
24017065091881717         00000062                                                                                                                    
46087294091981688         00000018                                                                                                                    
46017058091981688         00000014                                                                                                                    
75107305091981688         00000001                                                                                                                    
42157154091981688         00000137                                                                                                                    
230171010919816882105     00000004      89.25                                                                                                         
23107249091981688         00000100        .00                                                                                                         
23117323091981688         00000007        .00                                                                                                         
46087294271077888         00000002

March 31st, 2015 7:09pm

Made the change you suggested (see below) but gave me the same error. What else can I try and what else would help you figure this out? Thanks.

<?xml version="1.0"?>
<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="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NativeFixed" LENGTH="8"/>
  <FIELD ID="6" xsi:type="NativeFixed" LENGTH="11"/>
  <FIELD ID="7" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="CharFixed" LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="pgrpcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="pitmcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="corpnbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="cunbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="qty" xsi:type="SQLDECIMAL" PRECISION="8" SCALE="1"/>
  <COLUMN SOURCE="6" NAME="uprc" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="4"/>
  <COLUMN SOURCE="7" NAME="linememo1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="linememo2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="linememo3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Edited by David Chase89 Tuesday, March 31, 2015 10:31 PM

Free Windows Admin Tool Kit Click here and download it now

March 31st, 2015 10:27pm

Made the change you suggested (see below) but gave me the same error. What else can I try and what else would help you figure this out? Thanks.

<?xml version="1.0"?>
<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="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="NativeFixed" LENGTH="8"/>
  <FIELD ID="6" xsi:type="NativeFixed" LENGTH="11"/>
  <FIELD ID="7" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="NCharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="CharFixed" LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="pgrpcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="pitmcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="corpnbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="cunbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="qty" xsi:type="SQLDECIMAL" PRECISION="8" SCALE="1"/>
  <COLUMN SOURCE="6" NAME="uprc" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="4"/>
  <COLUMN SOURCE="7" NAME="linememo1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="linememo2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="linememo3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Edited by David Chase89 Tuesday, March 31, 2015 10:31 PM

March 31st, 2015 10:27pm

Sorry, I was a little quick last night. (I put the blame on the XML format being too verbose, so that forest is hidden among the trees.)

The format file that BCP generates is for a native-format file, and you have a text file. All Fields should have the type CharFixed, if it's an ANSI-text file. If it is a Unicode file, it should be NCharFixed. The length should be the length in the file and it should be in bytes.

You still need that 10 file, and if it is a Unicode file, the length should be 4.

Free Windows Admin Tool Kit Click here and download it now

April 1st, 2015 3:20am

You are a genius...it worked. Only question I have now is how can I have blank fields on import come in as NULL? For example the 4th column imported is a varchar(9) and most of the values on the import file are blank (spaces). The 6th column of decimal works perfectly and imports a NULL when blank. Is there something in command or format file that needs changing? I already posted the command I am using and used "KEEPNULLS" in the command but apparently that is not enough. Below is my new format file that I changed based on you last response. Thanks.

<?xml version="1.0"?>
<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="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharFixed" LENGTH="11" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="CharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="CharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="CharFixed" LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="pgrpcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="pitmcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="corpnbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="cunbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="qty" xsi:type="SQLDECIMAL" PRECISION="8" SCALE="1"/>
  <COLUMN SOURCE="6" NAME="uprc" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="4"/>
  <COLUMN SOURCE="7" NAME="linememo1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="linememo2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="linememo3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator 1 hour 42 minutes ago

April 1st, 2015 9:36am

<?xml version="1.0"?>
<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="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="2" xsi:type="CharFixed" LENGTH="4" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="3" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="4" xsi:type="CharFixed" LENGTH="9" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="5" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="6" xsi:type="CharFixed" LENGTH="11" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="7" xsi:type="CharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="8" xsi:type="CharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="9" xsi:type="CharFixed" LENGTH="35" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
  <FIELD ID="10" xsi:type="CharFixed" LENGTH="2" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
 </RECORD>
 <ROW>
  <COLUMN SOURCE="1" NAME="pgrpcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="2" NAME="pitmcd" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="3" NAME="corpnbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="4" NAME="cunbr" xsi:type="SQLVARYCHAR"/>
  <COLUMN SOURCE="5" NAME="qty" xsi:type="SQLDECIMAL" PRECISION="8" SCALE="1"/>
  <COLUMN SOURCE="6" NAME="uprc" xsi:type="SQLDECIMAL" PRECISION="11" SCALE="4"/>
  <COLUMN SOURCE="7" NAME="linememo1" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="8" NAME="linememo2" xsi:type="SQLNVARCHAR"/>
  <COLUMN SOURCE="9" NAME="linememo3" xsi:type="SQLNVARCHAR"/>
 </ROW>
</BCPFORMAT>

Marked as answer by Charlie LiaoMicrosoft contingent staff, Moderator Thursday, April 02, 2015 5:23 AM

Free Windows Admin Tool Kit Click here and download it now

April 1st, 2015 1:36pm

Unfortunately, this seems unavoidable. When you have a file which has a separator, an empty character field import as NULL. But as here, when you have fixed length, the spaces are handled as just that, spaces. You would have to run separate update to replace the spaces will NULL, or load the data into a staging table, and then copy over to the source table to fix this problem.

April 1st, 2015 6:10pm

I concur. Load everything into a staging table, and then you can determine how you should move the data from the staging table to the productions table. Bulk Insert is very FAST, and it is pretty DUMB too. You really can't do a lot with it; it's just not very flexible. It loads data into a table and that's about it.

Anyway, just get the data loaded into any table, and then you will have LOTS of options of how to copy it from this staging table to your actual production table, and during the copy, or rather insert, process. Here are a couple ideas of ow to get the data into your production table.

SQL INSERT INTO SELECT Syntax

We can copy all columns from one table to another, existing table:

INSERT INTO table2
SELECT * FROM table1;

Or we can copy only the columns we want to into another, existing table:

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;

Free Windows Admin Tool Kit Click here and download it now

April 6th, 2015 2:52pm

Yes, it is working great. The bulk load to staging table of 33,000+ rows was very fast and then I processed records with another query. Whole thing took approx. 5 seconds.

April 6th, 2015 4:23pm

This topic is archived. No further replies will be accepted.