Bulk load data conversion error overflow

I am getting this error

Bulk load data conversion error (truncation) for row 1, column 12 (is_download)

here is the csv…it only has one row

30,Bill,Worthy,sales,,709888499,bat@bat.com,,"Im a a people person., to work together for this new emerging env.HTTP://applesoftware.com","Bill and Son of Co","Contact Us: Contact Form",0

here is my bulk insert statement…

SE SalesLogix
GO

CREATE TABLE CSVTemp
(id INT,
firstname VARCHAR(255),
lastname VARCHAR(255),
department VARCHAR(255),
architecture VARCHAR(255),
phone VARCHAR(255),
email VARCHAR(255),
download VARCHAR(255),
comments VARCHAR(MAX),
company VARCHAR(255),
location VARCHAR(255),
is_download VARCHAR(255)
)
GO

BULK
INSERT CSVTemp
FROM 'c:leadsleads.csv'
WITH
(
DATAFILETYPE = 'char', 
BATCHSIZE = 50, 
FIELDTERMINATOR = ',', 
ROWTERMINATOR = 'n' 
)
GO
--Check the content of the table.
SELECT *
FROM CSVTemp
GO

The problem is most of the time it works great but in some situations (this being one of them) I get the errors

ANy ideas on what is causing this record to have this error

I have been racking my brain trying to figure out this error. I just don’t get it.

SQL Server Database Error: Bulk load data conversion error (overflow) for row 1, column 1 (CUR_CLM_UNIQ_ID).

Here is my code:

SET DATEFORMAT ymd;
go
BULK INSERT 
aco.PartAClaimsHeader
FROM 'C:UsersDerekDocumentsfilesP.A1000.ACO.CCLF1.D120730.T1251070'
WITH
(
 --DATAFILETYPE ='CHAR',
  FORMATFILE='C:UsersDerekDocumentsfilesHeaderFileFormat.xml',
  --ERRORFILE='C:UsersDerekDocumentsfilesERRORS.TXT',
  ROWTERMINATOR='rn',
  CODEPAGE='RAW',
 -- KEEPNULLS,
  TABLOCK
  )
  GO  

It’s fixed width.
Here is some sample data

00190000777340000000000000109061100000924A 102012-01-182012-02-16323569   ~         0000000003522.44 12010000~1154529360~         1518945500~         
00158077735600000000000000100256140000646D 602011-07-222011-07-241141401  78650     0000000009238.12 12010247~1992759419159875707215987570721598757072
0022980857396000000000000010S191274000037A 602012-06-042012-06-08112988   2988      0000000002255.52 12010885~1760429880~         1831184316~         

Here is my format 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="13"/>
    <FIELD ID="2" xsi:type="CharFixed" LENGTH="13"/>
    <FIELD ID="3" xsi:type="CharFixed" LENGTH="6"/>
    <FIELD ID="4" xsi:type="CharFixed" LENGTH="11"/>
    <FIELD ID="5" xsi:type="CharFixed" LENGTH="2"/>
    <FIELD ID="6" xsi:type="CharFixed" LENGTH="10"/>
    <FIELD ID="7" xsi:type="CharFixed" LENGTH="10"/>
    <FIELD ID="8" xsi:type="CharFixed" LENGTH="1"/>
    <FIELD ID="9" xsi:type="CharFixed" LENGTH="1"/>
    <FIELD ID="10" xsi:type="CharFixed" LENGTH="7"/>
    <FIELD ID="11" xsi:type="CharFixed" LENGTH="7"/>
    <FIELD ID="12" xsi:type="CharFixed" LENGTH="2"/>
    <FIELD ID="13" xsi:type="CharFixed" LENGTH="17"/>
    <FIELD ID="14" xsi:type="CharFixed" LENGTH="1"/>
    <FIELD ID="15" xsi:type="CharFixed" LENGTH="2"/>
    <FIELD ID="16" xsi:type="CharFixed" LENGTH="2"/>
    <FIELD ID="17" xsi:type="CharFixed" LENGTH="4"/>  
    <FIELD ID="18" xsi:type="CharFixed" LENGTH="1"/>
    <FIELD ID="19" xsi:type="CharFixed" LENGTH="10"/>
    <FIELD ID="20" xsi:type="CharFixed" LENGTH="10"/>
    <FIELD ID="21" xsi:type="CharFixed" LENGTH="10"/>
    <FIELD ID="22" xsi:type="CharFixed" LENGTH="10"/>
    <FIELD ID="23" xsi:type="CharTerm" TERMINATOR="rn"/>
