Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Connect to Excel Files in Sense to load multiple sheets

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

1 Reply
Gysbert_Wassenaar

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‌.


talk is cheap, supply exceeds demand