topic Re: Use excel sheet name in field in QlikView

Use excel sheet name in field

AbhijitBansode — Mon, 26 Aug 2013 10:05:40 GMT

Hi all,

I have many sheets within the excel file.

I'm concatenating data of each sheet with the previously loaded sheet's data.

Even though structure of all the load statements is same,

I Can't use foreach loop, because each sheet's load logic is different from another.

Within the load statement, I want to use table name i.e. sheet name of which data is being loaded.

This table name (sheet name) is one of the member of the composite key of the table.

Refer below Eg:

Load a,
     b,
     Autonumber ( 'D001'&a) as [% Key]
From
[Data.xlsx]
(ooxml, no labels, table is D001);

Load a,
     b*100 as b,
     Autonumber ('D299'@a) as [% key]
From
[Data.xlsx]
(ooxml, no labels, table is D299);

As shown above, currently, I'm doing hard coding of the sheet name to generate % key column.

Is there way to achieve this dynamically, without hard coding sheet name.

Regards,

Abhijit

Re: Use excel sheet name in field

tresB — Mon, 26 Aug 2013 10:12:27 GMT

You have to try with ODBC connection. Have a look here

PFA, it would help.

Re: Use excel sheet name in field

marcus_sommer — Mon, 26 Aug 2013 10:35:22 GMT

You could put an extra sheet in these excel-file and create there with a small loop (for each sh in sheets ...) a list from all sheets (index, names and perhaps in groups) and used this first in your load statement to generate a variable for an appropriate load-loop.

Couldn't you change these file, you could create such a list also extern per vbs-script and save the result in a text-file.

- Marcus

Re: Use excel sheet name in field

AbhijitBansode — Mon, 26 Aug 2013 11:05:51 GMT

Thanks for reply.

I'm not sure if ODBC connections will do the needful for me.

It will give me the names of sheets in the field and I need to traverse that filed to load the data form particular sheet.

But, As already stated, I cant use for loop for loading the data as logic of loading the same filed is different in different sheets. (column b in example)

Also, there is possibility that, between D001 and D299 sheets, there are other sheets, so I cant use for loop either.

Re: Use excel sheet name in field

AbhijitBansode — Mon, 26 Aug 2013 11:08:35 GMT

Marcus Sommer , Thanks for reply.

I can not change the structure of source file.

Re: Use excel sheet name in field

tresB — Mon, 26 Aug 2013 11:23:26 GMT

If your field b is differently calculated based on sheet, then :

Load

If ( $(vSheetName)= 'D001', b,If( $(vSheetName)=''D299', b*100)) as b // you can optimize the same using Pick(Match())

Can that be an option?

Re: Re: Use excel sheet name in field

marcus_sommer — Mon, 26 Aug 2013 11:37:04 GMT

An excel-file extern per vbs to read is not very complicated - see the attachment. Also could you read the data-structure from sheet for certain conditions or different load-statements.

- Marcus

Re: Use excel sheet name in field

Tue, 02 Feb 2016 21:57:00 GMT

Hi,

Here you are the solution to your problem.

LET vArchivosConf = 'C:\QlikView\Pruebas\Plantillas\';

LET vArch = '$(vArchivosConf)'&'01012016.xls';

If(Not isnull(FileTime('$(vArchivosConf)'&'01012016.xls'))) then //validate if exists the file

// connect to eaach Excel file

OLEDB CONNECT32 TO [Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Excel Files;DBQ=$(vArch);DriverId=1046;MaxBufferSize=2048;PageTimeout=5;"];

// Read list of sheets

Temp_Tables:

sqltables;

// Enumerate las hojas del archivo

for iSheet = 0 to NoOfRows('Temp_Tables') - 1

let vSheetName = peek('TABLE_NAME', iSheet, 'Temp_Tables');

let vSheetName = replace(replace(vSheetName, '$', ''), chr(39), '') & '$';

Metas:

LOAD Codigo,

'$(vSheetName)' as SheetName

FROM [$(vArchivosConf)$(vArchivo)]

(biff, embedded labels, table is [$(vSheetName)]);

DROP TABLE Temp_Tables;

EXIT For;

ENDIF

I hope help you