Loading Excel Files With Prefixes Into Qlik

[LK13]

Hello,

I have several excel files which needs to be loaded. Each of the files starts with one of the following prefixes: AMS, BFS, MIL. ie: AMS_New23, BFS_01/01/25 etc. Sometimes one of the files will not be there and i need to load whatever from this files exists into shared folder.

I tried:

SET vPrefixes = 'AMS, BFS, MIL';

PrefixesTable:
LOAD SubField('$(vPrefixes)', ',') as Prefix
AUTOGENERATE 1
WHILE IterNo() <= SubStringCount('$(vPrefixes)', ',') + 1;

for each prefix in FieldValueList('Prefix')
for each file in FileList('lib://Geo_EMEA/A1 Lazar Test/' & '$(prefix)*.xlsx')
LOAD *
FROM [$(file)] (ooxml, embedded labels, table is Sheet1);
next file
next prefix

But apparently FieldValueList does not work, since i am able to load only file with first defined Prefix.

Do you have any idea on how to do this ?

Thank you !

[SRA · Accepted solution]

Hi,

This Script should work:

SET vPrefixes = 'AMS, BFS, MIL';

PrefixesTable:
LOAD Trim(SubField('$(vPrefixes)', ',')) as Prefix
AUTOGENERATE 1;

for each prefix in FieldValueList('Prefix')
for each file in FileList('lib://Geo_EMEA/A1 Lazar Test/$(prefix)*.xlsx')
RESULT:
LOAD *
FROM [$(file)] (ooxml, embedded labels, table is Sheet1);
next file
next prefix

Trim will remove the unsusefull spaces you will get with the subfield.

Regards,

[Qrishna]

SET vFileDir = '';

LET vFilePrefixes = Chr(39) & 'AMS' & Chr(39) & ',' & Chr(39) & 'BFS' & Chr(39) & ',' & Chr(39) & 'MIL' & Chr(39);

//if all files have same no of fields/same fields else use concat to append all files data
FOR EACH vPrefix IN $(vFilePrefixes)

//Import_$(vPrefix)_File: - use this if you wan to load each file as different table
Import:
LOAD *
FROM
[$(vFileDir)$(vPrefix)_*.xlsx]
(ooxml, embedded labels, table is Sheet1);

NEXT

[LK13]

Hi SRA,

This script you provided first works perfect: (where Trim actually did great job)

SET vPrefixes = 'AMS, BFS, MIL';

PrefixesTable:
LOAD Trim(SubField('$(vPrefixes)', ',')) as Prefix
AUTOGENERATE 1;

for each prefix in FieldValueList('Prefix')
for each file in FileList('lib://Geo_EMEA/A1 Lazar Test/$(prefix)*.xlsx')
LOAD *
FROM [$(file)] (ooxml, embedded labels, table is Sheet1);
next file
next prefix

// *****************************

But this one does not work:

Error: There were no fields to load in the given source.

[SRA]

Hi, you are fully right! I change again to the first solution provided. Better if there is no files for one of the prefix. thanks