XML part of XLS data - how to extract

[Teppo_Ojala]

Hi,

I found couple of quite similar cases here, but yet I haven't solved my problem.

Attached a xls file that consists of two columns

A = Group name

B = XML information about which products belongs to the GROUP from column A. One GROUP can have 1-X products.

In this example GROUP 1 should have 4 products (A, B, C and D) - and GROUP 2 should include only two products (A and B).

I couldn't write a script that would return a data model with 1 or 2 tables where groups are linked to correct products.

Hopefully somebody here has skills for this

[marcus_sommer]

Just as idea. At first I would read the file normal as xls. Then I would loop through all records and pick with peek() the content and create from xml-content a load-statement:

xls:

Load * From xls;

for i = 0 to fieldvaluecount('Product') - 1

     let vGroup = peek('Group', $(i), 'xls');

     let vProduct = peek('Product', $(i), 'xls');

...

next

Here is a similar case: Re: Load Data from gisgraphy / xml source

- Marcus

[Ralf-Narfeldt · Accepted solution]

Or a crude parsing:

Directory;

Source:

LOAD Group,

     Product

FROM

temp.xlsx

(ooxml, embedded labels, table is [Koontikyselyn erikoishaun n...]);

Temp:

Load Group,

     SubField(Product, 'value uiname=') As ProductRaw

     resident Source;

Result:

Load Group,

     TextBetween(ProductRaw, '"', '"') As Product1

     resident Temp where Left(ProductRaw, 1)<> '<';

[Teppo_Ojala]

Thanks Ralf and Marcus,

I just used Ralf's script and it worked perfectly!