T-SQL Error in Enterprise Manager

[phzero]

Hi all, I'm trying to import a flat file using bulk insert into one of my tables but the following error keeps coming up: "Bulk Insert Data Conversion Error (truncation) for row 1, column 2". This might seem straight-forward, but, in the file, the specified field is 1 char long while in the database it is 2 chars long. How can there be any truncation of the data during the import. Naturally, it rollsback all transactions. I don't understand what the problem could be. The utility bcp brings up the exact same error message. Any comments, replies or suggestions are welcome.
Thanks all.

 

[dickiebird]

Has your input file got embedded non-printables such as tabs, eom, bs etc ? (try to view it with a hex editor)
Is the default delimiter not set to tab ?
Post a file sample ( hex if possible ) + your bcp statement.
Dickie Bird (-)))

 

[phzero]

Hi dickiebird,

thanks for the reply. No, there are no non-printable characters in the file. It's just a plain text file with the field delimiter being ';' and the row delimiter being '\n'. Here is my bcp statement:
bcp BopMatchMate..zValidateInfo in c:\tester.txt -c -q -t";" -SF2543826 -Ppassword
I get the following error:
SQLState = 22001, Native Error = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation

How do I attach a file to my reply?

 

[dickiebird]

I think -q needs a -J charset, so -q on it's own is not needed - try without it.
There's no file attachment facility in the forums -
Cut and paste is the only way, A.F.A.I.K. Dickie Bird (-)))

 

[phzero]

Hi, I have tried dropping certain parameters, I get the same error. Here is my file that I copied and pasted here:

