ODBC to EXCEL in SENSE

[christian77]

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.

[#2]

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.