topic Re: Loading several sheets without using ODBC in QlikView

Loading several sheets without using ODBC

brunopaulo — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Loading several sheets without using ODBC

Anil_Babu_Samineni — Wed, 06 Dec 2017 10:15:10 GMT

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

Re: Loading several sheets without using ODBC

brunopaulo — Wed, 06 Dec 2017 10:16:34 GMT

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

Thank you

Re: Loading several sheets without using ODBC

Anil_Babu_Samineni — Wed, 06 Dec 2017 10:18:46 GMT

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

Re: Loading several sheets without using ODBC

brunopaulo — Wed, 06 Dec 2017 10:25:44 GMT

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

Re: Loading several sheets without using ODBC

neha_shirsath — Wed, 06 Dec 2017 10:26:17 GMT

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

Re: Loading several sheets without using ODBC

brunopaulo — Wed, 06 Dec 2017 10:29:26 GMT

In this link the sugestion is to use connection ODBC.

Re: Loading several sheets without using ODBC

neha_shirsath — Wed, 06 Dec 2017 10:33:00 GMT

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');

Re: Loading several sheets without using ODBC

Anil_Babu_Samineni — Wed, 06 Dec 2017 10:33:01 GMT

You can just load using this?

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

Load * FROM $(file) ....

Next

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

Re: Loading several sheets without using ODBC

brunopaulo — Wed, 06 Dec 2017 10:36:06 GMT

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;

This is the code they're using

Re: Loading several sheets without using ODBC

brunopaulo — Wed, 06 Dec 2017 10:42:14 GMT

I'm trying do this

FOR EACH vSheet IN 'Alias','Sheet different','Sheet1','Business B'

Function:

LOAD Name,

Number,

Function

FROM

[..\Dados\NameFile.xls]

(biff, embedded labels, header is 1 lines, table is $(vSheet));