Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
vardhancse
Specialist III
Specialist III

Excel sheet Wildcard

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.

1 Solution

Accepted Solutions
tamilarasu
Champion
Champion

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

View solution in original post

14 Replies
Anil_Babu_Samineni

What is your expected result?

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
vardhancse
Specialist III
Specialist III
Author

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);

tamilarasu
Champion
Champion

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
Specialist III
Specialist III
Author

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
Specialist III
Specialist III
Author

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
Partner - Creator
Partner - Creator

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
Champion
Champion

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

vardhancse
Specialist III
Specialist III
Author

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