Load Excel file with multiple internal-cell lines

[dawgfather]

I am trying to ingest an Excel sheet into QlikView, but I have found that one field/column ends up having some cells/instances with multiple internal cell-lines of data. I need to effectively form some kind of loop to run through each of those internal cell-rows to pull out data.

To complicate it further, the data that is in each of those rows is actually a set of 4 separate fields all seemingly crammed into one cell.

Example (I believe data in the “Details” column is tab delineated internally because if, in Excel, I copy the contents and then paste into a new sheet, it pastes into the separate columns and rows):

I prettied this example up so you can get the feel of the cell-internal separate fields.

Item	Details	Type	Code
Apple	Name   Color  Weight Price Gala      Red     1        0.25 Fuji        Red     1        0.30 Granny  Green  1.1     0.35	Fruit	1111
Orange	Name           Color    Weight  Price Florida         Orange  1.5       0.86 Indian River Orange  1.7       0.92	Fruit	2222

What I want to get ultimately is an output like this (and I am okay with the title line being included if I have to):

Item	Details-Name	Details-Color	Details-Weight	Details-Price	Type	Code
Apple	Gala	Red	1	0.25	Fruit	1111
Apple	Fuji	Red	1	0.3	Fruit	1111
Apple	Granny	Green	1.1	0.35	Fruit	1111
Orange	Florida	Orange	1.5	0.86	Fruit	2222
Orange	California	Orange	1.7	0.92	Fruit	2222

Anyone have a great ideas?

[settu_periasamy]

Can you check this?

Splitting string into coloumns

[Anonymous]

Hello,

You can try something like this:

Data:
LOAD Item,
     SubField(Details, Chr(10), 1) & ' Item' & Chr(10) &
     Replace(Right(Details, Len(Details) - Index(Details, Chr(10))), Chr(10),
          ' ' & Item & Chr(10)) & ' ' & Item As Details,
     Type,
     Code
FROM [Data.xlsx]
(ooxml, embedded labels, table is Sheet1);


FinalData:
LOAD Item,
     Name As [Details-Name],
     Color As [Details-Color],
     Weight As [Details-Weight],
     Price As [Details-Price]
FROM_FIELD (Data, Details)
(txt, utf8, embedded labels, delimiter is spaces, msq);


Left Join
Load Item,
     Type,
     Code
Resident Data;


Drop Table Data;