topic Excel sheet Wildcard in QlikView

Excel sheet Wildcard

vardhancse — Tue, 09 May 2017 11:22:31 GMT

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.

Re: Excel sheet Wildcard

Anil_Babu_Samineni — Tue, 09 May 2017 11:24:43 GMT

What is your expected result?

Re: Excel sheet Wildcard

vardhancse — Tue, 09 May 2017 11:29:40 GMT

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

Re: Excel sheet Wildcard

avinashelite — Tue, 09 May 2017 11:36:49 GMT

check this

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

Re: Excel sheet Wildcard

tamilarasu — Tue, 09 May 2017 11:44:09 GMT

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

Re: Excel sheet Wildcard

vardhancse — Tue, 09 May 2017 11:51:24 GMT

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?

Re: Excel sheet Wildcard

vardhancse — Tue, 09 May 2017 11:57:19 GMT

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.

Re: Excel sheet Wildcard

kjhertz — Tue, 09 May 2017 11:57:36 GMT

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.

Re: Excel sheet Wildcard

tamilarasu — Tue, 09 May 2017 12:00:01 GMT

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

Re: Excel sheet Wildcard

vardhancse — Tue, 09 May 2017 12:05:57 GMT

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

Re: Excel sheet Wildcard

tamilarasu — Tue, 09 May 2017 13:09:34 GMT

My bad. I haven't tested the code. Thanks and Have a good day!!

Re: Excel sheet Wildcard

vardhancse — Tue, 09 May 2017 13:53:08 GMT

Hi Nagaraj,

similarly can please let me know how to load data from multiple excel sheet with different file names.

For e.g:

1. Sample.xlsx

2. Sample 1.xlsx

3. Sample 3.xslx

Now we need to load all the sheets data from all the 3 excel sheets and concatenate into one single sheet

Re: Excel sheet Wildcard

tamilarasu — Tue, 09 May 2017 13:57:49 GMT

Hi Sasi,

Have you tried by changing the file path (Highlighted in red) in above script? I believe this should work.

FOR EACH file in FileList('Filepath\*.xlsx');

Re: Excel sheet Wildcard

vardhancse — Tue, 09 May 2017 15:11:29 GMT

Hi Nagaraj,

Yes changed the path and given * as well in the file name.

But issue is that facing "_xlnm._FilterDatabase" issue.

Loading duplicate sheets by appending _xlnm._FilterDatabase in the file name.

Re: Excel sheet Wildcard

tamilarasu — Tue, 09 May 2017 15:19:24 GMT

Sasi - Do you apply any table formats in your excel file? I think that might cause this issue. You can exclude those sheet names by adding "IF" condition. If you still facing the issue, could you please post a snapshot that shows your problem.