Dynamic Load of multiple qvds from a folder

[grajmca_sgp123]

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

[amit_saini]

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​

[sunny_talwar]

One way could be like this:

TableName:

LOAD *

FROM MainFolder\SubFolder\Month_*.qvd (qvd);

[Anonymous]

If all tables are with same fields and structure then try what Sunny suggested you

[Kushal_Chawda]

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;

[grajmca_sgp123]

yes my tables have same fields and structure only , but getting huge duplicate records with this solution.

[sunny_talwar]

Duplicate records meaning synthetic keys or duplicate row of data?