Dynamically Loading Multiple Excel Files

[g23]

Hi all,

I'm trying to write a script that loads me all fields from some excels file but when I call the SUB PERGAR_MINISTAT_NPC in the script but my script is not calling correctly the SUB.

I attach the script I wrote:

SUB PERGAR_MINISTAT_NPC;

let vSubName = 'PERGAR_MINISTAT_NPC';

//set v_Concatenate ='';
For Each vFileName in FileList ($(vPathInput\PERGAR\pergar2_*$(vExt));

Trace -----------Inizio Sub Ministat NPC --------;
//$(v_Concatenate)

[$(vTableNamePergar)]:
load * from [$(vPathInput)\PERGAR\pergar2_*$(vExt)]
//'$(vFileName)'
(ooxml, embedded labels);

concatenate ($(vTableNamePergar));
//set v_Concatenate ='Concatenate';

NEXT vFileName

Trace --------------- Fine Caricamento $(vFileName) ---------------;

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

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

END SUB

can someone help me understand who to make my script work??

Thanks

[bramkn]

your sub: "SUB PERGAR_MINISTAT_NPC" can do without";"
also you need to call the sub where needed.
so: CALL PERGAR_MINISTAT_NPC

[g23]

I deleted the ; from the SUB and I was already calling the SUB but I still have the same error.

Any other advice?

[bramkn]

whats the error? and it seems like you are trying to have some inputs like "vPathInput" are these just variables or actual inputs in the call statement?

[g23]

This is the error:

and the vpathInput ecc.. are the variables that I created in another script.

I'll attach the script of the variable:

SUB VARIABILI

let vSubName = 'VARIABILI';

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

//Configurazione path
let vRoot = 'lib://SelfBI/PC\MINISTAT_NPC';
let vPathInput = '$(vRoot)\INPUT';
let vPathQVD1 = '$(vRoot)\QVD\QVD 1';
let vPathQVD2 = '$(vRoot)\QVD\QVD 2';
let vPathUtils = '$(vRoot)\UTILS';

//Files name
let vFileName = 'pergar2_*.$(vExt)';

// Formato file di input
let vExt = '.xlsx';
let vCsv = '.csv';

// Nome Tabella (file .qvd di output)
let vTableNamePergar = 'PERGAR';
let vTableNamePerprod = 'PERPROD';
let vTableNameTuttegar = 'TUTTEGAR';

Trace vPathInputPergar: $(vPathInputPergar);
Trace vPathInputPerprod: $(vPathInputPerprod);
Trace vPathInputTuttegar: $(vPathInputTuttegar);
Trace vPathQVD1: $(vPathQVD1);
Trace vPathQVD2: $(vPathQVD2);
Trace vPathUtils: $(vPathUtils);

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

[bramkn]

I assume you have the sub before the call?

[g23]

Yess the call script is at the end of the sub. 

[JordyWegman]

Hi,

There is probably something wrong in your sequence. Try to comment out ALL the script and do this:

SUB Test

Trace >>>> This is a test!;

END SUB

Put this at the end of your script:

CALL Test;

 Now try to run your script and continue from this.

Jordy

Climber