Loading a Dynamic Excel File

prees959 — Fri, 26 Oct 2018 08:59:38 GMT

Hi,

I need to load a table from excel where the column headings are dates, but the each month the dates roll along...

Unit Savings	Oct-17	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18
Finance	87%	87%	86.60%	86.90%	87.20%	87.20%	86.60%	86.90%	86.70%	86.10%	85.40%	71.40%
Production	91%	92%	91.4%	91.20%	91.30%	91.30%	91.40%	91.30%	90.70%	89.70%	89.20%	88.50%
Personnel	89%	89%	88.6%	89.30%	89.80%	89.70%	90.20%	90.10%	89.10%	88.80%	88.10%	87.10%
Estates	94%	94%	92.80%	93.40%	92.40%	90.80%	90.80%	91.20%	91.10%	91.00%	91.30%	90.60%
Directors	82%	83%	82.6%	82.80%	83.20%	83.30%	83.10%	82.90%	82.60%	81.80%	81.90%	82.30%
Manufacturing	80%	80%	81.0%	81.30%	82.50%	81.70%	81.30%	80.70%	80.60%	79.00%	79.10%	78.80%

So the next Month I will have the same dataset with the following headings :

Unit Savings	Nov-17	Dec-17	Jan-18	Feb-18	Mar-18	Apr-18	May-18	Jun-18	Jul-18	Aug-18	Sep-18	Oct-18

What would be the correct way to load this data and set the fieldnames to the corresponding column header?

something like

Load

'Unit Savings'

ColumnB as 'ColumnB Value',

ColumnC as 'ColumnC Value',

ColumnD as 'ColumnD Value'

etc etc

Thanks for any advice

Phil

Re: Loading a Dynamic Excel File

Gysbert_Wassenaar — Fri, 26 Oct 2018 16:11:54 GMT

Use the CrossTable function:

CrossTable(Month, Value)

LOAD * FROM ....