I'm importing a tab delimited text file to SQL Server 2012 via BCP. It's working except for the final line of the file, which is not imported. This is not the usual unexpected EOF issue where the delimiters are set incorrectly or a comma exists in data using a comma delimiter. The field delimiter is explicitly set to \t and the row delimiter is explicitly set to \n. On checking the final line in Notepad ++ it correctly contains the data and tab characters. It does not, however, have an end-of-line delimiter. Instead the file just stops with the final tab character (the last field of the row is null). Which to the best of my knowledge is normal.
The weird part is that if I add a end-of-line delimiter to the end of the file the final line imports correctly. The only unusual thing about the final line is that the data for the first field contains a backtick before the actual character data. But backticks aren't used as delimiters so that seems unlikely to be the problem, especially as the line does import when I add an "extra" end-of-line delimiter.
So, are delimited text file supposed to always have an end-of-line delimiter at the end of the data? Or is something else going on here?
Anonymized BCP code:
bcp DATABASENAME.dbo.tablename in "L:\directoryname\filename.txt" -c -S servername -U username -P passwordtext -F 2 -t \t -r \n
As @TT noted, you have sussed the problem. One fix could be to use PowerShell to ensure there is a closing CR/LF on the file:
powershell "Get-Content -Path .\original.tsv | Set-Content -Path .\corrected.tsv;"
If there is one, it will still be there and no new one will be added. If it is missing, it will be added.
Then modify your BCP line to use the corrected filename for the input, and clean up after yourself as normal.