Qlik Community

QlikView Documents

Documents for QlikView related information.

Use OLEDB to get Excel sheet names

gareth_wilson12
Contributor II

Use OLEDB to get Excel sheet names

Having in the past used the ODBC method of getting .xlsx sheetnames (How to load EXCEL worksheets and retrieve tab names), I recently worked for a client who didn't have access to be able to install the ODBC driver required on the server.

I was eventually able to get on OLEDB connection working, this shouldn't require an installation either on the client or server side.

The code below will read data from the excel file specified in the vFilePath variable.

It then reloads the data in the tables2 resident load and excludes the sheet names we do not to want to load.

Finally we use a For Each Loop to loop through the sheets and load them into Qlikview.

Hope this helps someone!

Let vFilePath = '\\path\to\my\file\';

//Get the Sheet Name of the file

FOR EACH file in FileList('$(vFilePath)');

//In order to get the file information from SQLtables command making use of the OLEDB connection

OLEDB CONNECT32 TO  [Provider=Microsoft.ACE.OLEDB.12.0;Data Source='$(vFilePath)';Extended Properties="Excel 12.0;HDR=YES";];

tables:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));

Concatenate

Table:

Load

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'[' & '$(sheetName)' & ']' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i;

Next

NoConcatenate

//Drop sheetnames we dont need/cant use

tables2:

Load Sheet_name Resident tables

Where not MixMatch(Sheet_name, '[Sheet1]'); //<< Incomplete Sheets

Drop Table tables;

//Loop through the sheetnames in the file and load our data

FOR EACH Sheet in FieldValueList('Sheet_name')

MyFile:

LOAD  field1,

  field2,

  field3,

     '$(Sheet)' as Sheet,

     Year(Date(Filetime())) as filetime,

     right(FileDir(),4) as Year

FROM

[$(vFilePath)]

(ooxml, embedded labels, header is 2 lines, table is  $(Sheet));

next;

Drop Table tables2;

Tags (3)
Version history
Revision #:
1 of 1
Last update:
‎01-03-2018 04:47 AM
Updated by: