This tip covers a new task called Modify RowSet which was made available to GoAnywhere Director users on version 4.6.0 and higher. With this new task, you can now manipulate data easily while performing your daily managed file transfers.
By definition, the Modify RowSet task allows you to add, modify, or delete columns from a RowSet that was generated by tasks that read the contents of a file or database (for example, SQL or Read CSV tasks).
When utilizing the Modify RowSet Task, you have the option of modifying the existing columns and their index order, or you can define new columns with an index order of your choosing. Modifying existing columns is useful when the RowSet data needs modifications to meet the desired requirements. Identifying all new columns is preferred when the Input RowSet requires several changes to RowSet data and/or drastic changes to the column Index order.
Let’s take a look at a simple example of how this can work for you.
Our original CSV file (figure 1)
Column 1: Full Description
Column 2: Short Description
Column 3: System Code
Column 4: Identification Number
Column 5: Current Date
Column 6: Original Date
Column 7: Path and name of the Associated TIF file
CSV RowSet Modifications needed:
1. Change Column 1 to say “WIDGETS”.
2. Remove the original Column 2 value and replace with original Column 4 value
3. Change original Column 3 value
a. If Original Column 1 contains the word “Returns” – Column 3 equals “RET”
b. If Original Column 1 does not contain the word “Returns” – Column 3 equals “PUR”
4. Column 4 now becomes the value of the file name only (no path)
To accomplish this transition, use the new Modify RowSet Task in version 4.6.0+
Our Project will look like the Outline in figure 2.
Figure 2
Our first step is to read the current CSV Format into a RowSet variable. Here, we are creating the RowSet variable named oldCSVFile.
Our next step allows us to modify the RowSet just read. We will be using the RowSet variable oldCSVFile (created by the Read CSV task above) as the input to the Modify RowSet task. This task will create the new RowSet Variable “modCSVFile”. Notice in this instance we are setting the “Start with Existing Columns” to “false”. (See Figure 3)
Figure 3
By clicking on the field label, you can review the help associated to this value (see Figure 4)
By default, the Modify RowSet Task will keep the original Column Layout to start with. For this example, we will be rebuilding each column that we need in the final output based on values in the original RowSet columns.
Our first rule from above is that Column 1 must contain the word “WIDGETS”. We get this value by clicking the “Next” button and selecting the “Add New Column” element.
Figure 5
Figure 5 above allows you to identify the Column Index as the number 1 then give the implicit value for the column, “WIDGETS”.
Our Second rule is that Column 2 will now contain the same value as the Original Column 4. Add another column element, then identify the Column Index as the number 2 and assign the value to the original column number 4 as seen in figure 6.
Figure 6
NOTE: Variable Reference in this instance is to COLUMN value, not Specific Field Value. ${oldCSVFile[4]} references any value on all records placed into the 4th column of the Original File.
Our third rule is to create a code for the value of Column 3 based on a value in Column 1. This is accomplished by adding a new 3rd column with an index value of “3”. We can then use functions to create the value needed. In this instance, we are using the “If” and “Contains” functions to test Column 1 for the existence of the word “Returns”. If it does exist, we set Column 3 to the value of “RET” otherwise; we set Column 3 to the value of “PUR”. (See figure 7)
Our final column rule is to change the value of Column 4 to be just the file name only from Column 7. Again, we utilize two functions, “FileInfo” and “String” to retrieve the filename from the original value. (See figure 8)
Our last step is to create a new CSV file using the ${modCSVFile} RowSet variable created from the Modify RowSet task. (See figure 9)
Figure 9
The final modified CSV file, Modified Data.csv, will now have the following format (see figure 10):
More examples of the Modify RowSet task can be found by launching the HTML User Guide located in GoAnywhere Director. Click Help from the Main Menu bar, and then choose Help Center. Click the User Guide link to launch the help system. From within the help system, navigate to Task Reference > Data Translation Tasks > Modify RowSet.
Feel free to contact Linoma Software with any questions or to inquire about our On-Site Professional Services or Training Programs.
Modify RowSet Task
Looking for an example project to get you started?
- Support_Rick Offline
- Support Specialist
- Posts: 590
- Joined: Tue Jul 17, 2012 2:12 pm
- Location: Phoenix, AZ
- Contact:
Modify RowSet Task
Rick Elliott
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696
Lead Solutions Consultant
(402) 944.4242
(800) 949-4696