"B12022003 ";"9";20030207;"138";"ACD";"0415900038JN0000";"00250841";"IL";"1999/000254/07 ";"ANGLO AFRICAN TRADING ";" ";" ";" ";"3rd Floor Permanent Building ";" ";"4 Plein Street ";"Stellenbosch, 7600 ";"ZA";" ";" ";" ";" ";"IG";" ";"ACC NO 200454022 ";" ";" ";" ";" ";" ";" ";" ";"US";" ";" ";" ";" ";"11 ";850.02;" ";0;"13";"301";" ";" ";" ";0;" ";"0";0;0;"0";0;0;0;"";" ";"321";850.02;"USD";25;"US";" ";" ";"00000000";0;" ";" ";" "
"B12022003 ";"9";20030207;"138";"ACD";"0416100038JN0000";"00250841";"IL";"1999/000254/07 ";"ANGLO AFRICAN TRADING ";" ";" ";" ";"3rd Floor Permanent Building ";" ";"4 Plein Street ";"Stellenbosch, 7600 ";"ZA";" ";" ";" ";" ";"IG";" ";"ACC NO 7320011387 ";" ";" ";" ";" ";" ";" ";" ";"US";" ";" ";" ";" ";"11 ";1350.00;" ";0;"13";"301";" ";" ";" ";0;" ";"0";0;0;"0";0;0;0;"";" ";"321";1350.00;"USD";25;"US";" ";" ";"00000000";0;" ";" ";" "
"B12022003 ";"9";20030207;"138";"ACD";"0416200038JN0000";"00250841";"IL";"1999/000254/07 ";"ANGLO AFRICAN TRADING ";" ";" ";" ";"3rd Floor Permanent Building ";" ";"4 Plein Street ";"Stellenbosch, 7600 ";"ZA";" ";" ";" ";" ";"IG";" ";"ACC NO 7305010170 ";" ";" ";" ";" ";" ";" ";" ";"US";" ";" ";" ";" ";"11 ";1375.00;" ";0;"13";"301";" ";" ";" ";0;" ";"0";0;0;"0";0;0;0;"";" ";"321";1375.00;"USD";25;"US";" ";" ";"00000000";0;" ";" ";" "
"B12022003 ";"9";20030207;"138";"ACD";"0416000038JN0000";"00250841";"IL";"1999/000254/07 ";"ANGLO AFRICAN TRADING ";" ";" ";" ";"3rd Floor Permanent Building ";" ";"4 Plein Street ";"Stellenbosch, 7600 ";"ZA";" ";" ";" ";" ";"IG";" ";"ACC NO 400906546 ";" ";" ";" ";" ";" ";" ";" ";"US";" ";" ";" ";" ";"11 ";4085.20;" ";0;"13";"301";" ";" ";" ";0;" ";"0";0;0;"0";0;0;0;"";" ";"321";4085.20;"USD";25;"US";" ";" ";"00000000";0;" ";" ";" "
"B12022003 ";"9";20030207;"138";"ACD";"0416400038JN0000";"00250841";"IL";"1999/000254/07 ";"ANGLO AFRICAN TRADING ";" ";" ";" ";"3rd Floor Permanent Building ";" ";"4 Plein Street ";"Stellenbosch, 7600 ";"ZA";" ";" ";" ";" ";"IG";" ";"Acc no 0111-7302596 ";" ";" ";" ";" ";" ";" ";" ";"US";" ";" ";" ";" ";"11 ";7000.00;" ";0;"13";"301";" ";" ";" ";0;" ";"0";0;0;"0";0;0;0;"";" ";"321";7000.00;"USD";25;"US";" ";" ";"00000000";0;" ";" ";" "
"B12022003 ";"9";20030207;"138";"ACD";"0415800038JN0000";"00250841";"IL";"1999/000254/07 ";"ANGLO AFRICAN TRADING ";" ";" ";" ";"3rd Floor Permanent Building ";" ";"4 Plein Street ";"Stellenbosch, 7600 ";"ZA";" ";" ";" ";" ";"IG";" ";"ACC NO 09 027 0231 ";" ";" ";" ";" ";" ";" ";" ";"US";" ";" ";" ";" ";"11 ";9925.00;" ";0;"13";"301";" ";" ";" ";0;" ";"0";0;0;"0";0;0;0;"";" ";"321";9925.00;"USD";25;"US";" ";" ";"00000000";0;" ";" ";" "
"B12022003 ";"9";20030207;"138";"ACD";"0415700038JN0000";"00250841";"IL";"1999/000254/07 ";"ANGLO AFRICAN TRADING ";" ";" ";" ";"3rd Floor Permanent Building ";" ";"4 Plein Street ";"Stellenbosch, 7600 ";"ZA";" ";" ";" ";" ";"IG";" ";"ACC NO 000.10.00.46.320 ";" ";" ";" ";" ";" ";" ";" ";"US";" ";" ";" ";" ";"11 ";20000.00;" ";0;"13";"301";" ";" ";" ";0;" ";"0";0;0;"0";0;0;0;"";" ";"321";20000.00;"USD";25;"US";" ";" ";"00000000";0;" ";" ";" "
"B12022003 ";"9";20030207;"138";"ACD";"0416300038JN0000";"00250841";"IL";"1999/000254/07 ";"ANGLO AFRICAN TRADING ";" ";" ";" ";"3rd Floor Permanent Building ";" ";"4 Plein Street ";"Stellenbosch, 7600 ";"ZA";" ";" ";" ";" ";"IG";" ";"ACC NO 000.10.00.46.320 ";" ";" ";" ";" ";" ";" ";" ";"US";" ";" ";" ";" ";"11 ";181722.97;" ";0;"13";"301";" ";" ";" ";0;" ";"0";0;0;"0";0;0;0;"";" ";"321";181722.97;"USD";25;"US";" ";" ";"00000000";0;" ";" ";" "

 

[JamesLean]

SQl Server won't recognise the double quotes in the fields as text qualifiers so the value in the second column is actually the whole of "9" (ie 3 characters).

Funnily enough, I had the exact same problem the other week. The way I solved it was to import the data first to a staging table. Import the file using the double quotes as field seperators and add columns to the table to hold the semi-colons temporarily.

Once the file is imported it should be easy to run an INSERT statement and transfer the columns you need into the proper table. --James

 

[dickiebird]

OR
Run the file through a formatter (eg 'sed' on Unix)
to remove all the " before running bcp. Dickie Bird (-)))