question about formatting my excel data

[#1]

Hello,

I have a problem with my datasource.

I want to import data from an excel dat i get delivered from my clients, this excel is an export from some program.

I do not have the possibility to change the export so i want to format it while loading the tables.

As you can see in the attachment, sheet: Problem. It is some strange form of reporting.

I have tried formatting the data by using a For loop to create a correct table.

sadly this did not work.

In the sheet: Result is shown how i would like to see the data imported.

Does anyone know how i can perform this?

I appreciate any answer.

[alexandros17]

Look at this syntax, I never used it directly but I know it works ...

Hope it helps

LOAD [Server Name],

     Date,

     Time,

     User,

     Program,

     [Security Audit Log message text]

FROM

(ooxml, embedded labels, table is Sheet1, filters(

Remove(Row, RowCnd(CellValue, 2, StrCnd(null)))

));

[bertdijks]

This should do the trick:

This code is generated by the import wizard  --> transformatiion steps:

Problemdata:

LOAD Artikel as Date,
    Quantity,
    [Price/each],
    F8 As Artikel
FROM
[Problem excel.xls]
(biff, embedded labels, header is 1 lines, table is Problem$, filters(
ColXtr(1, RowCnd(Compound,
RowCnd(CellValue, 1, StrCnd(contain, '0', not)),
RowCnd(CellValue, 1, StrCnd(start, 'Artikel', not))
), 0),
Replace(8, top, StrCnd(null)),
Remove(Row, RowCnd(Compound,
RowCnd(CellValue, 1, StrCnd(contain, '0', not)),
RowCnd(CellValue, 1, StrCnd(contain, 'Artikel', not))
))
));

[#4]

Hi Bert,

This seems to do it almost in the way i would want it. But now i miss some data, it only imports 4 rows of my whole dataset.

[#5 · Accepted solution]

Got a different script from the wizard than Bert's -

LOAD Artikel as Date,

     Quantity,

     [Price/each],

     F8 as Artikel

FROM

(biff, embedded labels, header is 1 lines, table is Problem$, filters(

ColXtr(1, RowCnd(CellValue, 2, StrCnd(null)), 0),

Replace(8, top, StrCnd(null)),

Remove(Row, RowCnd(CellValue, 2, StrCnd(null))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Total'))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Totaal'))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(null))),

Remove(Row, RowCnd(CellValue, 1, StrCnd(equal, 'Overall Total')))

));

HTH!

[#6]

YES! perfect thank you both for your input.