Example: Replace Empty CSV Fields

www.altova.com Print this Topic Previous Page Up One Level Next page

Home >  Functions > Defaults and Node Functions >

Example: Replace Empty CSV Fields

This example shows you how to create a MapForce mapping that reads data from a CSV file and writes data to another CSV file. The goal is to replace all empty fields from the source CSV file with a custom value ("n/a"). In other words, assuming that the source CSV file looks as follows:

 

H,111,332.1,22537.7,,Container ship,,,

D,111,A-1579-227,10,3,400,Microtome,,

D,111,B-152-427,7,6,1200,Miscellaneous,,

H,222,978.4,7563.1,,Air freight,,,

D,222,ZZ-AW56-1,10,5,10000,Gas Chromatograph,,

 

then the desired mapping output should look as follows:

 

H,111,332.1,22537.7,n/a,Container ship,n/a,n/a,n/a

D,111,A-1579-227,10,3,400,Microtome,n/a,n/a

D,111,B-152-427,7,6,1200,Miscellaneous,n/a,n/a

H,222,978.4,7563.1,n/a,Air freight,n/a,n/a,n/a

D,222,ZZ-AW56-1,10,5,10000,Gas Chromatograph,n/a,n/a

 

You can find the mapping created in this example at the following path: <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\ReplaceEmptyFields.mfd. The source CSV file for this mapping is called Orders.csv and is in the same folder. The target CSV file will be generated by MapForce.

 

To achieve the mapping goal, we will create a single node function that replaces each encountered empty value with "n/a". As shown below, this function is defined only once but it applies to multiple descendant CSV fields.

 

Step 1: Add the source CSV file to the mapping

You can add the source CSV file to the mapping as follows:

 

1.On the Insert menu, click Text File.
2.(MapForce Enterprise Edition only) Select the option Use simple processing for standard CSV (delimited) and/or FLF (fixed-length) fields, and click Continue.
3.Click Input File and browse for the following file: <Documents>\Altova\MapForce2019\MapForceExamples\Tutorial\Orders.csv.

 

If the check box Treat empty fields as absent is selected, clear it. When selected, this check box suppresses the empty values and thus will prevent the node function from working. For more information, see Setting the CSV Options.

 

4.Click OK.
5.If prompted to change the component name to "Orders", click the option you prefer (for example, Leave component name unchanged).

 

For more information about CSV components in MapForce, see CSV and Text Files.

 

Step 2: Add the target CSV file to the mapping

You can add the target CSV file to the mapping as follows:

 

1.On the Insert menu, click Text File.
2.(MapForce Enterprise Edition only) Select the option Use simple processing for standard CSV (delimited) and/or FLF (fixed-length) fields, and click Continue.
3.The target file must have the same number of fields as the source one. Therefore, click the Append Field button multiple times to add nine fields.
4.Click OK.

 

Step 3: Draw the mapping connections

At this stage, the mapping contains two components: the source CSV file and the target one. Click the output connector mf_ic_output_connectornext to the Rows item on the source component and drag the cursor to the input connector mf_ic_output_connectorof the Rows item in the target component. When you do this, MapForce may automatically connect all descendant items and create a so-called "Copy-All" connection, depending on your settings. This happens only if the Auto-connect matching children ic-conn-child toolbar option is active. As mentioned previously, node functions are not applied to descendants of "Copy-All" connections. Therefore, the "Copy-All" connection must first be changed to a standard one. To do this, connect Field1 from source to Field1 from target. When prompted, click Replace Connection, and then click Resolve copy-all connection.

 

If the Auto-connect matching children ic-conn-child option is not active, you can create connections between the source and target as follows:

 

1.Connect the Rows item in the source to the Rows item in the target.
2.Right-click the connection, and select Connect Matching Children from the context menu.
3.Clear the Create copy-all connections check box.
4.Click OK.

 

Your mapping should now look as follows:

mff_nodefunc_ex1

 

Step 4: Create the node function

You can create a node function either immediately after data leaves the source, or immediately before it is written to the target. For the purpose of this example, let's create the node function on the input side of the target component; this essentially means "immediately before data is written to the target". For more information, see Choosing When the Function or Default Should Apply.

 

Right-click the Rows item on the target component, and select Node Functions and Defaults | Input Node Functions and Defaults from the context menu. An empty grid appears at the top of the Mapping pane.

mff_nodefunc_ex2

Next, click the Add function mf_ic_add_nodefunc button to the left of the grid. The mapping now displays the function's input ("raw_value") and output ("result").

mff_nodefunc_ex3

As mention before, the function's goal is to convert any empty value into the string "n/a". To achieve this, let's add the following additional components to the mapping:

 

The MapForce built-in function empty. This function returns true if the value supplied as argument is empty; false otherwise. You can drag the function into the mapping from the Libraries window, or just double-click the mapping and type "empty", see also Add a Built-in Function to the Mapping.
A text value "n/a". To add this value, double-click an empty area on the mapping and enter "n/a" surrounded by double quotes, see also Add a Constant to the Mapping.
An If-Else Condition. To add it to the mapping, click the If-Else Condition ( mff_ic_if_else ) toolbar button. For more information about such components, see Example: Returning a Value Conditionally.

 

With the help of these components, design the function as follows:

mff_nodefunc_ex4

The design illustrated above works as follows: first, any input value from the outer mapping enters the function through the raw_value input. The raw value is then supplied as input to the empty function. Then, the If-Else Component evaluates the Boolean result (true or false) returned by the empty function. When the result is true, the constant "n/a" becomes the function's result. When the result is false, the function's raw input value becomes the function's result. The function's result (which is either "n/a" or raw_value) is then returned to the outer mapping.

 

Click mf_ic_exit_door Exit (or press Escape) to exit the function's editing area.

mff_nodefunc_ex5

In the mapping illustrated above, note the following:

 

The text at the top of the window clearly indicates where the function is defined. This is particularly useful in situations where multiple node functions are defined for the same component.
The Apply to option in the grid is set to All descendant items. In this example, this is the intended behavior. That is, all descendant items of Rows must be affected if they qualify. As you can see on the mapping, the left (input) side of the target component displays multiple mf_ic_node_func_applied icons, even though the function was defined only once, for the parent item.
The Data Type option is set to "string". In this example, since we are dealing with text data, this is the intended behavior. It is also the default behavior.
The Edit button lets you go back to the function's definition and change it if necessary. If you don't see this button, click the mf_ic_node_func_defined icon first.

 

Step 5: Run the mapping

To preview the mapping result directly in MapForce, click the Output tab. If any validation errors are encountered, these are displayed in the Messages window, see Validating Mappings. Upon success, the resulting CSV is displayed in the Output pane.

 

You can also execute such mappings on a server machine, with MapForce Server Advanced Edition, in one of the following ways:

 

If you have MapForce Server Advanced Edition standalone license, compile the mapping to an execution file and then copy it to the target machine, see Compiling Mappings to MapForce Server Execution Files.
If you have licensed both FlowForce Server and MapForce Server Advanced Edition, you can deploy the mapping directly to FlowForce Server and configure it to run as a scheduled or on-demand job, see Deploying Mappings to FlowForce Server.

© 2019 Altova GmbH