topic Load Excel file with multiple internal-cell lines in QlikView

Load Excel file with multiple internal-cell lines

dawgfather — Wed, 25 Nov 2020 16:16:04 GMT

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?

Re: Load Excel file with multiple internal-cell lines

settu_periasamy — Thu, 07 Jun 2018 21:36:15 GMT

Can you check this?

Splitting string into coloumns

Re: Load Excel file with multiple internal-cell lines

Anonymous — Fri, 08 Jun 2018 01:35:53 GMT

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;