Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
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.