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

[Chanty4u]

ForEach vFile in FileList('$(vPath)')

     ODBC CONNECT32 To [Excel Files;DBQ=$(vFile)];

     Sheets:

     SQLTABLES;

     DISCONNECT;

     For i = 0 To NoOfRow('Sheets')

          Let zSheet = Peek('TABLE_NAME, i, 'Sheets');

          ... do something with the file and sheet name, eg

          LOAD ....

          From [$(vFile)]

          (ooxml, no labels, table is [$(zSheet)]);

     Next

Next

[jagan]

Hi,

Check this link

Load all Excel files and all sheets in a folder

Regards,

jagan.

[nareshthavidishetty]

hi jagan the link prompts for group permission any other go

[jagan]

This is the content of that thread

Hi All,

The below scripts helps us in loading all Excel files and all sheets in a Folder into Qlikview.   Just change the file pathvFilePath variable to use this script.

LET vFilePath = 'C:\';

FOR EACH file in FileList('$(vFilePath)\*.xlsx');   // Loops each excel file in the given Folder

//In order to get the file information from SQLtables command making use of the ODBC connection format

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

SheetNames:

SQLtables;  // Loads all sheet names in the Excel file.

DISCONNECT;

FOR index = 0 to NoOfRows('SheetNames')-1  // Loops for each sheet in the Excel file.

LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

TableName:

Load * ,

  FileBaseName()as FIle,

  FileDir() as Dir,

  FileName() as File_Name,

  '$(sheetName)' as Sheet_name

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

NEXT index

DROP TABLE SheetNames;

NEXT

Hope this Helps you.

Regards,

Jagan.

[nareshthavidishetty]

Hi am getting  error as,

Script line error:

NEXT i

[nareshthavidishetty]

Hi below is the script

LET vFilePath = 'F:\QlikviewQlikview\Devel\app\sales';

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

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

SheetNames:

SQLtables;  // Loads all sheet names in the Excel file.

DISCONNECT;

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

LET sheetName = PurgeChar(PurgeChar(Peek('TABLE_NAME', index, 'SheetNames'), Chr(39)), Chr(36));

TableName:

Load * ,

  FileBaseName()as FIle,

  FileDir() as Dir,

  FileName() as File_Name,

  '$(sheetName)' as Sheet_name

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

NEXT index

DROP TABLE SheetNames;

NEXT

[nareshthavidishetty]

Hi Jagan ,the script executes with no error but it struck ie.,it stand by means it not execute completely.Let me know what might be the issue

[jagan]

Hi,

Is all sheets having same columns?  Try doing a limit load of 10 records and check whether is there any synthetic key tables created?

Regards,

jagan.

[nareshthavidishetty]

Hi,

Yes all sheets having same columns,

Total excel files: 4

Column count : AQ

Total sheets in each file : 70-90 Sheets