Loading Multiple Excel Sheets Dynamically along with file name and sheet name

[avinashelite]

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,

Avinashelite‌

[psankepalli]

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

[#3]

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

[avinashelite]

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

[#5]

This is terrific, works well for multiple sheets and multiple files. Very helpful. Thanks much

[#6]

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

[ChristofSchwarz]

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>>:

[ChristofSchwarz]

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

[brunobertels]

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

[avinashelite]

Hi Bruno,

I haven't tried this method in Qlik Sense , give me some time. Let me check and update you

Regards,

Avinash R

[brunobertels]

Hi

Many thanks for this

Take the time you need

It will surely help rcurlewis

Bruno