Not all CSV/XML Rows Loading

[#1]

Help! So frustrating - I have a 900,000 row CSV, or XML (or even JSON!) and only 450,000 or so get loaded into Qlikvie with a Load *.

Why is this happening?! Any ideas?

[MarcoWedel]

can you post a smaller example that shows this effect?

[#3]

Hi Marco - thanks for the reply.

I've solved it (I think) - it was a Code Page issue. When I changed to Code Page to Arabic it pulled in nearly 1.2million tweets.

BUT!

I don't think that's all of them! Is there a Codepage (or a workaround or something?) that will pull every single row?

Why do some codepages work better than others when I am loading my CSV in?

Thank you!

[ashfaq_haseeb]

Hi,

I had seen this issue with trial version on qvsource connector?

if you are using qvsource you need to talk to them.

Regards

ASHFAQ

[jaimeaguilar]

Hi,

this usually occurs because within your data there's  a character that is used as separator/Delimiter. For example there could be a comma or semicolon in your data that is interfering with the load,

regards

[#6]

Thanks for all the help, everyone - I think I'm getting closer.

One of the key things was pressing the addition of no eof as below:

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

So with that addition all my rows load, and the row count is correct. However - some of the data is mixed up, with maybe 15% of the rows scrambled and under the wrong column headings. Few questions, then:

1. What exactly is 'no eof' doing to my data?

2. Could adding 'no eof' to my data cause issues with the formatting of my table, causing the data to end up in the wrong column?

3. Reading around, 'no eof' solutions tend to indicate there is a symbol (as mentioned by Jaime Aguilar above) that is interrupting the data load. Is there a better solution? Can I identify the character and remove it?

4. And finally, will a tab-delimited .csv file cause problems? (as compared to a tab-delimited .tsv file)

Thank you for all the help guys, really appreciate it.

[jaimeaguilar]

Hi,

1. the 'no eof' option allows you to use an ASCII character that indicates the end of the file. In general, this can be useful to tell qlikview where to start loading data.

In your case I definitely think the problem is a character thar is stoping the load. It could be a little tricky to debug which record is causing this issue, but you can look at the last row that is being loaded in QlikView. You can use RecNo() as Field in script to add a counter to your loaded data so you can identify the last row that is being loaded into QlikView.

If everything goes well, in that last record you will find the character that is causing trouble. I'm think that the character that is causing trouble is a single or double quote.

Let me put an example:

suppose you have a CSV file with the following records:

1, x, some text,

2, y, some text

3, z, "some , " text",

in this example the last row would be causing trouble because the double quotes are being used to escape the comma that is included in the data, so QlikView knows that this comma is not a separator, The problem is that other double quote in the middle. In this case QV would not know how to handle it and then the load will end,

hope this helps,

regards

[#8 · Accepted solution]

Hey guys,

I think it's solved. The " character in CSVs screws the load.

A find and replace on the CSV allowed to load without any issues, removing the " characters.

Hope this helps anyone else with the same issue.

A