1 Reply Latest reply: Oct 9, 2015 10:34 AM by Gysbert Wassenaar RSS

    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





        // 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



        RowNo() as Row#,

        [A] ,

        [B] ,

        [C] ,

           '$(vFileName)' as [File Name],

            purgechar('$(vSheetName)','.') as [Sheet Name]


        FROM [lib://Forecast/Forecast.xlsx]

        (ooxml, no labels, table is [$(vSheetName)])






        DROP TABLE Temp_Tables;