topic Re: multiple excel sheets with non identical sheet names in QlikView

multiple excel sheets with non identical sheet names

nareshthavidishetty — Tue, 16 Feb 2016 09:58:12 GMT

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;

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

Re: multiple excel sheets with non identical sheet names

Chanty4u — Tue, 16 Feb 2016 10:19:55 GMT

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

Re: multiple excel sheets with non identical sheet names

jagan — Wed, 17 Feb 2016 02:27:44 GMT

Hi,

Check this link

Load all Excel files and all sheets in a folder

Regards,

jagan.

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Wed, 17 Feb 2016 05:44:02 GMT

hi jagan the link prompts for group permission any other go

Re: multiple excel sheets with non identical sheet names

jagan — Wed, 17 Feb 2016 06:23:55 GMT

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;

Hope this Helps you.

Regards,

Jagan.

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Wed, 17 Feb 2016 12:14:42 GMT

Hi am getting error as,

Script line error:

NEXT i

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Wed, 17 Feb 2016 12:57:12 GMT

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

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Wed, 17 Feb 2016 17:13:33 GMT

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

Re: multiple excel sheets with non identical sheet names

jagan — Thu, 18 Feb 2016 00:35:37 GMT

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.

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Thu, 18 Feb 2016 02:51:14 GMT

Hi,

Yes all sheets having same columns,

Total excel files: 4

Column count : AQ

Total sheets in each file : 70-90 Sheets

Re: multiple excel sheets with non identical sheet names

jagan — Thu, 18 Feb 2016 02:57:46 GMT

Hi,

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

Regards,

Jagan.

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Thu, 18 Feb 2016 03:49:41 GMT

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

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Thu, 18 Feb 2016 04:01:21 GMT

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

Re: multiple excel sheets with non identical sheet names

jagan — Thu, 18 Feb 2016 04:07:18 GMT

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.

Re: multiple excel sheets with non identical sheet names

jagan — Thu, 18 Feb 2016 04:09:19 GMT

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;

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Thu, 18 Feb 2016 04:51:08 GMT

Hi,

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

Thanks

Re: multiple excel sheets with non identical sheet names

jagan — Thu, 18 Feb 2016 05:06:45 GMT

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

Regards,

jagan.

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Thu, 18 Feb 2016 05:14:03 GMT

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

Re: multiple excel sheets with non identical sheet names

jagan — Thu, 18 Feb 2016 05:19:55 GMT

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.

Re: multiple excel sheets with non identical sheet names

nareshthavidishetty — Thu, 18 Feb 2016 14:27:23 GMT

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.