Use excel sheet name in field

[AbhijitBansode]

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

[tresesco]

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

PFA, it would help.

[marcus_sommer]

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

[AbhijitBansode]

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.

[AbhijitBansode]

Marcus Sommer , Thanks for reply.

I can not change the structure of source file.

[tresesco]

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?

[marcus_sommer]

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

[#8]

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)]);

  next

  DROP TABLE Temp_Tables;

  EXIT For;

  ENDIF

I hope help you