Multiple excel sheets loading at a time?

[#1]

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

[Anonymous]

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;

[buzzy996]

hopes it helps,

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

[#4]

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

[Anonymous]

Hi,

Try:

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

TABLE_NAME  is field name in xlstables

[#6]

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

[Anonymous · Accepted solution]

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

[#8]

thank u so much neeta... i vl try