Create Excel Data from Object Data

Overview

Data objects can be exported to CSV format within a Flow using the Custom Merge [NVelocity] and  Create Text File components. This example will first show how to create data for Excel and CSV if existing data has not previously been created. This example will also show how data objects can be exported to CSV and Excel format within a Flow using the Custom Merge [NVelocity] and Create Text File components.

Example

In this example, create Vehicle Passenger objects from the created spreadsheet. The spreadsheet will contain the Vehicle Passenger objects stored in a folder and contain three columns: First Name, Last Name, and Seat Position. This Flow is going to generate the data that will be used for the Excel and CSV spreadsheet.

Generate Data for Example

Create Flow Execution Extension

In a Designer Folder, select 'Create Datatypes/Integration' from the bottom action panel at the bottom of the Designer Studio. Choose Flow Execution Extension type, and select Create. 

Name the Flow Execution Extension and the data types. This Data Structure includes Vehicle Passengers' FirstName, LastName, and SeatPosition. Select 'Save'.

Create Folder

This folder is created to store the data. Right-click on the Designer Folder created for this project, select Add and choose Folder. Name this folder, The Process Folder.

Create Flow

Create a Flow that will generate the data for your CSV Spreadsheet using the Steps in Decisions. 

Select 'Create Flow' in the Designer Folder from the bottom action panel. Name the Flow and select 'Create'.

In the Flow Designer, open the Steps panel and add a ForLoop step. This step is located in Flow Management. In the Properties panel, under Inputs > No of Iterations, type "5" to create five passengers as example data.

On the 'Next' outcome path, add a Generate Random First Name step located in the Data > Text > TestData category in the Steps panel.

On the 'Done' outcome path from the Generate Random First Name step, add a Generate Random Surname Name step located in the Data > Text > TestData category in the Steps panel.

On the 'Done' outcome path from the Generate Random Surname step, add a Get Random Int with Min-Max step located in the Data > Numbers category in the Steps panel. This step will generate random seat positions with random integers of a min 1 and max 5.

On the 'Done' outcome path from the Get Random Int with Min-Max step, add a Setup Process Folder step located Flow Management > Data.

In the Properties panel, under the Parent Folder section, select Specify from the drop-down menu. For Parent Folder, pick the folder created earlier, named The Process Folder. This folder is where the Vehicle Passenger information will be stored.
Continue to scroll down the Properties Tab. Under Make Unique, Select No in the drop-down menu. UnderFlow Prefix, check the Include Flow Run Id in the Name box and the Use Extension Data box.

Under Inputs, select Extension Data and choose Build Data. Find FirstName, LastName, and SeatPosition. Choose Select From Flow and pick the corresponding names from the Flow. For the Folder Name, select Merge Text Plain as the mapping type, then select 'Show Editor'.

Debug: Generate Data for Example

Once all inputs are set, connect the done path back to the ForLoop Step in the Flow. After all, the steps are in the Flow Designer the pathways and mapping should look like the below-attached images.

Now that the Flow is complete, run the Flow. Close the Flow Designer, right-click on Generate Data for CSV, and select Run Flow. Navigate to the Process Folder; there are 5 items in the process folder that will be put into the CSV file in the next part.

Create Data Objects from Excel Data

Now that the data had been created, create a Flow that will display all of the data onto a CSV spreadsheet. 

In the Designer Folder, select 'Create Flow' from the bottom action panel and select 'Create'. Name the Flow and select 'Create'.

In the Steps panel, locate the Fetch All Step from the Flow Execution Data Structure. Navigate to User Defined Types > Flow Data Extensions > Vehicle Passengers > Fetch All, drag and drop the step into the Flow Designer.

In the Steps panel, locate Create Data Step. Choose Data > Create Data drag and drop step into Flow Designer. 

In the Data Definitions dialog, the name "CSVData" then under Inputs and leave as a Constant and set the value to "First Name, Last Name, Seat Position".

In the Steps panel, locate the ForEach step in the Flow Management category, and drag and drop the step into Flow Designer. 

In the Properties panel, under Data, select the drop-down under Type and choose the VehiclePassengers Flow Execution Data Structure. Under Inputs > Collection, choose the FetchAll_Output. Under Outputs, Rename the Item and set the value as Vehicle Passenger.

In the Steps panel, locate Custom Merge [NVelocity] in the Data > Text category and drag the step into the Flow Designer.

 In the Properties panel under Inputs >  Merge Setup >Template, create custom variables using the $ symbol to make these items dynamic.

Under Inputs > CSVData, choose Select From Flow, and pick CSVData. 

Under the First Name input, choose Select From Flow, and pick VehiclePassenger.FirstName. Under the Last Name input, choose Select From Flow, and pick VehiclePassenger.LastName. Under the Seat Position input, choose Select From Flow, and pick VehiclePassenger.SeatPosition.

Under Output > Merge String, Rename the value as CSVData.

In the Steps panel, locate the Create Text File step in the Integration > File category, and drag and drop the step into the Flow Designer.

In the Properties panel, under Inputs > data, choose CSVData from the Flow. Under file Name, choose Constant and set the value to PassengersList.csv.

In the Steps panel, locate the Load File step in the Integration > File category. Drag and drop the step into the Flow Designer. Under Inputs > file Name, choose Constant as the mapping type and set the value to PassengersList.csv. Under Outputs > Output, Rename the value as NewFile.

In the Steps panel, locate the Show Form step under Forms/Interaction>[Pick or Create Form]. Drag and drop this step into Flow Designer. Choose to Create a Form, name the Form "Download File Form", and select 'Create' to open the Form Designer. 

 The Form with designed with a Button component and a Download File component. This will allow the user to download and view the Form created at the end of the Flow. 

Under Inputs > New File, choose Select From Flow and select New File.

Debug: Create Data Objects from Excel/CSV Data

Now that the Flow is complete select Debug in the Flow Designer. When the Flow runs, the Download File Form will show; select the Download button to download and view the new CSV file. Select the Done button to end the Flow. Open the downloaded CSV file to view the data.