Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
Try
MyTable: LOAD If(Len(Trim(Type))>0, Type, Previous(Type)) as Type, Batch FROM MyExcelFile.xlsx (ooxml, embedded labels, table is Sheet1) ;
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
Maybe this then:
MyTable: LOAD Type, SubField(Batch, chr(10)) as Batch FROM MyExcelFile.xlsx (ooxml, embedded labels, table is Sheet1) ;