QV loads only 20% of data from a txt

[#1]

Dear all,

I really don' t know where to look at anymore.

I have a series of txt files (monthly orders line) that are loading fine.

This one instead, is loading only ~30k lines out of a total of ~147k lines.

Excel loads all the line, Access loads all the lines, QV not.

I really don't know what is wrong. I also checked in Excel if there were duplicated rows, but I could find only 200, so a negligible amount.

I know it is a long shot, but do you have any idea why this could possibly happen??

thank you so much!

[rajeshvaswani77 · Accepted solution]

Hi Eric,

I have also faced this.

At that point the txt file would not have the file structure in the expected format.

Depends on the delimiter. you would have to find that line and provably cleanse.

Alternately if you use a splitter tool and split file in some 10 parts, you will find it easy to narrow down to the problem area.

Hope this helps.

thanks,

Rajesh Vaswani

[#3]

I ran into a similar issue.  I found special escape characters in my file that was killing it. The good news is that you know exactly where it failed in the file.  So look at lines 30645 and 30646 with a good text editor that can view escape characters (Notepad++ or SciTE), and see if there is anything odd.

[Anonymous]

Try to add option No EOF.  From help:

no eof

If this option is used, ASCII character 26 which otherwise denotes
end-of-file is disregarded. If this specifier is used, character 26 can be part
of a field value.

Regards,

Michael

[rwunderlich]

This is frequently caused by an improperly escaped delimiter. For example, the input file using quoting but double quote appears in the data, like 17" Screen. Look at the data around where it's failing as others have suggested.

-Rob

[#6]

thanks for the suggestions.

I had a peek in Excel at that row .

Indeed there is a cell with this text

F├£R FORSCHUNG  "

If I understood your comments right, it is the " causing problems; the character ├ , looking pretty odd, should be digested by QV?

EDIT: setup delimiter n the script is actually

delimiter is '\t'

so why is the " causing troubles? Shouldn't it be seen as a normal character?

A point which is more important.

Don't you think this is a huge pitfall of QV? Now I could spot this error because the delimiter character was luckily placed at the beginning of the source.

How about it happens close to the end, when 90% of records are loaded?
I would never notice that and QV would not warn me.

I see this as a severe pitfall.

[rwunderlich]

I'm guessing you also have the "msq" spec in your load? That would tell QV that that quotes are properly escaped, and if they are not the results are unpredictable.

-Rob

[#8]

Dear Rob,

actually, this appears to be the end of my load script

FROM

(txt, utf8, embedded labels, delimiter is '\t', msq);

So, if I understood you rightly, that should have avoided for QV to get into troubles with the ".

But it did not!

[rwunderlich]

No, the MSQ will not avoid the problem. It specifies that quotes are properly escaped and balanced. If not, you will have problems. So perhaps you should remove the msg for your case.

-Rob

[#10]

Dear Rob,

thanks for the tip.

I actually removed the MSQ parameter from the string and the load works smoothly.

Can you please make me understand what is the added value of having the MSQ parameter?

thanks!