topic Re: Loading Multiple Excel Sheets Dynamically along with the File Name and Sheet Name in QlikView

Loading Multiple Excel Sheets Dynamically along with the File Name and Sheet Name

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

Hi all,

This is my first time asking question for Qlik so please bear with me. Basically, I want to load data from several .xlsx files in a folder along with sheet name and file name. I tried using the method suggested here: Loading Multiple Excel Sheets Dynamically along with file name and sheet name but it won't work for me. The actual code:

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

I don't have any connection to SQL or use SQL for that matter and I'm confused as to what ODBC connect line does. I already changed the filepath. Thanks in advance!

Re: Loading Multiple Excel Sheets Dynamically along with the File Name and Sheet Name

tresB — Tue, 06 Sep 2016 04:51:34 GMT

You have to create an ODBC to connect to excels. Check : https://community.qlik.com/message/716434#716473 here to know how.

Re: Loading Multiple Excel Sheets Dynamically along with the File Name and Sheet Name

amit_saini — Tue, 06 Sep 2016 05:00:24 GMT

Hi,

This might help u:

Code to Access all files in directory (subdirectories)

SUB DoDir (Root)

FOR each File in filelist(Root& '\*.xls')

Tab1:

LOAD <<Field Names>>

FROM

[$(File)]

(biff, embedded labels, table is <<Table name>>$);

NEXT File

FOR each Dir in dirlist (Root&'\*')

CALL DoDir(Dir)

NEXT Dir

END SUB

CALL DoDir('Your Directory');

Code to Access All sheets in a Excel work book

Directory;

for a=1 to 3

LOAD employee

FROM

Looping\constructs1.xlsx // this is the excel sheet name

(ooxml, embedded labels, table is Sheet$(a));// $ sign allows to access the value of a

Next

Code to Access all sheets in a excel work book and convert to QVD

For a=1 to 4

Directory1:

LOAD employee

FROM

Looping\constructs1.xlsx

(ooxml, embedded labels, table is Sheet$(a));

Next

STORE Directory1 INTO C:\Users\amits\Desktop\Looping\Directory.QVD;

Drop Table Directory1;

Directory;

LOAD employee

FROM

Looping\Directory.QVD

(qvd);

Thanks,

Re: Loading Multiple Excel Sheets Dynamically along with the File Name and Sheet Name

jonathandienst — Tue, 06 Sep 2016 05:05:00 GMT

You need an ODBC connection to Excel to get the sheet names (the SqlTables operation). If you know the sheet names, then the ODBC connection is not required.

Re: Loading Multiple Excel Sheets Dynamically along with the File Name and Sheet Name

susovan — Tue, 06 Sep 2016 06:05:40 GMT

You can also try this below process to load the multiple excel from any particular folder.

Table:

LOAD

FROM

[..\FolderName\*.xlsx]

(ooxml, embedded labels, table is SheetName);

STORE Table into $(VPath)Table1.qvd;