Loading Excel files with similar names

[Anonymous]

Hi

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

[datanibbler]

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];

NEXT

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

HTH

[#3 · Accepted solution]

Try this:

Suppliers:

...

FROM

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

[datanibbler]

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