Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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!
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)))
));
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))
))
));
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.
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!
YES! perfect thank you both for your input.