Load from txt not loading all records

[bazzaonline]

Hello,

I need help, I'm trying to load data from a txt. file which contains about 600k rows, its only picking up 300k rows for some reason and I have no idea why??

Any help?

[flipside]

You could load the file with a rowno() field, then select the max row number from a list box.

To fix this there are a few methods ...

1) Get the source of the data changed to output a different way - XML perhaps.

2) Try loading the data as full line records (without delimiters) into a temp table then parse the rows to split into fields.

3) Run the text file through a vbs (for example) routine to remove special characters* causing the problem.

* EDIT - Once you've identified the offending row, try using an advanced text editor to see if you can see what the problem character is on that row, possibly just a line feed char.

[Anonymous]

You may consider it as helpful then

BR

Serhan

[flipside]

Glad you got it sorted. Note though that a csv file is still just a text file and can introduce further issues if, for instance, someone has used a comma in a free-text field at the point of entry, causing fields to split in the wrong place. Many systems get around this by putting double-quotes around text fields when creating the csv file, but this introduces a new problem if someone uses a double-quote in the free-text field - it becomes a never ending circle of handling characters. The worst scenario is when financial figures end up in the wrong column and it isn't spotted - sometimes having it fail completely is preferred!

If you are designing a new system and have to rely on files from a system you do not control, XML is the best method.