Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
This document helps you in loading multiple excels and excel sheets with the name of the sheet and data.
//to read each file from the specified folder
FOR EACH file in FileList('filepath\*.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)]);
NEXT i
Next
Hope this helps !!!
please find the attachment for the eg: qvw and test fiels
Regards,
Hello Avinash,
Sorry for the delay in response, Actually problem was i donot have permissions to ODBC drivers on that server. I tried to access the same code where I have access rights. it worked perfectly. Thanks for your prompt response
Hi Avinashelite,
Thanks for the post. I am in need of some help please - getting an error "Error: File extdata.cpp, Line 2903"
Can you please advise?
Many thanks
Jaq
Hi jjsteyn2015
As per my knowledge
"If the name of the tab is different in any one of the files in loop, this error pops up." Please check once
This is terrific, works well for multiple sheets and multiple files. Very helpful. Thanks much
Hi, I have a batch of files in CSV format loaded in local directory with different language options.
1. How can I do above load job as you did for excel for CSV format files
2. How can i put restriction on language for example i only need files with english.
Please find the snap shot for overlook on the folder
I suppose you needed a DROP TABLE tables; between the two NEXT commands at the end of your script ...
Von meinem iPad gesendet
Am 17.05.2016 um 21:05 schrieb Brian Garside <qcwebmaster@qlikview.com<mailto:qcwebmaster@qlikview.com>>:
Hi Brian
If you like to export after each file, you’d have to add
STORE GL INTO (qvd);
DROP TABLE GL;
after line “NEXT I” …
That will loop through the sheets of one Excel, automatically concatenate those before creating one .qvd per excel. Is this what you need?
Bye
Hi Avinash
Does this work on Qlik Sense without legacy mode ?
I asked you this because of this post https://community.qlik.com/message/1041982#1041982
can you have a look please ?
Thanks in advance
Bruno
Hi Bruno,
I haven't tried this method in Qlik Sense , give me some time. Let me check and update you
Regards,
Avinash R