Loading several sheets without using ODBC

[brunopaulo]

Hi community,

I'm trying load several sheets from 1 excel file but i can't do it.
The sheets names are random (and i want do this dynamically)
I can't make the connection odbc ( i tried 32, 64 and only with connect to) problem i don't know acess to server and/or if excel file is in server. So i'm trying turn this around.

Any ideas?

Thank you
Best Regards

Bruno Paulo

[Anil_Babu_Samineni]

I don't think whether we need ODBC connection to pull data from Excel's..

[brunopaulo]

So can u explain me how to load several sheets dynamically please?

Thank you

[Anil_Babu_Samineni]

Not sure, How your data structure looks like in Excel. Can you make

SheetName , Some fields from different sheets.. If structure is same then we can make or else we need to look

[brunopaulo]

See the attach file. My excel is something like that. Where the sheet names is diferent and the number of sheets can be variable.

[neha_shirsath]

For fetching data from excel you dont need ODBC connection.

You can go through below link for dynamic sheet fetching from excel-

https://community.qlik.com/message/1198272

[brunopaulo]

In this link the sugestion is to use connection ODBC.

[neha_shirsath]

See for correct mark comment- replace your file path instead of ODBC i.e-

if you want to implement it on one file then replace * with your exact file name 

FOR EACH file in FileList('full_Path\file_name.xlsx');

[Anil_Babu_Samineni]

You can just load using this?

For each file in FileList('*.xlsx')

        Load * FROM $(file) ....

Next

OR

Load * From [Path\*.xls] (ooxml,...)

[brunopaulo]

FOR EACH file in FileList('\*.xlsx');

ODBC CONNECT32 TO [Excel Files;DBQ=$(file)];

Temp:

SQLtables;

DISCONNECT;

FOR i = 0 to NoOfRows('Temp')-1

LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'Temp'), chr(39)), chr(36));

Table:

Load * ,

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

From $(file)(ooxml, embedded labels, table is [$(sheetName)]);

NEXT i

//Drop table tables;

Next

This is the code they're using