Loading unstructured data from Excel

[jaimeoconor]

Hi all,

I have different Excel files from different dates, with always the same format:

Company name
Address
Security Number
			Date: 09/08/2019
Account number	Name	Amount
Class A
5645	Axxxx	100
7865	Bxxx	75
86543	Cxxx	45
Class B
423423	Dxxx	76
2434	Exxx	89

I need to load every file in the following way:

Account number	Name	Amount	Date	Class
5645	Axxxx	100	09/08/2019	A
7865	Bxxx	75	09/08/2019	A
86543	Cxxx	45	09/08/2019	A
423423	Dxxx	76	09/08/2019	B
2434	Exxx	89	09/08/2019	B

Any ideas? Thanks in advance!

[Lisa_P]

Try this script:
LOAD IF(IsNum([Account number]), [Account number], null()) as [Account number],
Name,
Amount,
SubField([Date: 09/08/2019],' ',2) as Date,
IF(IsText([Account number]), subField([Account number],' ',2), peek(Class)) as Class
FROM [lib://Community/unstructured.xlsx]
(ooxml, embedded labels, header is 3 lines, table is Sheet1, filters(
Replace(4, top, StrCnd(null)),
Remove(Row, Pos(Top, 1))
));

[eduardo_dimperio]

You could Load 2 times the same excel, first to get date, second to get other data and join both.

Table:

LOAD
[Account number],

Name,

Amount

FROM [lib://YourExcel.xlsx]
(ooxml, embedded labels, table is YourExcel); //Increase head as necessary

LEFT JOIN (TABLE)
LOAD
'E' AS Date
FROM [lib://YourExcel.xlsx]
(ooxml, embedded labels, table is YourExcel);