</RECORD>
<ROW>
    <COLUMN SOURCE="1" NAME="c1" xsi:type="SQLINT"/>
    <COLUMN SOURCE="2" NAME="c2" xsi:type="SQLINT"/>
    <COLUMN SOURCE="3" NAME="c3" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="4" NAME="c4" xsi:type="SQLVARYCHAR" />
    <COLUMN SOURCE="5" NAME="c5" xsi:type="SQLTINYINT" />
    <COLUMN SOURCE="6" NAME="c6" xsi:type="SQLDATE"/>
    <COLUMN SOURCE="7" NAME="c7" xsi:type="SQLDATE"/>
    <COLUMN SOURCE="8" NAME="c8" xsi:type="SQLCHAR" LENGTH="1"/>
    <COLUMN SOURCE="9" NAME="c9" xsi:type="SQLCHAR" LENGTH="1" />
    <COLUMN SOURCE="10" NAME="c10" xsi:type="SQLVARYCHAR" />
    <COLUMN SOURCE="11" NAME="c11" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="12" NAME="c12" xsi:type="SQLCHAR" LENGTH="2"/>
    <COLUMN SOURCE="13" NAME="c13" xsi:type="SQLDECIMAL" SCALE="2" PRECISION="17"/>
    <COLUMN SOURCE="14" NAME="c14" xsi:type="SQLCHAR" LENGTH="1" />
    <COLUMN SOURCE="15" NAME="c15" xsi:type="SQLCHAR" LENGTH="2" />
    <COLUMN SOURCE="16" NAME="c16" xsi:type="SQLCHAR" LENGTH="2"/>
    <COLUMN SOURCE="17" NAME="c17" xsi:type="SQLVARYCHAR"/>
    <COLUMN SOURCE="18" NAME="c18" xsi:type="SQLCHAR" LENGTH="1"/>
    <COLUMN SOURCE="19" NAME="c19" xsi:type="SQLCHAR"  LENGTH="10"/>
    <COLUMN SOURCE="20" NAME="c20" xsi:type="SQLCHAR" LENGTH="10" />        
    <COLUMN SOURCE="21" NAME="c21" xsi:type="SQLCHAR"  LENGTH="10"/>        
    <COLUMN SOURCE="22" NAME="c22" xsi:type="SQLCHAR"  LENGTH="10"/>        
    <COLUMN SOURCE="23" NAME="c23" xsi:type="SQLCHAR" />                
</ROW>
</BCPFORMAT>

UPDATE: I changed the 2 first INT’s to VARCHAR in the db and format file and it worked no problem. Would like to keep them INT’s though.

Posted by Simon Cho on 03/07/2013

This entry was posted on 03/07/2013 at 11:57 AM and is filed under Common.
Tagged: BCP, Bulk, Bulk Insert. You can follow any responses to this entry through the RSS 2.0 feed.

You can leave a response, or trackback from your own site.

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)

I am trying to import a simple CSV file into a table on SQL Server 2017. The CSV file and table match each other in number of columns and data types. This is an extract of the CSV file:

UID,customerID,DateT,TypeID,AssociatedID,AttributeID,LandingPage,jsonT,ClientIPAddress
9150,2345,2020-09-01T00:00:36Z,1,,859,campaign01.html,{'product': 'string01', 'type': '', 'string011'},192.168.200.2
9151,2356,2020-09-01T00:02:30Z,1,,640,campaign02.html,{'product': 'string02', 'type': '', 'string022'},192.168.200.1

This is my table:

CREATE TABLE [dbo].[activity](
    [UID] [bigint] NOT NULL,
    [customerID] [bigint] NOT NULL,
    [DateT] [datetime] NULL,
    [TypeID] [bigint] NULL,
    [AssociatedID] [float] NULL,
    [AttributeID] [bigint] NULL,
    [LandingPage] [varchar](256) NULL,
    [jsonT] [varchar](1024) NULL,
    [ClientIPAddress] [varchar](64) NULL,
)
GO

This is my import statement:

BULK INSERT dbo.activity
FROM 'C:tmpactivity.csv'
WITH (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',
    ROWTERMINATOR = '0x0a',
    BATCHSIZE = 1000,
    MAXERRORS = 2
)
GO

