0

OK... I give up. I have been struggling with trying to import a semicolon-separated file (field1;field2) with BCP for the past two days, but I only have been getting these errors:

Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]String data, right truncation
SQLState = S1000, NativeError = 0
Error = [Microsoft][SQL Server Native Client 10.0]Unexpected EOF encountered in BCP data-file

A notepad++ screenshot of my input files is below: Some fields have spaces before the semicolon Please note that some fields can have whitespace before the semicolon.

I don't need to import all fields, so I created a 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="CharTerm" TERMINATOR=";" />
<FIELD ID="2" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="4" COLLATION="Latin1_General_CI_AS"/>
<FIELD ID="3" xsi:type="CharTerm" TERMINATOR=";" />
  <FIELD ID="4" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="24" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="5" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="14" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="6" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="4" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="7" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="4" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="8" xsi:type="CharTerm" TERMINATOR=";" />
  <FIELD ID="9" xsi:type="CharTerm" TERMINATOR=";" MAX_LENGTH="120" COLLATION="Latin1_General_CI_AS"/>
  <FIELD ID="17" xsi:type="CharTerm" TERMINATOR=";" />
  <FIELD ID="19" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="128" COLLATION="Latin1_General_CI_AS"/>
</RECORD>
 <ROW>
  <COLUMN SOURCE="6" NAME="Municipio" xsi:type="SQLNUMERIC" PRECISION="18" SCALE="0"/>
  <COLUMN SOURCE="17" NAME="Placa" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="3" NAME="Categoria" xsi:type="SQLNCHAR"/>
 <COLUMN SOURCE="8" NAME="Taxa" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="1" NAME="Autenticacao" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="2" NAME="Chassi" xsi:type="SQLNCHAR"/>
  <COLUMN SOURCE="4" NAME="Cor" xsi:type="SQLINT"/>
  <COLUMN SOURCE="5" NAME="MarcaModelo" xsi:type="SQLNUMERIC" PRECISION="18" SCALE="0"/>
  <COLUMN SOURCE="7" NAME="TipoServico" xsi:type="SQLINT"/>
  <COLUMN SOURCE="19" NAME="Destino" xsi:type="SQLNUMERIC" PRECISION="18" SCALE="0"/>
  <COLUMN SOURCE="9" NAME="TipoVeiculo" xsi:type="SQLNCHAR"/>
 </ROW>
</BCPFORMAT>

Please also note that fields are not ordered.

My command line is

 bcp [dbname].dbo.dbTable in input.txt -f format.fmt -T

I am truly and hopelessly lost... If anyone can point me in the right direction, I would really appreciate it.

BULK INSERT fails as well.

Thanks in advance,

  • It looks like the last field in the row (02-CARGA in example) is attempting to be inserted into a numeric column. obviously that is not the error you are getting but since that looks incorrect and your example doesn't include all 19 fields it tough to tell exactly where the error is. Double check your types in your format file. – Bob Klimes Mar 19 '15 at 19:32
  • @BobKlimes: thanks for the hint. I have checked this link and MS tells us the bcp ignores the <row> element when bulk importing. Quoting: "Instead, bcp loads the data based on the column types of the target table.". More lost than ever... :( – Pbal Mar 19 '15 at 20:14
  • 2
    A word of advice: I always import data from flat files into a staging table, with columns that are purposely oversized - usually nvarchar(255) for everything, unless some field is larger than that. It's much easier to sanitize and normalize the data after the import rather than trying to do it during. – dartonw Mar 20 '15 at 7:35
  • @pbal I have to ask: Are the types in your target table consistent with the data that that is going in? – Bob Klimes Mar 20 '15 at 13:37
  • @BobKlimes: I have rechecked this and re-read the documentation. It seems BCP won't work, so I am focusing on BULK INSERT now. I am taking dartonw's advice and will restart with a staging table and build from there. Will post results and an eventual final answer. It can't be this difficult... – Pbal Mar 20 '15 at 17:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.