Loading different data types in qlik sense

[g23]

Hi all, 

I'm a new qlik sense user and I'm trying to write a script that loads multiple excel files and concatenate them. These files have a different structure and different data format. The problem is that I have some files .xlsx and other are .csv and I can't seem to write a script that knows how to reconize them. 

Can someone help me understanding where the issue in my script is?

I attach the script:

SUB TUTTEGAR_MINISTAT_NPC

let vSubName = 'TUTTEGAR_MINISTAT_NPC';

Trace -----------Inizio Sub Tuttegar --------;

LET vPrefix = '$(vTableNameTuttegar):
NOCONCATENATE';

for each Ext in 'xlsx', 'csv'
For Each vFileTuttegar in FileList('$(vPathInput)\TUTTEGAR'&'\*'&'$(vExt)');

$(vPrefix)

//let vL.FileExtension = SubField('$(FileTuttegar)', '.', -1) ; //filelist(*.xls) returns both xlsx and xls
//switch vL.FileExtension

Case Ext = 'xlsx'
Set delimet=(ooxml, embedded labels, table is tuttegar2);

CASE Ext = 'csv'
set delimet=(txt, codepage is 28591, embedded labels, delimiter is ';', msq);

ENDSWITCH

load
FileBaseName() AS FileName,
*
from [$(vFileTuttegar)]
$(delimet);
;

LET vPrefix = 'CONCATENATE ($(vTableNameTuttegar))';

NEXT vFileTuttegar

NEXT Ext

if NoOfRows('$(vTableNameTuttegar)')>0 then
store [$(vTableNameTuttegar)] into [$(vPathQVD1)/$(vTableNameTuttegar).qvd];
DROP TABLE [$(vTableNameTuttegar)];
else
trace DATASET $(vTableNameTuttegar) VUOTO ---------------------------;
End If

Trace --------------- Fine Sub $(vSubName) ---------------;

END SUB

Thanks 🙂

[marcus_sommer]

Your file-extension is a string and therefore the variable-call needs to be wrapped with single-quotes, maybe in this way:

switch '$(Ext)'

Case 'xlsx'
Set delimet = (ooxml, embedded labels, table is tuttegar2);

CASE 'csv'
set delimet = (txt, codepage is 28591, embedded labels, delimiter is ';', msq);

END SWITCH

An alternatively to the switch-statement would be to use and branch within an if-loop or maybe a pick(match()) expression as variable-assignment. Another possibility might be to leave the Ext-loop and just to use two filelist-loops.

- Marcus