topic Re: Loading data coming from several files with variable filename in QlikView

Loading data coming from several files with variable filename

Mon, 15 Sep 2014 14:55:02 GMT

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

Re: Loading data coming from several files with variable filename

giakoum — Mon, 15 Sep 2014 15:01:21 GMT

FROM

$(vDataSourceDir)\$(vFileName)

FROM

$(vDataSourceDir)\$(vFile) ??

where is $(vFileName) coming from?

Re: Loading data coming from several files with variable filename

nikhilgandhi715 — Mon, 15 Sep 2014 15:13:50 GMT

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.

Re: Loading data coming from several files with variable filename

Tue, 16 Sep 2014 07:39:47 GMT

Sorry loannis I forgot this:

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