Advanced MS Excel CSV export example with grouping
This example illustrates how the following order details in an Excel workbook can be read with the help of the MS Excel Adapter and exported to a CSV text file using the TextFile Adapter.
This involves a special challenge in that it has details spread across up to 3 Excel rows for each order record, and these are to be output as a single row in the CSV file.
Warning: This example assumes you already familiar with the basic operation of FlowHeater. Do not attempt this example if you are just starting out. As an introduction to FlowHeater we suggest you start with the Hello World (1, 2, 3) series of examples and then proceed to the simpler examples before you attempt this one.
Excel CSV export example data
Starting position
The screenshot shows the completed Definition; don’t be dismayed, it is all quite straightforward. On the READ side the Excel Adapter reads the MS Excel workbook "Expert-Excel-CSV-Export.xls". On the WRITE side the TextFile Adapter writes the data into a CSV text file. Of course, the most interesting part of this transformation Definition is in the Fitter; the shaded area in middle of the Designer page.
How is the data processed?
The description of the sections circled in red follows from left to right and from top to bottom.
OrderNumber marking (1) The Excel field OrderNumber is initially passed to a .NET Script Heater. The script in this Heater remembers each OrderNumber and if an empty value is detected (as in the second and third rows of an order) the Heater passes back the last encountered OrderNumber instead. This value is then passed on to the GroupBy Heater. While consecutive incoming values are equal, the GroupBy Heater determines these rows are for consolidation. The Excel OrderNumber is further passed on through two Clone Heaters eventually emerging as the CSV field OrderNumber. Note: The only function of the Clone Heaters is to make the Definition visually clearer.
VehicleCode marking (2) The Excel field VehicelCode is divided into up to three different pieces of information. In the CSV file these are to be output as the three separate fields VehicleCode1, 2 and 3. To achieve this we make use of the AutoID Heater with its grouping function. The output of the Heater also looks quite different. Note: You can configure the grouping function in the Heater’s properties or via the context menu (right mouse click on the Heater). The AutoID Heater increments for each group starting with 1. This value is passed to each of three IfThenElse Heaters. Respectively their conditions test the input value as 1, 2 or 3 and so determine which CSV field FlowHeater writes to. Note: The If-Then-Else condition can be inspected if you double click on the Heater.
Color marking (3) Just as for VehicleCode, the successive rows of the Excel field Color contains up to three different pieces of information. However, the individual pieces are not output as three separate fields this time, but concatenated together as a single string as the CSV field Color, with each piece separated by a space character. To accomplish this we use the String Append Heater with its grouping function, configured via the Heater properties. This Heater receives a space character from an X-Value Heater as its first parameter and the Excel field Color as its second parameter. The String Append Heater passes the concatenated string on to a Trim Heater. This removes the leading space added for the first row of each new group and passes on the result to the CSV field Color.
To make this process clearer, this is how the String Append Heater accumulates the three rows in the group:
Row 1: [space]Value1
Row 2: [space]Value1[space]Value2
Row 3: [space]Value1[space]Value2[space]Value3
The Trim Heater is simply used as a stopgap to strip out the space character inserted before the value for the first row.
Result after Trim: Value1[space]Value2[space]Value3
OrderType: The Excel field OrderType is passed through a String Append Heater (with active grouping function) to the TextFile Adapter. This procedure the necessary in this case, because this field only contains data in the first row of each group and would otherwise be overwritten by empty cells in the second and third rows. You are surely wondering why this was not also necessary for the OrderNumber. It is quite simply because for the OrderNumber field the grouping is first carried out.
CustomerInfo: The same treatment as Color.
DeliveryDate The same treatment as OrderType, but with the addition of a Clone Heater on the left, solely to aid visual clarity.
SalesPerson: The same treatment as OrderType, but with the addition of a Clone Heater on the right, for visual clarity.
The CSV field Count Excel Rows: This simply demonstrates how the AutoID Heater delivers the number of grouped rows for each instance.
Microsoft®, Windows®, Excel® are registered trademarks of Microsoft Corporation
