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

        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#,

        [A] ,

        [B] ,

        [C] ,

           '$(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