Using column name as value

[thrwmcmesing]

Hello Experts,

I am trying to do load multiple Excels and concatenate them as they each contain data from one specific company (they come in the same format).

In one sheet you have all the data and in a different sheet there is only cell  A1 filled  (with only the name of the Parent Company)

I need to join the first table with the value of the cell in A1 in the second sheet. 

Name of Company| CEO |Country| Sales| Parent Company

Nespresso | John Green| Antartica | 1 dollar |   Nestle

I have no idea how to fill in the column values with the cell A1 (as when i load the data, it just comes as the column name)

I understand i have to use a loop in order to load all the Excels but the bit about the second sheet A1 cell ...I have no idea how  to solve. 

Could you please help me out with this?

[marcus_sommer]

You could try the following:

temp: load A as A from Excel.xlsx (ooxml, no labels, table is sheet1);

final: load peek('A', 0, 'temp') as Company, * from Excel.xlsx (ooxml, embedded labels, tables is sheet2);

drop tables temp;

- Marcus