topic Re: Loading Excel files with similar names in QlikView

Loading Excel files with similar names

Anonymous — Tue, 12 May 2015 09:13:03 GMT

I have several excel files all with the filename Supplier*.xlsx. I want to load all the files without using separate script for each one. This is my current script to load the first two files which is repeated for every other file.

Suppliers:

CrossTable(Supplier, Supply, 4)
LOAD *
FROM

(ooxml, embedded labels, header is 1 lines, table is [Sheet1]);

SupplierTemp:
CrossTable(Supplier, Supply, 4)
LOAD *
FROM

(ooxml, embedded labels, header is 1 lines, table is [Sheet1]);

concatenate (Suppliers)
load *
resident SupplierTemp;
drop table SupplierTemp;

Please help

Thanks

Re: Loading Excel files with similar names

datanibbler — Tue, 12 May 2015 09:19:25 GMT

Hi,

as far as I can see, your LOADs are all identical but for the filename?

=> In that case try using a FOR EACH loop with a filelist.

- Build a searchmask like >> LET v_searchmask = 'supplier*.xlsx'; <<

- Then use the DIRECTORY '[path_to_your_directory]' command to make the directory where these files are located your pwd (that is not strictly
necessary, you could do it otherwise, but it makes things a bit easier) - just remember to reset that at the end (just
type > DIRECTORY; < without anything else)

Then you go like

>>>>>>>>>>>>>>>>>>>>

For Each v_file in filelist ('$(v_searchmask)')

[your LOAD]

FROM

$(v_file)

[specifier];

That will parse your directory for any files matching that searchmask and load everyone in the same manner.

HTH

Re: Loading Excel files with similar names

Tue, 12 May 2015 09:26:18 GMT

Try this:

Suppliers:

...

FROM

(ooxml, embedded labels, header is 1 lines, table is [Sheet1]);

Re: Loading Excel files with similar names

datanibbler — Tue, 12 May 2015 09:42:55 GMT

Hi Jose,

that will also work, of course.

That just loads all files that match this filelist - the same effect, basically.

The reason I'm not doing this with the * anymore is that in case anything goes wrong because of an error in one of the files, then, if you have the *, the log will not tell you which list the error is in and you'll have to look into them all.

We once had something like this with 11 lists and it was a lot of work looking at them all and finding out in which one there was a typo ...

Otherwise, that will work fine and it is certainly easier to write than that FOR EACH loop.

Best regards,

DataNibbler