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;
