Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

christian77
Valued Contributor

ODBC to EXCEL in SENSE

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.

1 Reply
Not applicable

Re: ODBC to EXCEL in SENSE

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.