Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

homersimpson
Contributor II

Declaration of code lines

Hi,

i have some questions for the red lines in this code:

Set vConcatenate = ;

sub ScanFolder(Root)

          for each FileExtension in 'csv'

                    for each FoundFile in filelist( Root & '\*.' & FileExtension)

                              FileList:

                              $(vConcatenate)

                              LOAD *, '$(FoundFile)' as SourceFile

                              FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

                              Set vConcatenate = Concatenate;

                    next FoundFile

          next FileExtension

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

                    call ScanFolder(SubDirectory)

          next SubDirectory

end sub

Call ScanFolder('C:\Users\hic\Documents\2012\Work\QV Apps\DoDir') ;

loop through to load all files from a folder and its subfolders?#

Why use this code LOAD *, '$(FoundFile)' as SourceFile ?

Normally it looks like this LOAD * from data1.csv;

In this line FROM [$(FoundFile)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

What does txt, codepage, msq, header mean?

regards,

René

6 Replies

Re: Declaration of code lines

LOAD *, '$(FoundFile)' as SourceFile adds simply the source to the table. You could also use a filefunction like filebasename() for it - in each load which goes on a file.

(txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines) is the definition of the fileformat - this meant you have on this point various options to adjust it for your needs.

- Marcus

homersimpson
Contributor II

Re: Declaration of code lines

ok, thanks.

What must i use if i have xlsx and csv files?

regards,

René

MVP
MVP

Re: Declaration of code lines

Import the file using the wizard. It will create the file format specifier for you. In your case, just choose one file to import, then copy and paste the specifier to the load statement in the loop. Then you can delete the load statement you created for the sample file.

Re: Declaration of code lines

The easiest way to get the right format-statement is to load one file of them normally - you will get a preview to check if the fields are correct interpreted - and then copy this format-statement into your loop-routine.

If you didn't used different extension-statements within the filelist-loop filelist( Root & '\*.' & FileExtension) then you will need an appropriate if-loop on fileextension() to adress the right fileformat.

- Marcus

homersimpson
Contributor II

Re: Declaration of code lines

I did it like you describe. I get always this error:

Error: File extdata.cpp, Line 2279: FileList: Concatenate LOAD *,...


But only if i load a xlsx file. If i load a csv file it works.


For csv i use this code:

FROM [$(Datei)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

For xlsx this code:

FROM [$(Datei)] (ooxml, embedded labels, table is test));


regards,

René

homersimpson
Contributor II

Re: Declaration of code lines

This script runs:

Set vConcatenate = ;

sub ScanFolder(Root)

for each FileExtension in 'csv','xlsx'

  if FileExtension = 'csv' then

   

  for each Datei in filelist( Root & '\*.' & FileExtension)

  

  FileList:

  $(vConcatenate)

  

  LOAD *, '$(Datei)' as SourceFile

  FROM [$(Datei)] (txt, codepage is 1252, embedded labels, delimiter is ',', msq, header is 2 lines);

  

  Set vConcatenate = Concatenate;

  next Datei

  

  elseif FileExtension = 'xlsx' then

   

  for each Datei in filelist( Root & '\*.' & FileExtension)

   

  FileList:

  $(vConcatenate)

  LOAD *, '$(Datei)' as SourceFile

  FROM [$(Datei)] (ooxml, embedded labels);

   

  Set vConcatenate = Concatenate;

  next Datei

   

  end if

next FileExtension

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

  call ScanFolder(SubDirectory)

  next SubDirectory

end sub

Call ScanFolder('C:\work');