Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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é
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
ok, thanks.
What must i use if i have xlsx and csv files?
regards,
René
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.
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
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é
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');