Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld 2023, a live, in-person thrill ride. Save $300 before February 6: REGISTER NOW!
cancel
Showing results for 
Search instead for 
Did you mean: 
Prabhu1204
Creator
Creator

Dynamic File Load and store it as qvd

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

 

 

-Prabhu
2 Solutions

Accepted Solutions
Kushal_Chawda

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;

 

View solution in original post

Kushal_Chawda

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

 

View solution in original post

5 Replies
Kushal_Chawda

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
Creator
Creator
Author

Thanks Kush. Will try it out and reply

 

-Prabhu
Prabhu1204
Creator
Creator
Author

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_0-1599194659073.png

 

-Prabhu
Prabhu1204
Creator
Creator
Author

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 ?

 

-Prabhu
Kushal_Chawda

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