Last line of csv never imports

Question

Last line of csv never imports

EamonSQL

SSCrazy

Points: 2307
More actions
August 10, 2006 at 7:28 am

#70924

Hi,
I've been importing alot of different csv files lately and have been having a problem with loosing the last line/record on each occasion. I've been importing a variety of different files and all are the same.
Has anyone else experienced this type of problem and is there any obvious problem with the way I am importing the data. Or is there something I should set along the way.
Any help welcome,
Eamon

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

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

John Trant SSC Rookie Points: 41 More actions · Answer 1

If you are importing your text files using a "text file source" connection, after choosing your file name in the connection properties dialog and then selecting the properties button, you get a "text file properties" dialog. There is a "row delimeter" drop down. Does the last line in your text file contain the row delimiter specified in this drop down? if not, it is probably getting bypassed.

ptap SSC Rookie Points: 28 More actions · Answer 2

Hi!

You could open the csv file in a text editor... Make an empty line (record) after the last line (record). Please check if it can be imported correctly...

Maybe this will help...

Malcolm Bee SSC Veteran Points: 271 More actions · Answer 3

I was having the same problem with csv files created by an external program with a {CR}{LF} row delimiter.

I got around it by having an the following activex script in my dts package immediately before the import, this adds in the final {CR}{LF} so that the last line is not missed on the import. Replace the open and create filenames with the ones you are using:

Function Main()

dim ofso

dim oSource

dim oDestination

SET ofso = CreateObject("Scripting.FileSystemObject")

SET oSource = ofso.OpenTextFile("d:\Racing\Price\zzz_br$24.csv")

SET oDestination = ofso.CreateTextFile("d:\racing\SSImport.csv", True)

oDestination.WriteLine oSource.ReadAll()

oSource.Close

oDestination.Close

SET ofso=Nothing

SET oSource=Nothing

SET oDestination=Nothing

Main = DTSTaskExecResult_Success

End Function

Randy Doub SSCertifiable Points: 5322 More actions · Answer 4

Similar to the last solution of adding the final row delimeter yourself, I copy text files to a staging folder on the sql server using xp_cmdshell. In the copy command I append a second text file that contains only {cr}{lf}. Appropriately named crlf.txt. The file is then imported from the staging folder. Its never failed.