multiple excel sheets with non identical sheet names

[nareshthavidishetty]

Hi i need to load multiple excel sheets with different sheet names like in

Excel-A the sheet names are : AA,BB,CC

Excel-B the sheet names are : AA,BD,CC,DD

like that i have tried to load this by using the following script,

FOR EACH file in FileList('F:\Qlikview_Development\Qlikview_Project_Template\Developers\viswanath\Lawson Budget\Budget for Lawson Export FY 13.xlsx');

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

SheetNames:

SQLtables;

DISCONNECT;

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

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

  if Right('$(sheetName)',8) <> 'Database' then

Table:

Load * ,

FileBaseName()as FIle,

FileDir() as Dir,

FileName() as File_Name,

'$(sheetName)' as Sheet_name

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

// store Table into F:\Qlikview_Development\Qlikview_Project_Template\Developers\viswanath\Lawson Budget-$(sheetName).qvd;

//                    DROP Table Table ;

//       end if;

NEXT i

//Drop table tables;

Next

But the script captures only identical sheet name and it showing error for no identical sheets.

From Excel-A and Excel-B it captures only AA and CC,for BD ,DD it shows error

[jagan]

Hi Naresh,

There is something going wrong, actually we are reading sheets names separately for each file, so if file has 50 sheets it will read only that 50 sheets after that it will move to next file.

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

SheetNames:

SQLtables;