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,

Did you done the limit load and checked DataModel for Synthetic keys?    Can you attach your current script.

Regards,

Jagan.

[nareshthavidishetty]

Hi,

Here is the current script,

LET vFilePath = 'F:\Qlikview\Template\Developers\dev\retail';

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

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

SheetNames:

SQLtables; 

DISCONNECT;

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

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

TableName:

Load *,

  FileBaseName()as FIle,

  FileDir() as Dir,

  FileName() as File_Name,

  '$(sheetNamez)' as Sheet_names

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

NEXT index

DROP TABLE SheetNames;

NEXT

[nareshthavidishetty]

Hi,

I have check with 10 records ,it has synthetic keys for each record.

Actually the data is for 2012,2013,2014,2015,2016 which has same fields.

No.of fields=30

Thanks

[jagan]

Hi,

If the field names are same then you wont get synthetic keys, I think there are different fields.  Try $Field & $Table as dimensions in Table Object and check whether is there any difference.

Regards,

jagan.

[jagan]

You can remove the synthetic key but we have to make sure our data and field names in the files are correct.

You can remove synthetic keys using Below script;

LET vFilePath = 'F:\Qlikview\Template\Developers\dev\retail';

Data:

LOAD

'' AS Dummy

AutoGenerate (0);

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

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

SheetNames:

SQLtables;

DISCONNECT;

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

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

Concatenate(Data)

Load *,

  FileBaseName()as FIle,

  FileDir() as Dir,

  FileName() as File_Name,

  '$(sheetNamez)' as Sheet_names

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

NEXT index

DROP TABLE SheetNames;

NEXT

DROP FIELD Dummy;

[nareshthavidishetty]

Hi,

It removes the synthetic keys,but the data is not populated correctly.

Thanks

[jagan]

That is why I asked you to check whether you have all the columns same in all the files. 

Regards,

jagan.

[nareshthavidishetty]

Hi,

Is there any other approach.

Below is the summary of the data source which am having,

No.of excel files - 4 (2013,2014,2015,2016)

No.of sheets in each excel file : 50-70 sheets,

No.of columns in each sheet : 30

Note: All columns are same in all sheets for all excel files

Thanks

[jagan]

Hi,

If all columns are same in all sheets then you won't get synthetic keys.  Instead of loading all the files first load one file and then verify the data is correct, then load another file repeat this.  Likewise you can find where the issue is.

Regards,

Jagan.

[nareshthavidishetty]

Hi,

Actually all columns are same but my sheets were defined by codes and in 2013 excel file am having 50 sheets and in 2015 excel file am having 70 sheets.I tried to create some dummy sheets on 2013 excel file to get same as

2015 excel file and then i ran script now its working fine.

Thanks Jagan.