Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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
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.
Gysbert,
I still see like the codes above sounds ok for it. I couldnt see any other need in the link. Am i wrong?