Bulk Insert - An unexpected end of file was encountered in the data file.

Question

Bulk Insert - An unexpected end of file was encountered in the data file.

AMCiti

SSC-Addicted

Points: 496
More actions
December 2, 2020 at 10:13 pm
Go to Answer
#3816260
Dear Group:
I am trying to perform a BULK INSERT using the code below. However, this is causing the following errors:
I know the problem, but not sure of how to solve for it (if it can be solved with BULK INSERT). The issue is that our tech department's standard is that every file has a trailer record and this last record in the file is entirely the trailer record. It is expecting more column delimiters, therefore gets unexpected end of file. Is there nothing we can do to ignore the last line of a file for BULK INSERT much like we can the FirstRow?
```
Msg 4832, Level 16, State 1, Line 4
Bulk load: An unexpected end of file was encountered in the data file.
Msg 7399, Level 16, State 1, Line 4
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 4
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".
```
```
BULK INSERT Auto_FileProcess
FROM '\\BulkInsert\Automated_FileProcess.txt'
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '0x0A',
);
```
frederico_fonseca

SSCoach

Points: 16154
More actions
December 3, 2020 at 6:21 pm
Answer
#3816634
to load the full file without errors you basically define a table with a single column and you load the file onto it without specifying field delimiters - only record delimiter
```
create table #big_single_record_table
(record varchar(max)
)

BULK INSERT #big_single_record_table
FROM '\\BulkInsert\Automated_FileProcess.txt'
WITH
(
ROWTERMINATOR = '0x0A',
);
```
once you have this temp table loaded you can count how many records you have.

or you may just use it to do the insert onto the final table if you can identify the bad records.
insert into final
select convert(date, (substring(record, 1, 10)) as date_field
, substring(record, 11, 50) as address_field
....
from #big_single_record_table
where record not like '%XXXX%' -- for header row
and record not like '%YYYYY%' -- for trailer row

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 1

Check the resulting table. You may find that all but that last row has been bulk loaded. Both BCP and BULK INSERT default to allowing up to 10 errors without rejecting the whole load.

If the row counts in the target table include everything except that last row, you're golden... although you may want to change the error tolerance from 10 to just 1.

Otherwise, you have two choices...

Get the people responsible for creating the file to move that last row to a "manifest" file leaving the main file with only consistent rows in them for clean loads. This is the best of both worlds because you can read the "manifest" file to know how many rows should be loaded (if that last row contains such a thing, and it should).
Load all the rows where each row is just one huge "record" and count them. Then do another load with the normal parsing using the "LastRow" option set to the number of rows first imported minus 1. Note that you cannot do similar with unwanted rows at the beginning of a file because neither BCP nor BULK INSERT actually count rows. Instead, they count delimiters.

Actually, there are two other choices but they're comparatively much slower and much more difficult to write. Option 1 above is the best option (other than embedding the manifest information into the file name itself).

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

AMCiti SSC-Addicted Points: 496 More actions · Answer 2

Thank you for the response but unfortunately, we are locked into this file type as it is directed from above this is how all files are created as it is company standard. I have also found that the entire load is rejected even though these are the only errors. As such, I am not sure how to "load all rows" and then do a second load, as I cannot get anything to load at all.

Jeff Moden SSC Guru Points: 1004324 More actions · Answer 3

I'm not a PowerShell affection ado but it seems like the script at the following URL would be of help rather than using Option #2 above. I recommend making a copy of the file prior to testing the script.

https://www.poshcodebear.com/blog/2014/2/1/removing-the-first-or-last-lines-from-a-text-file

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)