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,
Hi Avinah ,
Please Help me with my Script, I am trying to import Sheets from Xcel File , based on the it's Name stored in the Sheet, It is showing error When it's picking up the Sheet name in the Code (Marked as Bold).
NurturingOps:
LOAD [Ops Name]
FROM [lib://Nurturing (win-gt1ds6k1q8k_administrator)]
(ooxml, embedded labels, table is [Ops Name])
Where len([Ops Name])>0
;
countops:
Load count(distinct [Ops Name]) as "Ops Count"
resident NurturingOps;
Let vcountops=peek([Ops Count]);
drop table countops;
for i=1 to 1
//Let vOpsname=peek([Ops Name],$(i),NurturingOps);
LET vsheetName = purgeChar(purgeChar(peek([Ops Name],$(i),NurturingOps), chr(39)), chr(36));
'$(vsheetName)':
CrossTable([Opps Remark Date],[Nurturing Remark])
LOAD [Trip ID],
[42584.0]
FROM [lib://Nurturing (win-gt1ds6k1q8k_administrator)]
(ooxml, embedded labels, table is [$(vsheetName)] );
next i;
Did you check whether the variable is getting the value?? if so share the sample QVW and excel let me check
Hi Avinash,
yes it not reading the Variable Names i have checked that.
Please Suggest Me something .
My Question is does your variable is getting set with proper data ?? first check this , and if its getting the value than check the sheet name and the variable values are matching . NOTE: check for leading and trailing space ..even if their is an additional space also then it would pick the data
Why does this create a different table when I run it. Synthetic keys all over the place and I have cut and pasted your code. I also get Error: File extdata.cpp, Line 2903!
Julian,
The different tables and synthetic keys are most probably due to the fact that not all your sheets have the same amount of columns or some columns have a different header than in other files, whereas others coincide. When using this method each excel file should have the exact same layout.
As for the error, I have never seen it, might be a corrupted excel, or file with xls* extention that is not an excel file.
HP
Well i can suggest a simple way, we can just add another sheet in the Excel called Data, which has two columns,
SheetNo SheetName
1 2019-10
2 2019-11
......
8 2020-06
And then we can add the below coding,
Let serverpath='lib://Filepath/';
//Fetch all the sheetnames to a table – The table’s row count will be the limit for the loop
Sheetload:
LOAD
Sheetno,
Sheetname
FROM [$(serverpath)Filename.xlsx]
(ooxml, embedded labels, table is Data);
//Sheetname will be mapped to the table load
SheetNameMap:
Mapping
LOAD
Sheetno,
Sheetname
Resident Sheetload;
//Start of loop from 1 to no of sheets defined in the Data tab
for i=1 to NoOfrows('Sheetload')
Let sheetname=ApplyMap('SheetNameMap',i,'');
//v1 will have the sheet name i.e.,2020-10,2020-11,2020-12,etc., for each loop
Load
Column Names.....
FROM [$(serverpath)Filename.xlsx]
(ooxml, embedded labels, table is
[$(sheetname) Suffix]
//Suffix if needed here I assume I take data from 2020-04 Category and 2020-04
Load
Column Names.....
FROM [$(serverpath)Filename.xlsx]
(ooxml, embedded labels, table is
[$(sheetname) ]
// Here we extract 2020-04 alone
);
next;
Drop Table Sheetload;
Hello@avinashelite
Thanks for the script. However, did you manage to make it work on Qlik Sense (or have something similar) ?
At the moment, I redacted an idea about that https://community.qlik.com/t5/Suggest-an-Idea/Get-Excel-Sheet-Names-in-Excel-Loading-Data-Connector/...
Best regards,
Simon