I keep getting an error saying the last column (the client IP) is to be truncated. I checked all the values and they have nothing but IPs. Also the error is not on all rows and I cannot see for my life any difference between one IP format or the other, they are just IPs! I tried simply making the ClientIPAddress field bigger (just for the sake of it) but I just keep getting the same error.

Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 2, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 5, column 9 (ClientIPAddress).
Msg 4863, Level 16, State 1, Line 1
Bulk load data conversion error (truncation) for row 6, column 9 (ClientIPAddress).
Msg 4865, Level 16, State 1, Line 1
Cannot bulk load because the maximum number of errors (2) was exceeded.
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)".

Any idea on what I could check or what I am doing wrong would be highly appreciated.

Thank you!

bulk insert dbo.A
FROM 'd:AData.csv'
WITH 
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = 'n'
)

Inserting bulk data into a database, I approached an unexplainable error after review of the suspect data:

Msg 4867, Level 16, State 1, Line 6
Bulk load data conversion error (overflow) for row 1, column 42 (FES).

Column FES was tinyint, now smallint. d:AData.csv contains a 2 in row 1, column 42. Are there any other sources for this type of error?

—APPEND—

I’m not sure how to properly post this.

Create Table Script

create table AcsBy320082010Hus(
serialnoId bigint,
RT char,
DIVISION tinyint,
PUMA int,
REGION tinyint,
ST tinyint,
ADJHSG int,
ADJINC int,
WGTP int,
NP tinyint,
TYPE tinyint,
ACR tinyint,
AGS tinyint,
BDS tinyint,
BLD tinyint,
BUS tinyint,
CONP smallint,
ELEP smallint,
FS smallint,
FULP int,
GASP int,
HFL tinyint,
INSP varchar(6),
KIT tinyint,
MHP int,
MRGI tinyint,
MRGP int,
MRGT tinyint,
MRGX tinyint,
PLM tinyint,
RMS tinyint,
RNTM tinyint,
RNTP int,
SMP int,
TEL tinyint,
TEN tinyint,
VACS tinyint,
VAL tinyint,
VEH tinyint,
WATP int,
YBL tinyint,
FES tinyint,
FINCP int,
FPARC tinyint,
GRNTP int,
GRPIP tinyint,
HHL tinyint,
HHT tinyint,
HINCP int,
HUGCL bit,
HUPAC tinyint,
HUPAOC tinyint,
HUPARC tinyint,
LNGI tinyint,
MV tinyint,
NOC tinyint,
NPF tinyint,
NPP bit,
NR bit,
NRC tinyint,
OCPIP tinyint,
PARTNER tinyint,
PSF bit,
R18 bit,
R60 tinyint,
R65 tinyint,
RESMODE tinyint,
SMOCP int,
SMX tinyint,
SRNT bit,
SVAL bit,
TAXP tinyint,
WIF tinyint,
WKEXREL tinyint,
WORKSTAT tinyint,
FACRP bit,
FAGSP bit,
FBDSP bit,
FBLDP bit,
FBUSP bit,
FCONP bit,
FELEP bit,
FFSP bit,
FFULP bit,
FGASP bit,
FHFLP bit,
FINSP bit,
FKITP bit,
FMHP bit,
FMRGIP bit,
FMRGP bit,
FMRGTP bit,
FMRGXP bit,
FMVYP bit,
FPLMP bit,
FRMSP bit,
FRNTMP bit,
FRNTP bit,
FSMP bit,
FSMXHP bit,
FSMXSP bit,
FTAXP bit,
FTELP bit,
FTENP bit,
FVACSP bit,
FVALP bit,
FVEHP bit,
FWATP bit,
FYBLP bit,
WGTP1 int,
WGTP2 int,
WGTP3 int,
WGTP4 int,
WGTP5 int,
WGTP6 int,
WGTP7 int,
WGTP8 int,
WGTP9 int,
WGTP10 int,
WGTP11 int,
WGTP12 int,
WGTP13 int,
WGTP14 int,
WGTP15 int,
WGTP16 int,
WGTP17 int,
WGTP18 int,
WGTP19 int,
WGTP20 int,
WGTP21 int,
WGTP22 int,
WGTP23 int,
WGTP24 int,
WGTP25 int,
WGTP26 int,
WGTP27 int,
WGTP28 int,
WGTP29 int,
WGTP30 int,
WGTP31 int,
WGTP32 int,
WGTP33 int,
WGTP34 int,
WGTP35 int,
WGTP36 int,
WGTP37 int,
WGTP38 int,
WGTP39 int,
WGTP40 int,
WGTP41 int,
WGTP42 int,
WGTP43 int,
WGTP44 int,
WGTP45 int,
WGTP46 int,
WGTP47 int,
WGTP48 int,
WGTP49 int,
WGTP50 int,
WGTP51 int,
WGTP52 int,
WGTP53 int,
WGTP54 int,
WGTP55 int,
WGTP56 int,
WGTP57 int,
WGTP58 int,
WGTP59 int,
WGTP60 int,
WGTP61 int,
WGTP62 int,
WGTP63 int,
WGTP64 int,
WGTP65 int,
WGTP66 int,
WGTP67 int,
WGTP68 int,
WGTP69 int,
WGTP70 int,
WGTP71 int,
WGTP72 int,
WGTP73 int,
WGTP74 int,
WGTP75 int,
WGTP76 int,
WGTP77 int,
WGTP78 int,
WGTP79 int,
WGTP80 int)

