Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
Need help with this scenario from scripting gurus?
I have a situation where I have to load and concatenate all qvds dynamically.
This is my folder structure
MainFolder
SubFolderr
Month_31-Aug-2015.qvd,
Month_30-Sep-2015.qvd,
Month_31-OCT-2015.qvd
Month_30-Nov-2015.qvd,
Month_31-DEC-2015.qvd
How to load all the files dynamically if i give only "MainFolder" Name.
Thanks in advance
Regards
Rajesh,
Code to Access all files in directory (subdirectories)
SUB DoDir (Root)
FOR each File in filelist(Root& '\*.xls')
Tab1:
LOAD <<Field Names>>
FROM
[$(File)]
(biff, embedded labels, table is <<Table name>>$);
NEXT File
FOR each Dir in dirlist (Root&'\*')
CALL DoDir(Dir)
NEXT Dir
END SUB
CALL DoDir('Your Directory');
Code to Access All sheets in a Excel work book
Directory;
for a=1 to 3
LOAD employee
FROM
Looping\constructs1.xlsx // this is the excel sheet name
(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a
Next
Code to Access all sheets in a excel work book and convert to QVD
For a=1 to 4
Directory1:
LOAD employee
FROM
Looping\constructs1.xlsx
(ooxml, embedded labels, table is Sheet$(a));
//STORE Directory INTO C:\Users\asaini\Desktop\Looping\Directory$(a).QVD;
Next
STORE Directory1 INTO C:\Users\amits\Desktop\Looping\Directory.QVD;
Drop Table Directory1;
Directory;
LOAD employee
FROM
Looping\Directory.QVD
(qvd);
Thanks,
AS
One way could be like this:
TableName:
LOAD *
FROM MainFolder\SubFolder\Month_*.qvd (qvd);
If all tables are with same fields and structure then try what Sunny suggested you
Sometimes it may happen that all files don't have same number of fields so it is better to use for loop like below
//Read all file name
Files:
First 1 LOAD FileBaseName() as FileName
FROM
[FolderPath\*.qvd]
(qvd);
Table:
LOAD * Inline [
Junk ];
// loop to load all files
for i=1 to FieldValueCount('FileName');
let vFileName = FieldValue('FileName',$(i));
Concatenate(Table)
LOAD *,
FileBaseName() as FileName
FROM
[FolderPath\$(vFileName).qvd]
(qvd);
next
DROP Table Files;
DROP Field Junk;
yes my tables have same fields and structure only , but getting huge duplicate records with this solution.
Duplicate records meaning synthetic keys or duplicate row of data?