topic Re: Load from txt not loading all records in QlikView

Load from txt not loading all records

bazzaonline — Fri, 12 Dec 2014 08:23:15 GMT

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?

Re: Load from txt not loading all records

francoiscave — Fri, 12 Dec 2014 08:36:28 GMT

Hi Paul,

Qlikview display only the distinct rows after load .

Can you add the RowNo() function in your script to be certain that there are no "duplicate line".

François

Re: Load from txt not loading all records

arulsettu — Fri, 12 Dec 2014 08:41:50 GMT

post your text file

Re: Load from txt not loading all records

bazzaonline — Fri, 12 Dec 2014 08:45:44 GMT

Morning and thank you, I have opened the txt file in excel and completed a duplication check and there are no dupplicates?

Re: Load from txt not loading all records

bazzaonline — Fri, 12 Dec 2014 08:46:57 GMT

Sorry, thank you for the offer of support however the file contains information I'm not permitted to share.. Dpa issue

Re: Load from txt not loading all records

flipside — Fri, 12 Dec 2014 08:51:20 GMT

A common issue with text files is special (and non-visible) characters causing Qlikview to think it has reached the end of the file. If the load is always ending at the same record, then this is probably the reason. Does the last record end part-written? Try making a copy of the text file, delete the final record it is loading and see if it loads more records.

Fixing it is more problematic but try the above first and let us know if it changes things.

Re: Load from txt not loading all records

Anonymous — Fri, 12 Dec 2014 08:51:35 GMT

Hello,

It did not happen to me but I saw some cases which happened to my friends here.

In that case, it might be a bug or something after a certain amount of rows. You may try things like:

1. loading and writing it to a qvd immediately. Then try to read from qvd and see if anything changes.

2. Try to read from csv.

3. You may ask to technical support if nothing works. If you are in older version, it may have been fixed or something.

Serhan

Re: Load from txt not loading all records

francoiscave — Fri, 12 Dec 2014 08:53:46 GMT

Hi Paul again,

There is a limit when you load .txt file ?

See the link below to have help:

http://community.qlik.com/message/44925#44925

François

Re: Load from txt not loading all records

bazzaonline — Fri, 12 Dec 2014 08:54:39 GMT

Thank you, I think that might be the case. Is there a quick way to work out which was the last record?

Re: Load from txt not loading all records

bazzaonline — Fri, 12 Dec 2014 09:01:38 GMT

Ah, nice work. Amending to csv seems to have sorted it!

Re: Load from txt not loading all records

flipside — Fri, 12 Dec 2014 09:03:56 GMT

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.

Re: Load from txt not loading all records

Anonymous — Fri, 12 Dec 2014 09:12:16 GMT

You may consider it as helpful then

Serhan

Re: Load from txt not loading all records

flipside — Fri, 12 Dec 2014 09:41:12 GMT

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.