topic Re: Dynamic File Load and store it as qvd in QlikView

Dynamic File Load and store it as qvd

Prabhu1204 — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Dynamic File Load and store it as qvd

Kushal_Chawda — Thu, 03 Sep 2020 12:20:01 GMT

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;

Re: Dynamic File Load and store it as qvd

Prabhu1204 — Thu, 03 Sep 2020 13:15:35 GMT

Thanks Kush. Will try it out and reply

Re: Dynamic File Load and store it as qvd

Prabhu1204 — Fri, 04 Sep 2020 04:45:04 GMT

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.

Re: Dynamic File Load and store it as qvd

Prabhu1204 — Fri, 04 Sep 2020 04:58:19 GMT

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 ?

Re: Dynamic File Load and store it as qvd

Kushal_Chawda — Fri, 04 Sep 2020 09:51:07 GMT

@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.