topic Re: How to automatically upload an excel file's SHEETS? in QlikView

How to automatically upload an excel file's SHEETS?

Mon, 16 Mar 2015 08:49:30 GMT

Hi Dear all,

I have an excel file which is fine and i can read it. However I want to be able to make it read the other sheets of the same file. Altough I have found some similar disscussion in here. Mine is slightly different. My some of the examples sheets' names are below.

BP_2015_Yılmaztekin

BP_2015_Yılmaztekin tekirdağ

BP_2015_Mercan

They all start with BP_2015_ and then it changes to the names.

Is there a way how to make it read automatically?

Re: How to automatically upload an excel file's SHEETS?

Mon, 16 Mar 2015 09:06:51 GMT

Hi Kezban,

have a look at this thread

http://community.qlik.com/thread/154050

you can use the same technique to connect to read through the sheet names and then use a wildmatch on BP_2015 instead of match to get the sheets you require

hope that helps

Joe

Re: How to automatically upload an excel file's SHEETS?

Gysbert_Wassenaar — Mon, 16 Mar 2015 09:16:10 GMT

You can find an example here: Loading Multiple Excel Files and Multiple Excel sheets

You may want to include a check for the sheetname to skip any that don't start with BP_2015:

SUB LoadAllExcelSheets(vFile)

ODBC CONNECT TO [Excel Files;DBQ=$(vFile)];

tables:
SQLtables;
DISCONNECT;

Let vSheetCount = NoOfRows('tables');

FOR i = 0 to $(vSheetCount) -1
   
  LET vSheetName = replace(purgeChar(purgeChar(peek('TABLE_NAME', $(i), 'tables'), chr(39)), chr(36)),'#','.');

    IF wildmatch('$(vSheetName)', 'BP_2105*') THEN
   
        [$(vSheetName)]:
        LOAD *, '$(vSheetName)' as Sheet, '$(vFile)' as FileName
        FROM [$(vFile)] (ooxml, embedded labels, table is [$(vSheetName)]);

    END IF

NEXT i

DROP TABLE tables;

END SUB

Re: How to automatically upload an excel file's SHEETS?

Mon, 16 Mar 2015 10:11:29 GMT

Hi Gysbert,

I am trying to figure out the code. Can I use it as it is above exactly ? Then can it give the result?

Many thanks

Cheers

Re: How to automatically upload an excel file's SHEETS?

Gysbert_Wassenaar — Mon, 16 Mar 2015 10:19:34 GMT

No, you can't use it as it is. See the example in the discussion I linked to. You can see in that example how the subroutine is used.

Re: How to automatically upload an excel file's SHEETS?

Mon, 16 Mar 2015 11:25:25 GMT

Gysbert,

I still see like the codes above sounds ok for it. I couldnt see any other need in the link. Am i wrong?