Wednesday 7 March 2012

Looping Through Excel Sheets

I answered a question on the QlikCommunity the other day and it reminded me of something I've not done for a little while, looping through sheets in an Excel file within a QV script.

The key to achieving this is to use an ODBC connection to connect to the Excel file first, and then use the SQLTables command to return a table listing the sheets (tables) within the file. Once you have this you can loop through this table and load the sheets like this:

ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];

XlsTables:
SQLTables;

DISCONNECT;

LET vRows = NoOfRows('XlsTables');

FOR i = 0 TO $(vRows)-1
    LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);

    $(vSheetName):
    LOAD
        *
    FROM [ExcelFile.xlsx]
    (ooxml, embedded labels, table is '$(sheetName)');
NEXT i

DROP TABLE XlsTables;


This little trick can come in handy and with a few tweaks it can also address a number of other senarios for dealing with Excel spreadsheets such as:
  • What if I want to load from an .xls file instead of a .xlsx file? Not a problem, simply change the Connect and Load statements accordingly.
  • What if I want all the sheets loaded into a single table? You can create a blank table first, then use the above to concatenate the sheets into it rather than into their own named tables.
  • What if the Excel file always has one sheet but it is named differently each time? Tweak the above to load the sheet into a known table in QV.
  • What if, rather than loading all sheets from the file, I want to load only those which start with a known string (eg. "My Data Tab 01/01/2012", "My Data Tab 01/02/2012", etc). Simply wrap an IF statement around the LOAD to check if the table name starts with the required string.
I've probably only ever used this trick a handful of times, but its a very useful tool to have in your pocket should you need it.

1 comment:

  1. Hi Matthew, I really like this trick but have just noticed that it only works when I had the Excel document open. When it is closed, vRows grows to 7 (should be 4 in my document) and it repeats the first 3 tabs. Any thoughts? John

    ReplyDelete