Format excel table load script

[Purple_13s]

Hi All, 

I have a an excel file in this format. Any advise on how I can format the column headers in the load script?

Year	Count			Resignation			% Attrition
	UK	Other Region	Total	UK	Other Region	Total
2022	363	1973	xxx	363	1973	xxx	xxx
2021	279	1786	xxx	279	1786	xxx	xxx
2020	655	1689	xxx	655	1689	xxx	xxx
2019	708	1576	xxx	708	1576	xxx	xxx
2018	900	1487	xxx	900	1487	xxx	xxx

[G3S]

what is the result you are expecting? 

from experience, might be better to make this a flat table. 

[stevedark]

Hi @Purple_13s 

You will have to set the header rows so that it picks up the second row as the headers. This will  most likely miss the year label, as this will be counted as being on row 1. My guess is it will refer to that column as @1 . As you have multiple columns with the same name (as the first row will be ignored) a suffix will be put on the extra columns.

The load will come out something like:

LOAD
   @1 as Year,
   UK as [Count UK],
   [Other Region] as [Count Other],
   [UK 1] as [Resignation UK],
   [Other Region 1] as [Resignation Other]
FROM [... your file ... ]
(ooxml, embedded labels, header rows 1);

The as statements and new fieldnames you will need to add yourself, but the rest should be created for you when you bring the file in. You won't need the totals or attrition values, as you can calculate these in the front end.

You might also want to change UK/Other into separate rows, rather than columns, like this:

LOAD
   @1 as Year,
   'UK' as Type,
   UK as Count,
   [UK 1] as Resignation
FROM [... your file ... ]
(ooxml, embedded labels, header rows 1);

LOAD
   @1 as Year,
   'Other Region' as Type,
   [Other Region] as Count,
   [Other Region 1] as Resignation
FROM [... your file ... ]
(ooxml, embedded labels, header rows 1);

Hope that all makes sense?

Steve

https://www.quickintelligence.co.uk/blog/