Field has multiple values, need to strip out and use each one

[spikenaylor1]

I have a sheet set like this

    A                      B

1 Type          Batch

2 Minor        AB1234

3 Minor       AB6789

                      AB9876

4 Minor       CC5645

                       CC6786

                       CC9865

There is the possibility of lots of batch numbers in each field

I need to load the data such that I end up with effectively

    A                      B

1 Type          Batch

2 Minor        AB1234

3 Minor       AB6789

 4 Minor      AB9876

5 Minor       CC5645

6 Minor      CC6786

7 Minor      CC9865

 any ideas on how to achieve this?

Regards

[Gysbert_Wassenaar]

Try

MyTable:
LOAD
    If(Len(Trim(Type))>0, Type, Previous(Type)) as Type,
    Batch
FROM
   MyExcelFile.xlsx (ooxml, embedded labels, table is Sheet1)
    ;

[spikenaylor1]

sorry, maybe not clear enough, in the spreadsheet, B3 contains both Batch numbers with a line break between (Alt-Enter)

B4 contains 3 batch numbers with a line break between each.

It is the Batch numbers I need to strip out and use as separate rows with the accompanying Type

regards

[Gysbert_Wassenaar]

Maybe this then:

MyTable:
LOAD
    Type,
    SubField(Batch, chr(10)) as Batch
FROM
   MyExcelFile.xlsx (ooxml, embedded labels, table is Sheet1)
    ;