Problem with bulk insert - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Problem with bulk insert

Author

Topic

Kasper Lund
Starting Member

1 Post

Posted - 2012-02-23 : 02:44:54

Hey

i got some SQL that is loading a deltafile every day to a table on a sqlserver.

I use bulkinsert but i got one problem:

at the deltafile (tekstfile) i have a [LF] at the end of the last line, witch gives me a blank line at the bottom.

i use the followingen bulkinsert:

DECLARE @sql AS VARCHAR(4000)
SET @sql = 'BULK INSERT Data.dbo.Temp_indlaest FROM ''G:\SQLData\DELTA.csv'' WITH (CODEPAGE=''1252'', FIELDTERMINATOR=''|'', ROWTERMINATOR=''' + CHAR(10) + ''')'
PRINT @sql
EXECUTE (@sql)

and i get this error:

Server: Msg 4832, Level 16, State 1, Line 1
Bulk Insert: Unexpected end-of-file (EOF) encountered in data file.
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAM' reported an error. The provider did not give any information about the error.
OLE DB error trace [OLE/DB Provider 'STREAM' IRowset::GetNextRows returned 0x80004005: The provider did not give any information about the error.].
The statement has been terminated.

My plan was to use LASTRAW (filecount -1)', but i got no clue how to do that.

If i manualy delete the last line in the file my bulk insert works just fine.

hope someone can help.

//Kasper

just here to look :)

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-02-23 : 07:02:59

When I have run into this problem, the only solutions I have found are one or the other of the following, each of which is somewhat unsastisfactory:

a) Insert the file as one whole string into a staging table that has just one VARCHAR(MAX) column, and use T-SQL to parse the data.

b) Use something like Powershell (or anything else) to preprocess the file to strip out the last line.

c) Use something like Powershell (or anything else) to calculate the number of lines in the file and then use that number in the LASTROW option.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2012-02-23 : 07:27:36

ROWTERMINATOR = 0x0A

If you use binary value for terminator, you will be ok.

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources