BULK INSERT missing last row?

Question

Post reply

BULK INSERT missing last row?

draca.milos89

SSC Enthusiast

Points: 185
More actions
November 21, 2016 at 7:47 pm

#315180

Hello, I have text files that are tab delimited. My Bulk works fine until I discovered one situation if I value in the last row column filed is empty Bulk will not insert that row. If I put any value in that field insert works fine. Here my code that I use:
BULK INSERT ##TEMP_TEXT
FROM '#uncdir#\#cffile.ServerFile#'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
)
have tried to replace ROWTERMINATOR to \r and 0x0a but that did not fix the problem. I spent many hours tring to fix this problem but till did not get any solution.
My data row looks like this:
Mike Johnson 1/29/1987 M
After letter M is a tab delimiter with an empty value for that column. In this case that row will not be inserted in DB. Please if anyone can help let me know.

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

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

Eirikur Eiriksson SSC Guru Points: 182948 More actions · Answer 1

Eirikur Eiriksson

SSC Guru

Points: 182948

November 22, 2016 at 2:28 am

#1913924

Have you tried adding KEEPNULLS?

😎

draca.milos89 SSC Enthusiast Points: 185 More actions · Answer 2

I have tried KEEPNULLS but still missing my last row...

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

draca.milos89 (11/21/2016)
Hello, I have text files that are tab delimited. My Bulk works fine until I discovered one situation if I value in the last row column filed is empty Bulk will not insert that row. If I put any value in that field insert works fine. Here my code that I use:
BULK INSERT ##TEMP_TEXT
FROM '#uncdir#\#cffile.ServerFile#'
WITH (
FIELDTERMINATOR = '\t',
ROWTERMINATOR = ''
)
have tried to replace ROWTERMINATOR to \r and 0x0a but that did not fix the problem. I spent many hours tring to fix this problem but till did not get any solution.
My data row looks like this:
Mike Johnson 1/29/1987 M
After letter M is a tab delimiter with an empty value for that column. In this case that row will not be inserted in DB. Please if anyone can help let me know.

Do you have a row delimiter at the end of your last row? If the row is not complete, it might not import it.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

draca.milos89 SSC Enthusiast Points: 185 More actions · Answer 4

Yes my row ends with delimiter . The only solutions that i could find so far was using cmd_schell to add the new line. This solution worked for some of my text files but not for all of them.