topic Re: Challenge. Loading a bunch of excel files in an efficient way in QlikView

Challenge. Loading a bunch of excel files in an efficient way

marcel_olmo — Fri, 19 Oct 2018 18:15:50 GMT

Hi guys,

I have a situation like the following :

There's an automated process which creates excel files from first record to last record in a range of 500k records (generating new records every day) like this :
- Excel_1_500000
- Excel_500001_1000000
- Excel_1000001_150000
- ...

If I do this :

MyJoinedTable :

LOAD

fieldA,

fieldB

FROM

[excel_*.xlsx]

(ooxml, embedded labels, table is Sheet1);

I get a new table which loads all the records available in the excels, and it's fine.

But right now my load script takes more than an hour, and it keeps increasing every day.

What would you do to improve it?

Regards, Marcel.

Re: Challenge. Loading a bunch of excel files in an efficient way

dplr-rn — Fri, 19 Oct 2018 19:23:09 GMT

See attached a sample app and data used

Basically for the first load (based on a flag) load the file names and data into a qvds

Subsequent loads

- get list of files names

- load old list of file names from qvd

- create another table with latest filenames only

- load data from qvd

- loop through new file names and load which gets autoconcatenated

- drop unnecessary tables (havent done this in the app to prove it works)

- save qvds

hope it helps

Re: Challenge. Loading a bunch of excel files in an efficient way

dplr-rn — Tue, 23 Oct 2018 16:44:56 GMT

did it work?

Re: Challenge. Loading a bunch of excel files in an efficient way

CathyRDuvall — Tue, 23 Oct 2018 19:31:33 GMT

Can you get the data from the source that creates the excel files? If so, you get set up an incremental load from the source.

Cathy

Re: Challenge. Loading a bunch of excel files in an efficient way

marcel_olmo — Tue, 23 Oct 2018 20:53:45 GMT

Thank you Dilip, it was a very complete advice.

Finally what I'm doing is your for next loop and storing each table in a single QVD, it reduced the reload time from 1 hour to 10 minutes.

After the first reload, I check the filesize() of the excel to decide if I have to store it again as QVD or not.

Regards, Marcel.

Re: Challenge. Loading a bunch of excel files in an efficient way

dplr-rn — Tue, 23 Oct 2018 20:56:04 GMT

Good to hear

Thank You

Dilip Ranjith

Sent via mobile