Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

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

Re: Connect to Excel Files in Sense to load multiple sheets

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
Community Browser