Excel sheet Wildcard

[vardhancse]

Hi,

PFA excel sheet.

Here is the Requirement:

1. irrespective of the sheet name which ever sheet starts with Region & Area all sheets should be reloaded.

2. Both the data needs to be concatenated

can any one please let me know option.

[Anil_Babu_Samineni]

What is your expected result?

[vardhancse]

Hi Anil,

Here is the expected output.

1. Should load the data from all the tabs

Always there will be 2 sheets

But name of the sheet is not static

Will start with Region* and another sheet will start with Area*

So my expected output is to wildcard table name, at present I am giving static name but it should be like wildcard ! !

Table1:

LOAD S.No,

    [EMP ID],

    Name

FROM

[Sheet Wild card.xlsx]

(ooxml, embedded labels, table is Region123);

concatenate

LOAD S.No,

    [EMP ID],

    Name

FROM

[Sheet Wild card.xlsx]

(ooxml, embedded labels, table is Area456);

[avinashelite]

check this

https://qlikviewcookbook.com/2008/09/loading-multiple-excel-sheets/

[tamilarasu · Accepted solution]

Hi Sasi,

Try this,

FOR EACH file in FileList('Filepath\*.xlsx');
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));

If Wildmatch(sheetname, 'Region*','Area*') Then
Table:
Load *
From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

ENDIF
NEXT i

Drop table tables;
Next file

[vardhancse]

Hi Nagaraj,

thank you for your response.

Used similar set of code for another tables, but field not found error was coming always.

Can please let me know the root cause for the above error?

[vardhancse]

Hi Nagaraj,

Just given correct path and tried to reload the script.

Only tables was getting loaded and drop. apart from that the main table is not getting loaded.

can please let me know any alternative solution for the same.

[kjhertz]

I can think of two solutions here:

1) If you know the table names in advance loop through the excel load statements and use "table is $(variable)"

2) If you dont know the names this seems like a good solution: Loading Multiple Excel Sheets Dynamically along with file name and sheet name.

[tamilarasu]

Sasi - Could you post the script that you have tried?

[vardhancse]

Thank you so much.

Only typo error

If Wildmatch(sheetName, 'Region*','Area*') Then

Earlier it was given as sheetname and so it was not reloading