Declaration of code lines

[Anonymous]

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é

[marcus_sommer]

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

[Anonymous]

ok, thanks.

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

regards,

René

[jonathandienst]

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.

[marcus_sommer]

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

[Anonymous]

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é

[Anonymous]

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