none
BCP error: Unexpected EOF encountered in BCP data-file RRS feed

  • Question

  • Hello,

    I am using SQL2k8R2 and it often comes to a need to do big data import for analysis, I believe bcp whould be the best efficient way to go, however, the bcp is really something that you like it and you also hate it, here is my question with the detailed steps that you can use to replicate the issue on your own server, the test was done on AdventureWorks2008 but you can certainly do it in any other test DB:

    1. I create a test table:
    CREATE TABLE [dbo].[EmployeesTest](
    [BusinessEntityID] [int] IDENTITY(1,1) NOT NULL,
    [NationalIDNumber] [nvarchar](15) NOT NULL,
    [LoginID] [nvarchar](256) NOT NULL) 

    GO

    2. Add a line of data

    insert EmployeesTest (NationalIDNumber, LoginID) values ('111111', 'aaaaa')

    3. Now I export the data to csv
    bcp AdventureWorks2008.dbo.EmployeesTest out C:\Data\EmployeesTest.csv –c –t -S -T

    4. create test table for back import data

    CREATE TABLE [dbo].[EmployeesTestImport](
    [BusinessEntityID] [int] IDENTITY(1,1) NOT NULL,
    [NationalIDNumber] [nvarchar](15) NOT NULL,
    [LoginID] [nvarchar](256) NOT NULL) 

    GO

    5. Now I want to import data back, to a different table EmployeesTestImport, here I stuck on the bcp:

    5.1 I create a format file, the reason I use format file is later on I will have much bigger and complex data file for import:
    bcp AdventureWorks2008.dbo.EmployeesTestImport format nul -T -c -f C:\Data\EmployeesTest.fmt

    5.2 I use the format file in the bcp and it throws me error
    bcp AdventureWorks2008.dbo.EmployeesTestImport in C:\Data\EmployeesTest.csv -f C:\Data\EmployeesTest.fmt -S -T

    Error:
    Starting copy...
    SQLState = S1000, NativeError = 0
    Error = [Microsoft][SQL Native Client]Unexpected EOF encountered in BCP data-file

    0 rows copied.
    Network packet size (bytes): 4096
    Clock Time (ms.) Total : 1 

    What's wrong with this simple procedure?

    I then turned to Bulk Insert, and it gives me more details of the error:

    If I use Bulk Insert to do the same job, I got a little bit more info for the error:

    BULK INSERT EmployeesTestImport
    FROM 'C:\Data\EmployeesTest.csv' 
    WITH ( 
    CODEPAGE = 'RAW'
    ,DATAFILETYPE = 'char'
    ,FORMATFILE = 'C:\Data\EmployeesTest.fmt' 
    ,TABLOCK
    )
    ;


    Error:
    Msg 4832, Level 16, State 1, Line 1
    Bulk load: An unexpected end of file was encountered in the data file.
    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)".

    Thank you.

    Friday, October 24, 2014 3:40 PM

Answers

  • If you had field terminators, SSIS would be your best option because you would have characters that could be reliably employed in column definition. Since you don't have any, I guess your only option will be sticking to the method I described above.

    Having a variable ending character position for the last column won't be a problem. You'll just have to call substring(column, starting_position, len(column) - starting_position).

    Tuesday, October 28, 2014 3:08 PM

