Table Name While Loading From Excel File

[Gabbar]

Hi All, 
I have a File names:-  Product_Datefield.xlsx.   these are multiple excel in the same location.
So i use Folder/* and Loop to load the data from the location.


While loading from these Excel we get something like   :- 

(ooxml, embedded Labels, Table is [File_Name_1]);

Now what is happening is some excel files doesnt have the table name as File_Name_1   but File_Name only.

I tried removing the 'Table is'   Part but there are other tables in some excel as well which cause the problem.
I tried File_Name* but it doesnt work Because it matches the name while searching,

Is there any way to implement file_Name* condition so it picks all the tables which starts with file_name.

[Gabbar]

@sunny_talwar , @marcus_sommer , @rubenmarin .

[Kushal_Chawda · Accepted solution]

@Gabbar  Take a look at this

https://community.qlik.com/t5/Member-Articles/Dynamically-Loading-Multiple-Excel-Files/ta-p/1479021

[QFabian · Accepted solution]

HI @Gabbar , one option is make two loads, with the same fields in each load.

Here the example script :

set errormode = 0; // with this, when an error occurs, the script continue

First load excel files with the label [File_Name_1]

Data:
LOAD
fields...
FROM [lib://YOUR LIB/*.xlsx]
(ooxml, embedded Labels, Table is [File_Name_1]); /// First load with _1

Then load excel files with the label [File_Name]
LOAD
fields...
FROM [lib://YOUR LIB/*.xlsx]
(ooxml, embedded Labels, Table is [File_Name]); /// then load without _1

set errormode = 1;// with this, when an error occurs, the script stop

[marcus_sommer]

The capability to use wildcards within the file-path is a feature of the Windows file-system and isn't implemented within the Excel libraries. Within the old xls-Excel there was the possibility to specify the position-index of a sheet with something like @1 or @2 but AFAIK it's not available within the newer xlsx-Excel but if I remember correctly there is a way to grab always the first sheet (I'm not sure about the syntax - I believe it's working without specifying any sheet-name - you may need some further investigation and/or some trial and error attempts). But if the order of sheets isn't fixed determined you would also run into any errors.

Therefore take a look on the provided workarounds from @Kushal_Chawda and @QFabian whereby the first suggestion needs to enable the legacy mode and the second one may better run within a filelist('path\file*.xlsx') loop to load really all files instead of just avoiding a load-error and you may also include appropriate logging-routines to track which files exists with which sheets.  

[Gabbar]

Hi @Kushal_Chawda , Your Solution Works. But my Admin team Denied to allow That many Connection being created in QMC.

[Gabbar]

Hi @QFabian  Thanks, I didnt knew about errorCode 0, I didnt wanted to implement fearing as I was using loop to fetch, but it worked.