topic Re: Multiple excel sheets loading at a time? in QlikView

Multiple excel sheets loading at a time?

Fri, 27 Feb 2015 10:32:23 GMT

Hi Folks,

I have a Excel file Sample.xlsx

it contains sheet names having different names like this AAA,BBB,CCC,DDD (but structure is same in all sheets.the field names all are same)

then how can i fetch all the sheets at atime in one load statement.

Plz help me on this.

regards,

Rakesh

Re: Multiple excel sheets loading at a time?

Anonymous — Fri, 27 Feb 2015 10:41:38 GMT

Hi Rakesh,

ODBC CONNECT TO [Excel Files;DBQ=ExcelFile.xlsx];

XlsTables:
SQLTables;
DISCONNECT;

LET vRows = NoOfRows('XlsTables');

FOR i = 0 TO $(vRows)-1

LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);

$(vSheetName):
LOAD *
FROM [ExcelFile.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)');

NEXT i

DROP TABLE XlsTables;

Re: Multiple excel sheets loading at a time?

buzzy996 — Fri, 27 Feb 2015 10:57:02 GMT

hopes it helps,

http://community.qlik.com/message/258417#258417

Re: Multiple excel sheets loading at a time?

Fri, 27 Feb 2015 10:59:29 GMT

Hi neeta,

I am having bit confussion on this.my source is excel so why i need to use odbc

and from wer i will get the field name as TABLE_NAME

Re: Multiple excel sheets loading at a time?

Anonymous — Fri, 27 Feb 2015 11:06:48 GMT

Hi,

Try:

ODBC CONNECT TO [Excel Files;DBQ=C:\Users\Source\YourExcelFileName.xlsx];

TABLE_NAME is field name in xlstables

Re: Multiple excel sheets loading at a time?

Fri, 27 Feb 2015 11:16:26 GMT

I don't have any field like TABLE_NAME in my source data

Re: Multiple excel sheets loading at a time?

Anonymous — Fri, 27 Feb 2015 11:23:38 GMT

Hi Rakesh,

Manually enter below line in script

ODBC CONNECT TO [Excel Files;DBQ=C:\Users\Source\YourExcelFileName.xlsx];

XlsTables:
SQLTables;

XlsTables: and SQLTables will create field TABLE_NAME with list of sheetnames as rows.

the below code will loop thru this rows and loads data from the sheets in excel file.

LET vRows = NoOfRows('XlsTables');

FOR i = 0 TO $(vRows)-1

LET vSheetName = subfield(peek('TABLE_NAME', i,'XlsTables'),'$',1);

$(vSheetName):
LOAD *
FROM [ExcelFile.xlsx]
(ooxml, embedded labels, table is '$(vSheetName)');

NEXT i

Hope this helps

Regards

Neetha

Re: Multiple excel sheets loading at a time?

Fri, 27 Feb 2015 11:25:16 GMT

thank u so much neeta... i vl try