All replies

  • I did again using xml format file:

    1. create the format file

    bcp AdventureWorks2008..EmployeesTest format nul -c -t, -x -f C:\Data\EmployeesTest.Xml -T

    2. bulk insert:

    BULK INSERT EmployeesTestImport
    FROM 'C:\Data\EmployeesTest.csv' 
    WITH ( 
    CODEPAGE = 'RAW'
    ,DATAFILETYPE = 'char'
    ,FORMATFILE = 'C:\Data\EmployeesTest.xml' 
    ,TABLOCK
    )

    3. Error:

    Msg 4832, Level 16, State 1, Line 1
    Bulk load: An unexpected end of file was encountered in the data file.
    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)".

    4. The csv file is as below:

    1           111111                        aaaaa

    So, no BOM as you can see from the hex format screenshot, what's going wrong there?

    Friday, October 24, 2014 6:25 PM
  • Mdivk,

    when you bulk import data, you specify a row terminator and a field terminator. I don't know if this is being correctly set in your format file.

    Either way, the error you are getting is thrown when the process reading your file encounters a line (which is defined by a preceding row terminator) which contains a number of field terminators smaller than the expected number, calculated from the number of columns in the destination table.

    Basically, for every new row terminator ocurrence in the file, you must have exactly the same number of columns (defined by the field terminator occurrence) as the destination table, or else the EOF error will be thrown and the operation will be rolled back.

    Friday, October 24, 2014 6:45 PM
  • Thanks Samir, where did you see I have specified row/field terminator and how do you suggest the format file should be? in both xml and non-xml format?

    The format file was created based on the  table schema I'm about to import data back, presumably they should match.

    I am a beginner in terms of using bcp, sorry if my question is too obvious to you or any other one, and your help is greatly appreciated for sure.

    Friday, October 24, 2014 6:59 PM
  • Your BULK INSERT statement lacks the ROWTERMINATOR and FIELDTERMINATOR options. These parameters may or may not be in the format file. If they are incorrectly set or nonexistant, there is your problem.

    Just to make sure, I recommend you try to run the bulk insert command without using the format file. If the destination table is already created, just add the options mentioned above, with their respective values, and see if the error persists.

    BULK INSERT EmployeesTestImport
    FROM 'C:\Data\EmployeesTest.csv' 
    WITH ( 
    CODEPAGE = 'RAW'
    ,DATAFILETYPE = 'char'
    ,ROWTERMINATOR = '0x0A' -- Try this value
    ,FIELDTERMINATOR = ',' -- Replace this with your field terminator.
    ,TABLOCK
    )

    Monday, October 27, 2014 1:11 PM
  • Thanks for your reply. May I ask how to write this in bcp instead of Bulk Insert?

    Monday, October 27, 2014 1:24 PM
  • Thanks for your reply. May I ask how to write this in bcp instead of Bulk Insert?

    The fmt file is the same regardless of utility, so for bcp you have to add it to your fmt.

    Did the bulk insert statement work? This is the first step in determining if the parameters i sent you are correct (specially rowterminator).

    Monday, October 27, 2014 2:08 PM
  • Thank you Samir, yes it works. 

    Now I want to move on:

    I have a bigger data file to be imported, the file is generated as fixed-width for each field/column, I was able to generate the format file using:

    bcp Cloud.dbo.Weblog format nul -n -f C:\Weblog\WeblogFixWidth.fmt T

    and

    bcp Cloud.dbo.Weblog format nul -n –x -f C:\Weblog\ WeblogFixWidth.Xml T

    I cannot attach the test file here for your convenience as the forum doesn't seem to support it and I cannot share it with cloud drive as it is blocked in my company's network, here are two lines for example:

    326567      98          1026             11          35638468    3232282837           2259294439           2886995029           1                    395                  0
    281578      9           1026             11          48210209    3232282839           3247785220           2886998653           1                    119                  0

    I have the table schema here: (I also tried removing the first Identity field ID as it is not from import)

    CREATE TABLE [dbo].[Weblog](

          [ID] [int] IDENTITY(1,1) NOT NULL,

          [UserID] [int] NULL,

          [Category] [bigint] NULL,

          [DispositionCode] [bigint] NULL,

          [ProtocolID] [bigint] NULL,

          [UrlID] [bigint] NULL,

          [SourceServerIpInt] [bigint] NULL,

          [DestinationIpInt] [bigint] NULL,

          [SourceIpInt] [bigint] NULL,

          [TotalHits] [bigint] NULL,

          [TotalBytesSent] [bigint] NULL,

          [TotalBytesReceived] [bigint] NULL

    )

    The script I run is:

       BULK INSERT Cloud.dbo.WebLog
       FROM 'C:\Weblog\test.rpt' --data file, filetype should be text, extension not important
       WITH ( 
             CODEPAGE     = 'RAW'
            ,DATAFILETYPE = 'char'
            ,FORMATFILE   = 'C:\Weblog\WeblogFixWidth.fmt' --format file for the data file
            ,TABLOCK
            )
    ;

    It throws me error:

    Msg 4863, Level 16, State 4, Line 2
    Bulk load data conversion error (truncation) for row 1, column 1 (UserID).
    Msg 7399, Level 16, State 1, Line 2
    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 2
    Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

    Same error with or without Identity field ID and same error with fmt and xml format.

    What is wrong here?

    Thank you very much for your patient.

    Monday, October 27, 2014 2:48 PM
  • I forgot to post the format files:

    fmt:

    9.0
    11
    1       SQLBIGINT     1       8       ""   1     UserID                                   ""
    2       SQLBIGINT     1       8       ""   2     Category                                 ""
    3       SQLBIGINT     1       8       ""   3     DispositionCode                          ""
    4       SQLBIGINT     1       8       ""   4     ProtocolID                               ""
    5       SQLBIGINT     1       8       ""   5     UrlID                                    ""
    6       SQLBIGINT     1       8       ""   6     SourceServerIpInt                        ""
    7       SQLBIGINT     1       8       ""   7     DestinationIpInt                         ""
    8       SQLBIGINT     1       8       ""   8     SourceIpInt                              ""
    9       SQLBIGINT     1       8       ""   9     TotalHits                                ""
    10      SQLBIGINT     1       8       ""   10    TotalBytesSent                           ""
    11      SQLBIGINT     1       8       ""   11    TotalBytesReceived                       ""

    xml:

    <?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="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="2" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="3" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="4" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="5" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="6" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="7" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="8" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="9" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="10" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
      <FIELD ID="11" xsi:type="NativePrefix" PREFIX_LENGTH="1"/>
     </RECORD>
     <ROW>
      <COLUMN SOURCE="1" NAME="UserID" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="2" NAME="Category" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="3" NAME="DispositionCode" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="4" NAME="ProtocolID" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="5" NAME="UrlID" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="6" NAME="SourceServerIpInt" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="7" NAME="DestinationIpInt" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="8" NAME="SourceIpInt" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="9" NAME="TotalHits" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="10" NAME="TotalBytesSent" xsi:type="SQLBIGINT"/>
      <COLUMN SOURCE="11" NAME="TotalBytesReceived" xsi:type="SQLBIGINT"/>
     </ROW>
    </BCPFORMAT>

    Thanks.

    Monday, October 27, 2014 2:50 PM
  • Hello,

    1. I created fmt file by executing 

    C:\Users\vaibhav>bcp MyDB.dbo.Weblog format nul -c -f c:\temp\format1.fmt

    2. Below is generated .fmt file

    10.0
    11
    1       SQLCHAR             0       21      "\t"     1     UserID                                   ""
    2       SQLCHAR             0       21      "\t"     2     Category                                 ""
    3       SQLCHAR             0       21      "\t"     3     DispositionCode                          ""
    4       SQLCHAR             0       21      "\t"     4     ProtocolID                               ""
    5       SQLCHAR             0       21      "\t"     5     UrlID                                    ""
    6       SQLCHAR             0       21      "\t"     6     SourceServerIpInt                        ""
    7       SQLCHAR             0       21      "\t"     7     DestinationIpInt                         ""
    8       SQLCHAR             0       21      "\t"     8     SourceIpInt                              ""
    9       SQLCHAR             0       21      "\t"     9     TotalHits                                ""
    10      SQLCHAR             0       21      "\t"     10    TotalBytesSent                           ""
    11      SQLCHAR             0       21      "\r\n"   11    TotalBytesReceived                       ""

    3. Executed below in SSMS and got records

    -Vaibhav Chaudhari



    Tuesday, October 28, 2014 10:49 AM
  • Mdivk,

    take a look at the image below, it should help you understand the fmt file structure:

    Now the error message returned by the bcp utility changed from being a terminator error to a data type truncation error. It happened for the first row read by bcp (which makes me assume that this is an incorrectly generated format file and not an isolated, rogue row), and for column 1, which, in your case, is UserID.

    Try to increase the size of the host file data length from 21 to something higher. Use your data file as a reference if you are not sure which value to set this to. Make sure the destination column's length matches the value you set in the fmt file.

    Tuesday, October 28, 2014 1:02 PM
  • Hello,

    1. I created fmt file by executing 

    C:\Users\vaibhav>bcp MyDB.dbo.Weblog format nul -c -f c:\temp\format1.fmt

    2. Below is generated .fmt file

    10.0
    11
    1       SQLCHAR             0       21      "\t"     1     UserID                                   ""
    2       SQLCHAR             0       21      "\t"     2     Category                                 ""
    3       SQLCHAR             0       21      "\t"     3     DispositionCode                          ""
    4       SQLCHAR             0       21      "\t"     4     ProtocolID                               ""
    5       SQLCHAR             0       21      "\t"     5     UrlID                                    ""
    6       SQLCHAR             0       21      "\t"     6     SourceServerIpInt                        ""
    7       SQLCHAR             0       21      "\t"     7     DestinationIpInt                         ""
    8       SQLCHAR             0       21      "\t"     8     SourceIpInt                              ""
    9       SQLCHAR             0       21      "\t"     9     TotalHits                                ""
    10      SQLCHAR             0       21      "\t"     10    TotalBytesSent                           ""
    11      SQLCHAR             0       21      "\r\n"   11    TotalBytesReceived                       ""

    3. Executed below in SSMS and got records

    -Vaibhav Chaudhari



    Thanks Vaibhav ,

    I believe there is difference between the data file you created and my original data file, although they might look like the same.

    Mine is fixed-width and yours is TSV.  

    Your format file won't work on fixed width data. unfortunately. 

    Tuesday, October 28, 2014 1:38 PM
  • Mdivk,

    take a look at the image below, it should help you understand the fmt file structure:

    Now the error message returned by the bcp utility changed from being a terminator error to a data type truncation error. It happened for the first row read by bcp (which makes me assume that this is an incorrectly generated format file and not an isolated, rogue row), and for column 1, which, in your case, is UserID.

    Try to increase the size of the host file data length from 21 to something higher. Use your data file as a reference if you are not sure which value to set this to. Make sure the destination column's length matches the value you set in the fmt file.

    Thanks Samir, I knew the format, it is just pissing me off when comes to fixed width data, I have no issue with CSV or TSV.
    Tuesday, October 28, 2014 1:39 PM
  • From the data you posted above, it appears you don't have any column terminators right? Each column is delimited by absolute character positions within a row (aka blocks).

    If this is the case, you shouldn't use format files. You'll have to either use a function to split the rows or store each column starting and ending positions in a reference table, import data by ignoring the field terminators, and split each column by calling substring(column, start, end-start).

    Tuesday, October 28, 2014 2:30 PM
  • From the data you posted above, it appears you don't have any column terminators right? Each column is delimited by absolute character positions within a row (aka blocks).

    If this is the case, you shouldn't use format files. You'll have to either use a function to split the rows or store each column starting and ending positions in a reference table, import data by ignoring the field terminators, and split each column by calling substring(column, start, end-start).

    Thank you Samir, I guess you are right, using a format file is causing headache on this case: the data is not well formed: 12 fields with fixed width and the last one is variable!

    As the data sometimes can go over GB, which makes it difficult to transform data in advance. I wonder maybe SSIS is the option I should take? I have no previous experience on SSIS, any existing walkthrough sample?

    Thank you again for your help.


    • Edited by mdivk Tuesday, October 28, 2014 3:00 PM
    Tuesday, October 28, 2014 2:59 PM
  • If you had field terminators, SSIS would be your best option because you would have characters that could be reliably employed in column definition. Since you don't have any, I guess your only option will be sticking to the method I described above.

    Having a variable ending character position for the last column won't be a problem. You'll just have to call substring(column, starting_position, len(column) - starting_position).

    Tuesday, October 28, 2014 3:08 PM