Qlik Community

Ask a Question

QlikView Documents

QlikView documentation and resources.

Announcements
Join us for a live Q&A! September 21, 10 AM ET - Onboarding Fast in Qlik Sense SaaS! REGISTER

Use OLEDB to get Excel sheet names

gareth_wilson12
Partner
Partner

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)
Comments
NickP_DF
Creator
Creator

Hello Gareth,

I've got the same problem you had (I can't use ODBC to retrieve the list of sheets), so I'd like to use your procedure, but it gives me an error:

OleDb error

Connector connect error: IDataInitialize::GetDataSource. HRESULT = 80040154. ErrorSource: Microsoft OLE DB Service Components, ErrorMsg: Not registered interface.

OLEDB CONNECT32 TO ***

 

Would you mind to help me 'cos I'm not skilled in configuring OLEDB? Pls note that I'm using 64-bit Office.

Thank you so much.

N.

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