SQL Server Bulk Insert fixed width file failure

Question

I am attempting to Use Bulk Insert to upload a very large data file (5M rows). All columns are just varchars no conversion. So the Format file is simple...

11.0
29
1   SQLCHAR     0   8   ""  1   AccountId       ""
2   SQLCHAR     0   10  ""  2   TranDate        ""
3   SQLCHAR     0   4   ""  3   TransCode       ""
4   SQLCHAR     0   2   ""  4   AdditionalCode      ""
5   SQLCHAR     0   11  ""  5   CurrentPrincipal    ""
6   SQLCHAR     0   11  ""  6   CurrentInterest     ""
7   SQLCHAR     0   11  ""  7   LateInterest        ""
...
27  SQLCHAR     0   8   ""  27  Operator        ""
28  SQLCHAR     0   10  ""  28  UpdateDate      ""
29  SQLCHAR     0   12  ""  29  TimeUpdated     ""

but each time, at some point, I get the same 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)".

I have tried the following:

Bulk Insert 
    [TableName] From 'dataFilePPathSpecification'
    With (FORMATFILE = 'formatFilePPathSpecification')

but I get the error after about 5-6 minutes, and no data has been inserted.

When I added BatchSize parameter, I get the error after a much longer time, near the end of the file, after all except a very few of the rows have been inserted successfully.

Bulk Insert 
    [TableName] From 'dataFilePPathSpecification'
    With (BATCHSIZE = 200, 
          FORMATFILE = 'formatFilePPathSpecification')

When I set the BatchSize to 2000 it runs much faster, (Fewer, larger transacxtions I assume), but it still fails.

Does this have something to do with how the Bulk Insert recognizes the end of the file? If so, what do I need to do to the format file to fix it ?

Janine Rawnsley · Accepted Answer · 2014-06-27 15:39:24Z

1

Explicitly state your row terminator:

    BULK INSERT TableName FROM 'Path'
       WITH (
          DATAFILETYPE = 'char',
          ROWTERMINATOR = '\r\n'
          With (FORMATFILE = 'formatFilePPathSpecification')
    );

If this still fails, check your file to see if you have unexpected terminators embedded in text fields.

answered Jun 27, 2014 at 15:39

Janine Rawnsley

1,2582 gold badges10 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Charles Bretana Over a year ago

My data file was a fixed width file, no field terminators, but what do you mean by line and row terminators, that's the same thing, no? - or did you just misspeak?

Charles Bretana Over a year ago

thought so... You're right, then, that fixed it... But I had to put "\r\n" for the row (last field) terminator

Janine Rawnsley Over a year ago

Yeah, depends largely on the file you're pulling in. Have updated the answer accordingly..

Brandon · Accepted Answer · 2014-06-27 14:08:16Z

1

Trying using the errorFile specifier in the WITH portion to find the offending data:

ERRORFILE = 'C:\offendingdata.log'

answered Jun 27, 2014 at 14:08

Brandon

67010 silver badges22 bronze badges

Comments

Gary Walker · Accepted Answer · 2014-06-27 14:23:06Z

If you still have problem even after enabling the errorfile output, you can do a binary search for the problem by setting the FirstRow and LastRow options and running bulk insert repeatedly to isolate the problem.

To be honest your input format looks so simple it might be a good idea to write a small C#, Python, or whatever floats your boat app to quality check you data before attempt import. You could simply discard invalid rows (or possibly fix them) or write them to an exceptions file for hand processing, or simply stop the job -- I.e., file must be perfect or it is considered corrupted. Validating 5M rows this way will be quite fast -- essentially as fast as you can read the file (and possible write) the file.

Charles Bretana · Accepted Answer · 2014-06-27 15:37:50Z

Thanks for the suggestions to all, I applied both ideas... I wrote a small .Net (c#) file processor utility and it told me there were additional nulls (binary zeroes (\0) at the end of every line, and I was able to strip them off using a simple c# program.

The error file indicated the issue was at the very end, (That's what the error msg said!)

The actual issue was that the Bulk Insert could not recognize the EOF.. I had to modify the format file like this to fix it.. Then it worked.

11.0
29
1   SQLCHAR     0   8   ""      1   AccountId       ""
2   SQLCHAR     0   10  ""      2   TranDate        ""
3   SQLCHAR     0   4   ""      3   TransCode       ""
4   SQLCHAR     0   2   ""      4   AdditionalCode  ""
5   SQLCHAR     0   11  ""      5   CurrentPrincipa ""
6   SQLCHAR     0   11  ""      6   CurrentInterest ""
7   SQLCHAR     0   11  ""      7   LateInterest    ""
...
27  SQLCHAR     0   8   ""      27  Operator        ""
28  SQLCHAR     0   10  ""      28  UpdateDate      ""
29  SQLCHAR     0   12  "\r\n"  29  TimeUpdated     ""

Collectives™ on Stack Overflow

SQL Server Bulk Insert fixed width file failure

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

3 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related