Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi community,
I am trying to connect to Excel to load multiple sheets and get the sheet name. I want to use the sqltables function to do that. That is why I am using a connection and not loading the file as a normal excel spreadsheet.
In QlikView seems to work good, but in Sense, I am getting a problem with the connection string. It does not load anything at all. I have no data. I have tried:
- ODBC connect to 'Excel Files; DBQ= xxx.xlsx';
- CONNECT TO 'Excel Files; DBQ= xxx.xlsx';
- LIB CONNECT TO ..
And I get nothing..
Any input will be helpul. Thanks!
This is my code:
// set the data folder
let vDataFolder = 'C:xxx\Forecast\';
let vFile = 'C:\xxx\Forecast\Forecast.xlsx';
// enumerate files
for each vFile in filelist('$(vDataFolder)*.xlsx')
// connect to Excel file
ODBC CONNECT TO 'Excel Files;DBQ=C:\xxx\Forecast\Forecast.xlsx';
// Read list of sheets
Temp_Tables:
sqltables;
// Get just the file name
let vFileName = mid(vFile, index(vFile, '\', -1) + 1);
// Enumerate sheets
for iSheet = 0 to NoOfRows('Temp_Tables') - 1
let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');
let vSheetName = replace(replace(replace(vSheetName, '$', ''),'#','.'), chr(39), ''); // sqltables seems to add a random $ sign and single quotes
// Load the data
Data:
LOAD
RowNo() as Row#,
,
'$(vFileName)' as [File Name],
purgechar('$(vSheetName)','.') as [Sheet Name]
FROM [lib://Forecast/Forecast.xlsx]
(ooxml, no labels, table is [$(vSheetName)])
;
next
DROP TABLE Temp_Tables;
next
You'll have to define your odbc connection first too so it becomes available in the library.
Or you can try enabling the legacy script mode: Qlik Sense Legacy mode.