SQL - read CSV file with unexpected EOF error

Question

I need to read data from .csv file which contains many records, but in last row there is string "the_end" and after that there is no LF sign.

Here is my csv file:

1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
the_end

Below my sql script which reads data into temporary table:

create table #Data 
(
id int,
first_name varchar(50),
last_name varchar(50),
birthdate smalldatetime
)


bulk insert #Data
from 'C:\Users\Michał\Desktop\csvtest.csv'
with
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)


select * from #Data

drop table #Data

Error is:

Msg 4832, Level 16, State 1, Line 13
Bulk load: An unexpected end of file was encountered in the data file.

Msg 7399, Level 16, State 1, Line 13
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 13
Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".

I want to read all data into my table without this last line label "the_end", that's obvious. How can i manage that? I cannot modify this file, because it's given from outside and I cannot do that.

"I cannot modify this file, because it's given from outside and I cannot do that." Copy the file. Remove the offending line. Import the copy. — Schwern
– Schwern, Commented Mar 18, 2017 at 20:47
Sorry, I don't know SQL script. Modify the file either by hand (if it only has to be done once) or with a program, then feed the modified file to the script. — Schwern
– Schwern, Commented Mar 18, 2017 at 21:12

SqlZim · Accepted Answer · 2017-03-18 21:31:09Z

2

Use the lastrow option

bulk insert #Data
from 'C:\Users\Michał\Desktop\csvtest.csv'
with
(
FIELDTERMINATOR = ','
, ROWTERMINATOR = '\n'
, lastrow = 4
)

dynamic sql example using a variable for lastline:

create table #Data (
    id int
  , first_name varchar(50)
  , last_name varchar(50)
  , birthdate smalldatetime
);

declare @lastline int = 4;

declare @sql nvarchar(max)= '
bulk insert #Data
from ''C:\Users\Michał\Desktop\csvtest.csv''
with (
    FIELDTERMINATOR = '',''
  , ROWTERMINATOR = ''\n''
  , lastrow = '+convert(nvarchar(11),@lastline)+'
);';
exec sp_executesql @sql;

select * from #Data

drop table #Data

edited Mar 18, 2017 at 21:31

answered Mar 18, 2017 at 20:42

SqlZim

38.2k6 gold badges45 silver badges61 bronze badges

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

10 Comments

mike_pl Over a year ago

did you read exaclty my post? I have different files and in each of them in another line there is end of file.. 4 or 3333 or 34 or 22332 ...

SqlZim Over a year ago

@mike_pl I did read your post. Your post didn't mention multiple files or that you had no idea which row should be the last row.

mike_pl Over a year ago

So now you know thath :)

SqlZim Over a year ago

@mike_pl if you want to know how many lines are in a file, you can use find /v /c "" C:\Users\Michał\Desktop\csvtest.csv from a windows command prompt and it will tell you.

mike_pl Over a year ago

It won't help me ! I wrote about that, because, i cannot assign variable to LASTROW .. :/ it's foribbden

|

ASH · Accepted Answer · 2017-03-19 04:27:21Z

0

If the lastrow option doesn't work for you, manually delete the last row and try to re-run your BulkLoad process. If everything works fine, crate a VB.NET executable to open the file, strip out the last row, and re-save the file. can schedule the delete-last-row process and the BulkLoad process using the Windows Task Scheduler.

https://www.sevenforums.com/tutorials/11949-elevated-program-shortcut-without-uac-prompt-create.html

answered Mar 19, 2017 at 4:27

ASH

20.5k28 gold badges117 silver badges247 bronze badges

Collectives™ on Stack Overflow

SQL - read CSV file with unexpected EOF error

2 Answers 2

10 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

10 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related