CODEWITHSUNDEEP
sqlsql-serverbulkinsert
Charles Bretana
Charles Bretana

Reputation: 146409

SQL Server Bulk Insert fixed width file failure

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 ?

Upvotes: 0

Views: 2201

Answers (4)

Janine Rawnsley
Janine Rawnsley

Reputation: 1258

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.

Upvotes: 1

Charles Bretana
Charles Bretana

Reputation: 146409

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     ""

Upvotes: 1

Gary Walker
Gary Walker

Reputation: 9134

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.

Upvotes: 1

Yoseph
Yoseph

Reputation: 660

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

ERRORFILE = 'C:\offendingdata.log'

Upvotes: 1

Related Questions