Headers row

serialno,RT,DIVISION,PUMA,REGION,ST,ADJHSG,ADJINC,WGTP,NP,TYPE,ACR,AGS,BATH,BDSP,BLD,BUS,CONP,ELEP,FS,FULP,GASP,HFL,INSP,MHP,MRGI,MRGP,MRGT,MRGX,REFR,RMSP,RNTM,RNTP,RWAT,SINK,SMP,STOV,TEL,TEN,TOIL,VACS,VALP,VEH,WATP,YBL,FES,FINCP,FPARC,GRNTP,GRPIP,HHL,HHT,HINCP,HUGCL,HUPAC,HUPAOC,HUPARC,KIT,LNGI,MULTG,MV,NOC,NPF,NPP,NR,NRC,OCPIP,PARTNER,PLM,PSF,R18,R60,R65,RESMODE,SMOCP,SMX,SRNT,SVAL,TAXP,WIF,WKEXREL,WORKSTAT,FACRP,FAGSP,FBATHP,FBDSP,FBLDP,FBUSP,FCONP,FELEP,FFSP,FFULP,FGASP,FHFLP,FINSP,FKITP,FMHP,FMRGIP,FMRGP,FMRGTP,FMRGXP,FMVP,FPLMP,FREFRP,FRMSP,FRNTMP,FRNTP,FRWATP,FSINKP,FSMP,FSMXHP,FSMXSP,FSTOVP,FTAXP,FTELP,FTENP,FTOILP,FVACSP,FVALP,FVEHP,FWATP,FYBLP,WGTP1,WGTP2,WGTP3,WGTP4,WGTP5,WGTP6,WGTP7,WGTP8,WGTP9,WGTP10,WGTP11,WGTP12,WGTP13,WGTP14,WGTP15,WGTP16,WGTP17,WGTP18,WGTP19,WGTP20,WGTP21,WGTP22,WGTP23,WGTP24,WGTP25,WGTP26,WGTP27,WGTP28,WGTP29,WGTP30,WGTP31,WGTP32,WGTP33,WGTP34,WGTP35,WGTP36,WGTP37,WGTP38,WGTP39,WGTP40,WGTP41,WGTP42,WGTP43,WGTP44,WGTP45,WGTP46,WGTP47,WGTP48,WGTP49,WGTP50,WGTP51,WGTP52,WGTP53,WGTP54,WGTP55,WGTP56,WGTP57,WGTP58,WGTP59,WGTP60,WGTP61,WGTP62,WGTP63,WGTP64,WGTP65,WGTP66,WGTP67,WGTP68,WGTP69,WGTP70,WGTP71,WGTP72,WGTP73,WGTP74,WGTP75,WGTP76,WGTP77,WGTP78,WGTP79,WGTP80

Row 1

2008000000023,H,6,01300,3,01,1012650,1031272,00043,04,1,1,,1,02,02,2,0000,150,2,0002,020,3,00150,,2,00980,1,1,1,03,,,1,1,,1,1,1,1,,0210000,2,0080,09,2,000058100,2,,,4,1,000058100,0,3,3,3,1,1,1,3,02,04,0,0,02,024,0,1,0,1,0,0,1,01170,3,0,1,19,1,3,3,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,00112,00045,00068,00017,00044,00091,00071,00032,00036,00042,00048,00041,00013,00031,00063,00013,00037,00017,00063,00098,00026,00036,00017,00065,00036,00020,00014,00035,00037,00046,00047,00048,00080,00034,00012,00061,00031,00069,00043,00060,00011,00050,00016,00057,00050,00013,00016,00068,00056,00039,00042,00049,00081,00041,00012,00092,00050,00055,00049,00016,00062,00041,00103,00012,00048,00060,00073,00052,00063,00038,00047,00040,00017,00040,00059,00020,00042,00010,00038,00018

Link to ACS 2008-2010 Data Dictionary