BULK INSERT missing last row?

Question

I use BULK INSERT for my text files. Everything works fine but one thing that I discovered, If I give the final line's final column a value, it will import. If the value of that final column in the final line is blank, it discards the line, despite the fact that the destination column allows nulls! Text file uses tab delimiter, here is example of the last row data:

Mike    Johnson 1/29/1987   M

if I have any value in the last column field row will be inserted, example here:

Mike    Johnson 1/29/1987   M   test

This is my BULK Insert:

BULK INSERT ##TEMP_TEXT
FROM '#uncdir#\#cffile.ServerFile#'
WITH (
    FIELDTERMINATOR = '\t', 
    ROWTERMINATOR = '\n'
)

I tried to use \r instead of \n but that did not fix the problem. I also researched on some websites but could not find any solution. I'm wondering this is something that can be fixed in SQL. If someone knows how this can be fixed please let me know.

SOLUTION:

For anyone how use ColdFusion here is the line that will add newline in the text file.

exec xp_cmdshell 'echo. >> "#uncdir#\#cffile.ServerFile#"';

Key was to put double quotes around coldfusion variables, otherwise code does not work.

uncdir code is here:

<cfset uncdir = createObject("java","java.net.InetAddress").getLocalHost().getHostName()/>

cffile.ServerFile you can get from the form. I used JQuery to submit the text file. I hope this helps. Thank you.

Answer 1

I reproduced your issue on SQL Server 2008 R2. The solution is as simple as adding a newline to your file so that the last row terminates with a newline.

I created two files:

1. 
2. 

Then ran the following script:

CREATE TABLE #t(first_name VARCHAR(128),last_name_etc VARCHAR(128),sex CHAR(1),test VARCHAR(128));

BULK INSERT #t
FROM 'C:\temp\without_newline.txt'
WITH (
    FIELDTERMINATOR='\t',
    ROWTERMINATOR='\n'
);

SELECT * FROM #t;

TRUNCATE TABLE #t;

BULK INSERT #t
FROM 'C:\temp\with_newline.txt'
WITH (
    FIELDTERMINATOR='\t',
    ROWTERMINATOR='\n'
);

SELECT * FROM #t;

DROP TABLE #t;

Result 1:

first_name  | last_name_etc     | sex | test
--------------------------------------------
Tom         | Jackson 2/28/1986 | M   | test

Result 2:

first_name  | last_name_etc     | sex | test
--------------------------------------------
Tom         | Jackson 2/28/1986 | M   | test
Mike        | Johnson 1/29/1987 | M   | NULL

---

The solution should be as simple as making sure the last line terminates with \r\n. Either you change the process that generates the text file or do it manually right before you do the bulk insert.

One way to do this manually would be to run EXEC xp_cmdshell 'echo. >> C:\temp\without_newline.txt' right before you do the bulk insert.