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‌

[#2]

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;

[avinashelite]

Did you check whether the variable is getting the value?? if so share the sample QVW and excel let me check

[#4]

Hi Avinash,

yes it not reading the Variable Names i have checked that.

Please Suggest Me something .

[avinashelite]

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 

[Chanty4u]

Hi avi,

Can you look into this?

RE: Keepchar

[juliangibson]

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!

[happydays1967]

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

[Jayasharma89]

Hi I want i am trying to use use same with Qliksense. but its giving an error of data handling. Can anyone explain. Using below code ODBC CONNECT32 TO [Excel Files;DBQ=$(VExcelPath)Estimates.xls]; XlsTables: SQLTables; DISCONNECT; FOR i = 0 to NoOfRows('XlsTables')-1 LET sheetName = purgeChar(peek('TABLE_NAME', i, 'XlsTables'), chr(39)); Estimate: CrossTable(Month, Estimates, 4) LOAD Text(Branch) as [Division Code], text([Main Account]) as [Main Account], text([Sub Account]) as [Sub Account], text([Product Code]) as [Department Code], JANUARY, FEBRUARY, MARCH, APRIL, MAY, JUNE, JULY, AUGUST, SEPTEMBER, OCTOBER, NOVEMBER, DECEMBER FROM [lib://QVD (infotech_1001095)/BI_QlikView_Premia_NICE/1.Excel/Estimates.xls] (biff, embedded labels, table is [$(sheetName)]); NEXT i DROP TABLE XlsTables;

[rajkumar2389]

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;

[simonaubert]

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