One cell from multiple sheets in excel to QV

[MindaugasBacius]

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!

[t_chetirbok]

Hello!

Try this:

FOR i=1 TO 3 

  LOAD 

        A

FROM

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

NEXT

[MindaugasBacius]

I've checked this option:

Load Multiple excel sheets using For loop

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

[t_chetirbok]

anyway you need to know names and count of sheet

You can use just several loads and concatenate

[Colin-Albert]

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.

[MindaugasBacius · Accepted solution]

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:

1. ODBC connection didn't work. IT guys installed QV for 64 bits. That solved the main problem.
2. 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.
3. To load only one cell I used Albert Colin advice and Transformed the excel table.