1 Reply Latest reply: May 9, 2017 12:15 AM by paul sun RSS

    ODBC to EXCEL in SENSE

    Christian Conejero

      Hi:

       

       

      That's what I need, but I don't know how to make it work in Sense. It Works fine in QlikView.

       

             ODBC CONNECT32 TO [Excel Files;DBQ=$(File)];                    
                               tables:
                               
      LOAD
                                    *;
                               
      SQLtables;
                        
      DISCONNECT;

       

       

      That's because the Excel book has unkown number of sheets with unknown names.

      Once I pick up those names I just loop over the sheets.

       

       

      Thanks.

        • Re: ODBC to EXCEL in SENSE
          paul sun

          Hi,

           

          I'm new to Qlik view and Sence, but have same requirement as you, after searching i got it worked like below, hope it could give any useful information.

           

          First I just found CONNECT32 not works for me but CONNECT64 works, this may cased by your odbc driver version, so i just user CONNECT not specify 32 or 64.

           

          As second, make sure you create ODBC in SYSTEM DSN and named 'Excel Files', or any other name but just need change [Excel Files;DBQ=$(File)] to [your name;DBQ=$(File)]; for me, i create Excel in system dsn and use [Excel;DBQ=$(file)]

           

          so those two steps make me success to load multi tab for once excel; the scrip is

          LET vFilePath = 'C:\';
          
          FOR EACH file in FileList('$(vFilePath)\*.xlsx');   // Loops each excel file in the given Folder
          //In order to get the file information from SQLtables command making use of the ODBC connection format
          ODBC CONNECT TO [Excel;DBQ=$(file)];
          
          SheetNames:
          SQLtables;  // Loads all sheet names in the Excel file.
          
          DISCONNECT;
          
          FOR index = 0 to NoOfRows('SheetNames')-1  // Loops for each sheet in the Excel file.
          LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));
          
          if not WildMatch(sheetName,'*xlnm*') then //In case your sheet contains any filter or other 
          TableName:
          Load * ,
            FileBaseName()as FIle,
            FileDir() as Dir,
            FileName() as File_Name,
            '$(sheetName)' as Sheet_name
          From $(file)(ooxml, embedded labels, table is [$(sheetName)]);
          ENDIF
          NEXT index
          DROP TABLE SheetNames;
          NEXT
          

           

          the code is based on Jagan's answer Load Multiple excel sheets using For loop  and Settu's in duplicate entries when reading (3) worksheets in workbook.

           

          hope this could works for you.