Multiple fileExtension in FROM statement

[JuMo_dev]

Hi, 

I need help to load CSV and XLS files in one bloc. The files contains same fields but can be registered as CSV or XLS.  I want to avoid to 2 blocs of LOAD like blow. Someone can help me please? Thanks!

//CSV files:

[Acknow]:
LOAD
[Domain],
[Dossier Reference],
[Submitted by],
[Submitted at]
FROM [lib://Test/Raw data/Queries_Repair*.csv]
(txt, unicode, embedded labels, delimiter is '\t', msq);

//XLS files:

concatenate

[Acknow]:
LOAD
[Domain],
[Dossier Reference],
[Submitted by],
[Submitted at]
FROM [lib://Test/Raw data/Queries_Repair*.xls*]
(ooxml, embedded labels);

[Chanty4u]

below is having one example similar to this

https://community.qlik.com/t5/New-to-Qlik-Sense/Load-multiple-files-in-QlikSense/td-p/1260173

[JuMo_dev]

Thank you. I already saw this post but I didn't find how it can help me.  I am annoyed with the format-spec in FROM statement because there are different from CSV to XLS format. 

[marcus_sommer]

Within the loop you may query the extension to add the appropriate format to a variable like:

for each file in filelist(path)
   let format = if(fileextension('$(file')) = 'csv', '(txt, ...)', '(ooxml, ...)');
   t: load * from [$(file)] $(format);
next

- Marcus

[JuMo_dev]

Hello Marcus, thank you for your help. I am trying your solution, seems good but I have one error here: let format = if(fileextension('$(file')) = 'csv', '(txt, unicode, embedded labels, delimiter is '\t', msq)', '(ooxml, ...)'). Seems I can't write an apostrophe in another.  I tried with a variable but same issue :(. 

[JuMo_dev]

Hi Marcus, I resolve my quote issue and I wrote the script below. But I don't know why,  I have an error, seems like Qlik doesn't detect correctly the FileExtension. Any idea why? I don't understand.  

****error***

The error occurred here:

[Acknow]: LOAD [Domain], [Dossier Reference], [Submitted at], FileExtension() FROM [lib://Test/Raw data/Queries_Engineering_Acknow_2019-01.03.csv] (ooxml, embedded labels)

******script******

Set vPath = 'lib://Test/Raw data/';
SET vFormatSpeccsv = "(txt, unicode, embedded labels, delimiter is '\t', msq)";
SET vFormatSpecxls = '(ooxml, embedded labels)';

For each Ext in 'csv', 'xls*'
For each File in FileList('$(vPath)'&'Queries*.'&'$(Ext)')
let Format = if(FileExtension('$(File)') = 'csv', '$(vFormatSpeccsv)', '$(vFormatSpecxls)');

[Acknow]:
LOAD
[Domain],
[Dossier Reference],
[Submitted at],
FileExtension()
FROM [$(File)] $(Format);

Next File;
Next Ext;

[marcus_sommer]

The file-format should be also valid with if you remove the single-quotes from the delimiter, like:

... '(txt, unicode, embedded labels, delimiter is \t, msq)' ...

If not respectively by other occasions you could double them, like:

... '(txt, unicode, embedded labels, delimiter is ''\t'', msq)' ...

- Marcus

[marcus_sommer · Accepted solution]

At the first glance there seems no mistake within your script which should cause this error but if I look within the help it seems that fileextension() worked unlike the other file-functions only within a load and not as a sperate statement. Therefore try the following change:

Set vPath = 'lib://Test/Raw data/';
SET vFormatSpeccsv = "(txt, unicode, embedded labels, delimiter is '\t', msq)";
SET vFormatSpecxls = '(ooxml, embedded labels)';

For each File in FileList('$(vPath)'&'Queries*.*')
   let Format = if(subfield('$(File)', '.', -1) = 'csv', '$(vFormatSpeccsv)', '$(vFormatSpecxls)');

   [Acknow]: LOAD [Domain], [Dossier Reference],[Submitted at] FROM [$(File)] $(Format);

Next

A loop through the various extensions is possible but seems in your case not necessary.

- Marcus

[JuMo_dev]

Thank you, it is working well! 🙂

Many thanks for your help.