none
Help with BULK Insert

    Question

  • Hello SQL Buddies,

    I am trying to run a BULK insert.
    Here is my code:

    Code start:
    use Lagerliste
    drop table Import

    use Lagerliste
    create table Import
    (
    TOTALQTY float null,
    PARTNO nvarchar (255) null,
    [DESC] nvarchar (255) null,
    CO nvarchar (255) null,
    BIN nvarchar (255) null,
    PRICE float null,
    DISCOUNT nvarchar (255) null,
    LASTPURC nvarchar (255) null,
    AGECODE nvarchar (255) null,
    MANFPART nvarchar (255) null
    )

    use Lagerliste
    bulk
    insert Import
    from 'D:\FTP\RG\Stockfile.csv'
    with
    (
    formatfile = 'D:\FormatFile\test.xml',
    Errorfile = 'D:\FormatFile\error.txt'
    )
    Code stop..

    My format file code is here:
    Code start:
    <?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"    MAX_LENGTH="255"    TERMINATOR= '","' />
          <FIELD ID="3"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="4"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="5"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '",'    />
          <FIELD ID="6"    xsi:type="CharTerm"      TERMINATOR= ',"'    />
          <FIELD ID="7"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="8"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="9"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="10"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '"'        />
        </RECORD>

        <ROW>
          <COLUMN SOURCE="1" NAME="TOTALQTY"    xsi:type="SQLFLT8"/>
          <COLUMN SOURCE="2" NAME="PARTNO"    xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="3" NAME="DESC"        xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="4" NAME="CO"        xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="5" NAME="BIN"        xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="6" NAME="PRICE"        xsi:type="SQLFLT8"/>
          <COLUMN SOURCE="7" NAME="DISCOUNT"    xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="8" NAME="LASTPURC"    xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="9" NAME="AGECODE"    xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="10" NAME="MANFPART"    xsi:type="SQLNVARCHAR"/>
        </ROW>

      </BCPFORMAT>

    Code stop..

    If i run the code it says:
    Msg 4832, Level 16, State 1, Line 20
    Bulk load: An unexpected end of file was encountered in the data file.
    Msg 7399, Level 16, State 1, Line 20
    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 20
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

    And the error file says:
    Row 473629 File Offset 42503226 ErrorFile Offset 0 - HRESULT 0x80004005

    If i then in my bulk insert adds: "Lastrow = '473629' it works all fine. So it will mean that it gets an error at the last line where there is no more data? But this sql query should run every day, so i have to do so it dont gets the error.

    Can someone help me?
    Looking forward to an answer, i hope someone have a solution.

    Regards Christian.

    Wednesday, March 05, 2014 1:08 PM

All replies

  • There is an inconsitency between your format file and your verbal description. You say at the last line. However, your format file does not seem to based on a notion of lines. Normally, in a text file, the last field in the format file always has \r\n as the terminator. (Or \n for a Unix file or \r for a Mac file).

    So I think that is your error, you should add the line terminator to the last field.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Wednesday, March 05, 2014 1:49 PM
  • My data is looking like this:

     0 ,"MA140 780 10 14","HOLDER","21","U/L", 231.92 ,"20","04/07/2013","0","A1407801014"
     1 ,"MA168 780 01 36","PLADE","21","11007021", 223.6 ,"20","30/08/2013","0","A1687800136"
     1 ,"MA168 780 02 36","PLADE","21","11007031", 223.6 ,"20","30/08/2013","0","A1687800236"
     1 ,"MA168 780 00 38","REGULERINGSARM","21","14506032", 126.88 ,"20","06/09/2013","0","A1687800038"

     1 ,"MA168 780 01 38","REGULERINGSARM","21","14506033", 126.88 ,"20","06/09/2013","0","A1687800138"

    Can you explain what i then have to do in my format file?

    Wednesday, March 05, 2014 2:58 PM
  • Try the below 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"    MAX_LENGTH="255"    TERMINATOR= '","' />
          <FIELD ID="3"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="4"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="5"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '",'    />
          <FIELD ID="6"    xsi:type="CharTerm"      TERMINATOR= ',"'    />
          <FIELD ID="7"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="8"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="9"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '","'    />
          <FIELD ID="10"    xsi:type="CharTerm"    MAX_LENGTH="255"    TERMINATOR= '"\r\n'        />
        </RECORD>
        <ROW>
          <COLUMN SOURCE="1" NAME="TOTALQTY"    xsi:type="SQLFLT8"/>
          <COLUMN SOURCE="2" NAME="PARTNO"    xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="3" NAME="DESC"        xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="4" NAME="CO"        xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="5" NAME="BIN"        xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="6" NAME="PRICE"        xsi:type="SQLFLT8"/>
          <COLUMN SOURCE="7" NAME="DISCOUNT"    xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="8" NAME="LASTPURC"    xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="9" NAME="AGECODE"    xsi:type="SQLNVARCHAR"/>
          <COLUMN SOURCE="10" NAME="MANFPART"    xsi:type="SQLNVARCHAR"/>
        </ROW>
      </BCPFORMAT>

    Refer

    http://stackoverflow.com/questions/8530353/sql-bulk-insert-xml-format-file-with-double-quotes-in-terminator

    http://technet.microsoft.com/en-us/library/ms191234(v=sql.105).aspx


    Regards, RSingh


    Wednesday, March 05, 2014 3:25 PM
  • Hey, with this format you gave me i says:
    Msg 4866, Level 16, State 1, Line 20
    The bulk load failed. The column is too long in the data file for row 1, column 10. Verify that the field terminator and row terminator are specified correctly.

    Thursday, March 06, 2014 6:43 AM
  • RSingh assumed that you have a Windows file, and specified "\r\n as the field terminator. That will not work if this is a Unix or a Mac file. Or for that matter a Unicode file.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, March 06, 2014 8:15 AM