topic Re: One cell from multiple sheets in excel to QV in QlikView

One cell from multiple sheets in excel to QV

MindaugasBacius — Mon, 29 Feb 2016 14:28:14 GMT

Hei,

Maybe someone owns a code that allows to load data from one single cell from multiple sheets from excel to QV?

1. Sheet names are unknown.

2. The cell I need is in B3.

I have attached the excel file and have been trying for a while.

Any ideas would be appreciated.

Thank you!

Re: One cell from multiple sheets in excel to QV

t_chetirbok — Mon, 29 Feb 2016 14:47:08 GMT

Hello!

Try this:

FOR i=1 TO 3

LOAD

FROM

(ooxml, no labels, table is Sheet$(i));

Re: One cell from multiple sheets in excel to QV

MindaugasBacius — Mon, 29 Feb 2016 14:51:51 GMT

I've checked this option:

Load Multiple excel sheets using For loop

I would like to update my task. Sheet names are not predefined.

Re: One cell from multiple sheets in excel to QV

t_chetirbok — Mon, 29 Feb 2016 14:59:40 GMT

anyway you need to know names and count of sheet

You can use just several loads and concatenate

Re: One cell from multiple sheets in excel to QV

Colin-Albert — Mon, 29 Feb 2016 15:07:51 GMT

If the cell is always the same (B3) then you can use a transformation in the load script to remove the rows in the spreadsheet above B3 and add a "first 1" command before the load so you only load 1 row of data.

Then just select column B.

Finally use this load in your loop to get the data from all sheets.

Re: One cell from multiple sheets in excel to QV

MindaugasBacius — Tue, 01 Mar 2016 11:08:52 GMT

The script:

ODBC CONNECT TO [Excel Files;DBQ=C:\Users\Mindaugasb\Desktop\QV.xlsx];

tables:
SQLtables;
DISCONNECT;

FOR i = 0 to NoOfRows('tables')-1
LET sheetName = purgeChar(purgeChar(peek('TABLE_NAME', i, 'tables'), chr(39)), chr(36));
Table:
Load * ,
  FileBaseName()as File,
  FileDir() as Dir,
  FileName() as File_Name,
  '$(sheetName)' as Sheet_name,
  RowNo() as nr
From $(file)(ooxml, no labels, table is [$(sheetName)], filters(
Remove(Row, Pos(Top, 2)),
Remove(Row, Pos(Top, 1))
));

NEXT i

The story is that I have faced few issues:

ODBC connection didn't work. IT guys installed QV for 64 bits. That solved the main problem.
I have found a script for loading data from multiple excel sheets in Loading Multiple Excel Sheets Dynamically along with file name and sheet name. Made few changes.
To load only one cell I used Albert Colin advice and Transformed the excel table.