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) ---------------;
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.
