Creating Excel Data from Data ObjectsLast Updated: 07/19/2016 Introduced in Verision: 2.0 |
Data objects can be exported to CSV format within a flow by using the Custom Merge [NVelocity] and Create Text File components. The Custom Merge [NVelocty] component can be found in the Toolbox tab, under the Data > Text category. The Create Text File component can be found in the Integration > File category.
Example
In our example flow, we will create a spreadsheet containing the VehiclePassenger objects within a Car folder. This example assumes that we’ve already designed the form with which we’ll download our spreadsheet.
We will begin by navigating to the behavior actions folder of our Car folder at System > Common Data Elements > Folder Behavior Actions > Transportation.CarFolderBehavior, and selecting the Create Flow button.
In the resulting New Flow pop-up, we will name our flow “Export Passengers” and click OK to open it in the Flow Designer.
To retrieve all of our Car’s passengers, we will use the Get All VehiclePassenger by Folder Id component. We will find it in Flow Designer’s start-up window, under the category Integrations > All Integrations > Folder Entity Data Structures > VehiclePassenger. Click Add to add this step to our workspace.
In the Mapping Editor, we will map FolderId to Entity Folder Id and rename the Results output to “CarPassengers”.
To store our CSV data, we will first create a String variable called “CSVData”. We will find the Create or Copy Data component in the Toolbox tab, under the Data category.
In the Properties tab, under the Data Definitions category, we will click the Add button and define our CSVData string.
In the Mapping Editor, we will define the first row of our CSV file which will consist of column headings.
Next, we need to iterate through our VehiclePassenger objects and create CSV rows from them. We will find the ForEach Step component in the Toolbox tab, under the Flow Management category.
In the Collection category of the Properties tab, we will open the Type selector and select VehiclePassenger.
In the Mapping Editor, we will map CarPassengers to Collection and rename the Item output to “CarPassenger”.
Next, we will create a CSV row from the properties of each CarPassenger. We will find the Custom Merge [NVelocity] component in the Toolbox tab, under the Data > Text category.
In the Merge Setup section of the Properties tab, we will define the formatting and contents of our file. Because we want to aggregate our rows together, our first line will contain all of the CSV data defined in earlier iterations. Each subsequent row will consist of the CarPassenger’s First Name, Last Name and Seat Position, separated by commas.
Once we’ve defined our template, the NVelocity component will automatically generate inputs for each variable we’ve defined. In the Mapping Editor, we will map CSVData to CSVData. Then we will map the FirstName, LastName and SeatPosition properties of CarPassenger to the respective inputs. We will also rename the MergedString output to “CSVData”.
Once all CarPassenger entities have been turned into rows, we need to create the final CSV file. We will find the Create Text File component in the Toolbox tab, under the Integration > File category.
In the Mapping Editor, we will define the location and name of our CSV file as “C:\temp\Passengers.csv” in the fileName property and we will map CSVData to data.
After creating the CSV file, we will need to load it back into our flow so that the user can download it via a form. We will find the Load File component in the Toolbox tab, under the Integration > File category.
In the Mapping Editor, we will define fileName as “C:\temp\Passengers.csv” and rename Output to “File”.
Next, we will place the form with which our user will download the finished CSV file. We will find [Form] Download Passengers CSV Form in the Forms [Interaction] > [Root Folder] > Designer Entities category.
In the Mapping Editor, we will map File to FileForDownload.
Once we’ve connected our remaining outcomes, our flow will be complete. We will save it and close the Flow Designer.
To test our new action, we will navigate to a Car folder and select the Export Passengers action.
In the resulting form, we will select the Download File button and download our new CSV.
When we open our file in Excel, we will see the details of our VehiclePassenger entities.
