Dynamic File Load and store it as qvd

[Prabhu1204]

Hello All, I have a requirement like, There will be a 5 to 6 ( department) will put the file in a folder having the same naming convention and common values. Like file1, file2 etc.. I need to load all the files from the folder and do a concatenation and store that into a qvd. whenver the new files arrives the data has be get append in the qvd.How to achieve this. do anyone have script. i searched the community and took the below script. But i am not sure that does the the code mean "FOR i = 0 to NoOfRows('tables')-1" and where the concatenation and stroring qvd happens.

Could anyone please help

FOR EACH file in FileList('F:\AnT\Next \*.xlsx');

//In order to get the file information from SQLtables command making use of the ODBC connection format
ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Table:
Load * ,
FileBaseName()as FIle,
FileDir() as Dir,
FileName() as File_Name,
'$(sheetName)' as Sheet_name
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

[Kushal_Chawda · Accepted solution]

try below. Change path to your actual path

for Each vFile in FileList('C:\*.xlsx')


ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

Temp:
LOAD *;
SQLtables;
DISCONNECT;

Data:
LOAD * INLINE [
junk ];

FOR i = 0 TO NOOFROWS('Temp')-1

LET vSheetName = PURGECHAR(PURGECHAR(PEEK('TABLE_NAME', i, 'Temp'), CHR(39)), CHR(36));

CONCATENATE(Data)
LOAD *,
FILEBASENAME() AS FileName,
'$(vSheetName)' AS Sheet_name
FROM $(vFile)(ooxml, embedded labels, table is [$(vSheetName)]);

NEXT i


DROP TABLE Temp;

DROP FIELD junk;

NEXT $(vFile)

Set ErrorMode=0;
Drop Field A;     // When there is blank sheet in excel file, field A is created
Set ErrorMode=1;

[Prabhu1204]

Thanks Kush. Will try it out and reply

[Prabhu1204]

Hello @Kushal_Chawda 

I have tried the above script, it fails due to the below error. Wat would be the reason ?

All my excel have only one sheet and all excel columns and  sheet name would be the same. Could you please help and let me know where to write the store commnet, It fails on adding next to Next vfile.

[Prabhu1204]

Hello @Kushal_Chawda 

I rectified the error.Thanks a lot. Only thing i need to know is 

1) Where to use the store command ?

2) The user will delete the excels from the folder and put the new excels in the folder, In that way also will it work fine and the qvd hold the old data too ?

[Kushal_Chawda · Accepted solution]

@Prabhu1204  You can use store command at the end. Yes,if user will add or delete file then it will automatically look at the existing file only and create QVDs out of those.