SQL Server Bulk Insert with condition if the line is empty, do not read it

Question

I just want to ask if there is a way to use the bulk insert feature but to check if the last line is empty and skip it.

I have a text file that is being populated with data but the last line will always be empty cause when it repopulates, it will start from there and the the end of the previous line that is already populated.

My query so far looks like this:

BULK INSERT #TEMP 
FROM 'C:\Test\Test.txt'
WITH (FIELDTERMINATOR ='\t', 
      ROWTERMINATOR = '\r', 
      FIRSTROW = 2, KEEPNULLS)

It will then be input into a temp table but the query will not go this far because of the last line in the text file. Is there a setting to say, skip the last line if its empty?

Which dbms are you using? (Product specific question...) – jarlh Dec 07 '15 at 13:07 — jarlh, Dec 07 '15 at 13:07

score 0 · Answer 1 · answered Dec 07 '15 at 13:38

0

If you're expecting one line to be nonimportable then include

MAXERRORS =1

In the command and BULK INSERT should import the other ones fine.

BULK INSERT #TEMP 
FROM 'C:\Test\Test.txt'
WITH (FIELDTERMINATOR ='\t', 
      ROWTERMINATOR = '\r', 
      MAXERRORS =1,
      FIRSTROW = 2, KEEPNULLS)

Unless there's an unexpected problem in another line (in which case you'll want to trap the error anyway)

https://msdn.microsoft.com/en-us/library/ms188365.aspx

answered Dec 07 '15 at 13:38

Tom Page

1,211
1
7
8

If it was only that easy lol, for some reason it doesn't allow it. If i manually delete the last empty line all works perfect – Christoph Bethge Dec 07 '15 at 15:38

score -1 · Answer 2 · edited May 23 '17 at 11:59

-1

Well i found my answer here:Difference between CR LF, LF and CR line break types?

What i did is changed the from ROWTERMINATOR = '\r' to ROWTERMINATOR = '0x0a'

You can read up on the link about the differences in end of line chars which soved my issue. I hope this works for others as well! Best of luck guys!

edited May 23 '17 at 11:59

Community

1
1

answered Dec 07 '15 at 19:30

Christoph Bethge

217
1
5
20

SQL Server Bulk Insert with condition if the line is empty, do not read it

2 Answers2