topic Loading a huge CSV file : problems with Memory in QlikView

Loading a huge CSV file : problems with Memory

Fri, 03 Dec 2010 11:52:50 GMT

Hello !

We have a huge CSV file that needs to be loaded and further saved as a QVD.

Cause within our installation, data tables are alwayes dumped into CSV files and then transfromed into QVDs.

The real applications (QVWs) read those QVDs (and not the original database or CSV file). This is way faster to develop.

So far , so good.

Now it happens that we have a huge CSV file that cannot be loaded by QV.... What we have observed :

The file does not have weird characters. It is a typical CSV files with letters, numbers and some punctuation chars. Nothing special .
Many, many records. Huge files. Many fields as well. File is well aligned, that is, all lines (records) have same number of fields.
When we reduce the number of fields being loaded, by quoting part of the load script statement, the processing goes on.

So, it looks pretty much with a LIMITATION of QLIK VIEW to load huge files.

Do you think you could dump the attached (linked) kit and run at your side ?

On the kit you have :

The huge CSV file, of course.
The QVW that will load it, called ARQUIVO.QVW. The interface is empty. Just need to load (test the load process).
The script , called ARQUIVO.QVS that is included via macro on the QVW.

Hope you can at least simulate the problem there at your side. If it is a technical problem I need to open a support request to QlikTech.

But before doing that I wanna test with other fellows.

Thanks in advance 4 your suPPort !

Loading a huge CSV file : problems with Memory

Fri, 03 Dec 2010 12:51:43 GMT

Without set erromode=0 the script would actually stop at the first error.

-alex

Loading a huge CSV file : problems with Memory

Fri, 03 Dec 2010 12:54:19 GMT

I have been running it for quite a few minutes and it loads very slowly because of all the transformation (IF...).

Try loading it straight into memory and then do the transforms from the resident file e.g.

tFCRES:

LOAD * FROM FCRES.CSV;

FCRES:

LOAD..IF(...)

RESIDENT tFCRES;

DROP TABLE tFCRES;

Hope this helps!

Gordon

Loading a huge CSV file : problems with Memory

Fri, 03 Dec 2010 13:30:00 GMT

Have a 32 bit laptop with QV10. Original script gives out of memory, so I did:

removed the set ERRORMODE=0, as it skips over errors I want to see
split the csv file into 100,000 lines . Put back the header row to each fragment
commented out the store statements into R:\ drive, and the load ERRORFILE as they are missing
loaded separately the 6 fragments

All files load just fine. It means you need to move to a machine with bigger memory, and possibly 64 bits.

When you removed columns, it equally reduced the memory consumption.

-----------

And rewrite those copy pasted code chunks, those are a mess ..

-Alex

Loading a huge CSV file : problems with Memory

Fri, 03 Dec 2010 13:52:00 GMT

This script is not meant to be "human readable" ..

The script is generated automatically by a Cobol Program. Thus, the Cobol program running on a Mainframe DUMPs a table onto the CSV and then creates the script that will transform the CSV into a QVD.

The IF statements are there to detect invalid content on the fields.

But let's listen to other opinions...