GoAnywhere makes it easy to modify data by adding or removing columns, updating information, or dividing one column into multiple. This can all be accomplished with the Modify RowSet Task.
Let’s start off with an example on adjusting generic column names. This demonstration will also split column 4, “fullname,” which contains both a first and a last name in one field. The goal is to take this information and create a column for a first name and a column for a second name, dropping the original fullname column.
The Project
For this example, the Project is called “Modify RowSet – ExcelSplitandTransfer.” The first Task will connect to an SFTP Resource and grab a file called testdata.xlsx. The Project is then going to read that testdata file and put the records into a RowSet Variable called ${myRowSet}.
At this point, the Project will use that Task called Modify RowSet, which will modify the records that were just read in. Modify RowSet is found under the Data Translation section of the Component Library.
For additional information about using this Task, click on the question mark in the top-right corner to be taken to the relevant page in the User Guide. This page contains field definitions and examples for using Modify RowSet.
The Task will start off with the Input Variable ${myRowSet}, and once modified, the data will be put into another RowSet Variable called ${myModifiedRowSet}. The original columns will not be needed in this example as it will use a template and define each column, so “Start with Existing Columns” is set to false.
The Columns
Beginning with the first name, a column is created called “FirstName MI,” representing index number 1. Within the Value is where multiple Functions will be used to modify the original record. A Substring Function is done on column 4, starting at a position just past the comma, going to the end of the string.
The next column is for the LastName. This also uses a Substring, starting at position 4, ending at one position before the comma.
The Address comes from column number 1 of the original file. City, State, Phone No., and birth date are all defined similarly.
Using the New RowSet
The next step is the Write Excel Task. This will take in the ${myModifiedRowSet} Variable, which contains all the modified records, and write it out to finalexcelfile.xlsx. In this example, the Task uses a template, which defines some headings for the columns ahead of time. On the Data Options, the data will start at row number 2. An Output File Variable is also defined called ${newModFile}. This is used later as the input for the PGP Encrypt Task.
The “PGP Encrypt and Archive” Task encrypts the file, which is then sent to a separate SFTP server.
The Final Results
Shown below are the results of the Project. The first window is the original file, while the window down below is the modified result from the Project. The new file is formatted with the template specified in the Write Excel Task, and it contains the new columns and headings defined by the Modify RowSet Task.
