Loading data coming from several files with variable filename

[#1]

Hello, all

I try to load data stored in multiple CSV files (every file respect the same format but has specific name), so i've decided to make a loop FOR with the LOAD ... FROM  instruction, using substitution variables for Path (directories are changing in further steps in the code) and file names

Here is my code :

SET vDataSourceDir = ' Y:\COLT\Aval\QlikView\protoBDD\jeu de test';

For Each vFile in filelist (vDataSourceDir & '\*.csv')  

CrossTable(Pays_1, Données_1, 3)

LOAD Name as Palier_1,

         Key as Variable_1,

         Unit,

         ADA,

         BEL,

         CHE,

         CZE,

         ESP,

         FRA,

         FSZ,

         GBR,

         HUN,

         IRL,

         ITN,

         ITS,

         MASSE,

         NLD,

         POL,

         PRT,

         SVK            

    FROM

    $(vDataSourceDir)\$(vFileName)

Problem is that I have the following error code :

Bad Zip File

SortieBrutReporting_1:

    CrossTable(Pays_1, Données_1, 3)

    LOAD Name as Palier_1,

         Key as Variable_1,

         Unit,

         ADA,

         BEL,

         CHE,

         CZE,

         ESP,

         FRA,

         FSZ,

         GBR,

         HUN,

         IRL,

         ITN,

         ITS,

         MASSE,

         NLD,

         POL,

         PRT,

         SVK            

    FROM

    Y:\COLT\Aval\QlikView\protoBDD\jeu de test\CH13_Realiste_DP2013.csv

    (ooxml, embedded labels, header is 2 lines, table is GlobalReporting_2, filters(

    Remove(Row, RowCnd(Interval, Pos(Top, 13), Pos(Bottom, 1), Select(13, 0)))

    ))

What is weird, is that the error appears when I use substitution variables for both path and file name. When i substitute only path, its working well.

Any suggestions , ideas ??

Thanks for your help

[giakoum]

FROM

    $(vDataSourceDir)\$(vFileName)

or

FROM

    $(vDataSourceDir)\$(vFile) ??

where is $(vFileName) coming from?

[nikhilgandhi715 · Accepted solution]

Hi Cedric,

Please try using below code.

Set vConcatenate = ;
Set vFileName = ;

FileList:
LOAD

'' AS SourceFile
AUTOGENERATE0;

Main:
Load

'' AS PERIOD

AUTOGENERATE 0;

sub

ScanFolder(Root)
for each FileExtension in 'csv'
for each FoundFile in filelist( Root & '\*.' & FileExtension)
FileList:
LOAD '$(FoundFile)' as SourceFile

AUTOGENERATE 1;
Set vFileName = $(FoundFile);
Set vConcatenate = Concatenate;

Main:
LOAD left(Right('$(FoundFile)',10),6) AS PERIOD

FROM[$(vFileName)](csv);
next

FoundFile

next

FileExtension

for each SubDirectory in dirlist( Root & '\*' )
call ScanFolder(SubDirectory)
next

SubDirectory
end sub

Call ScanFolder('<YOUR DIRECTORY PATH>');

Regards,

Nikhil.

[#4]

Sorry loannis I forgot this:

Let vFileName = SubField(vFile, '\', -1);