Create Excel Data from Object Data

Last Updated: 10/10/2018 Introduced in Version:

Data objects can be exported to CSV format within a flow by using the Custom Merge [NVelocity] and Create Text File components. Part 1 of this example will first show how to create data for an Excel and CSV spreadsheet if existing data has not previously been created. Part 2 of this example shows how data objects can be exported to CSV and Excel format within a flow by using the Custom Merge [NVelocity] and Create Text File components.

Example

In our example flow, we will create Vehicle Passenger objects from the created spreadsheet. Our spreadsheet will contain the Vehicle Passenger objects that are stored in a folder and contain three columns: First Name, Last Name and Seat Position.

 

PART 1

CREATE DATA

This flow is going to generate the data, (vehicle passenger information) that will be used for the Excel and CSV spreadsheet, the reason for creating this spreadsheet is for our flow to have information to pull from. 

In the Designer Studio select CREATE DATATYPES/INTEGRATION from the blue action panel at the bottom of the Designer Studio, choose Flow Execution Extension type and select Create. Name your Flow Execution Extension and its’ data types, now select Save. This Data Structure includes the Vehicle Passengers First Name, Last Name and Seat Position. 

 

CREATE FORM

Create a basic form in the Flow Designer and name it Download File Form. This gives us a way to be able to view the form that is created at the end of the flow. Set Form to Canvas in the Properties Panel on the right.  The form should have a button component and a download file component. In Form Controls on the right panel locate Button and download file, drag and drop components on the form.  https://documentation.decisions.com/creating-your-first-form/. 

 

CREATE FOLDER

This folder is created to store your example data. Right click on your project folder (this is the folder that you’re creating this project in. The folder could be My Apps, or it may be a folder you created just for this project), hover over Add, and select Folder (this is NOT a Designer Folder). Name this folder, TheProcessFolder.

 

Create a Flow that will generate the data for your CSV Spreadsheet, using the Steps in Decisions. Select Create Flow from the blue Action Panel across the bottom of the Designer Studio, choose your flow type, select Create.

Name your flow and select Create.

 

In the Designer Studio begin by selecting the Steps Tab on the right panel. 

 

For Loop Step 1

In the Steps Tab locate Flow Management> Utilities> For loop Step, drag and drop step into Designer Studio. Set settings under Common Properties. We have used 5 as the number of iterations to create five passengers as example data. 

 

Generate Random First Name Step

In the Steps Tab locate Data>Test>TestData, drag and drop step into Designer Studio. Set Settings under Common Properties.

 

Generate Random Surname Step

In the Steps Tab locate Data> Text> TestData. Set Settings under Common Properties.

 

Get Random Int with Min and Max

In the Steps Tab locate Data>Numbers>Get Random Int with Min-Max, drag and drop step into Designer Studio. Set settings under Common Properties. This step will generate random seat positions with random integers of a min 1 and max 5.

 

Set up Process Folder Step

In the Steps Tab locate Flow Management>Data, drag and drop step into Designer StudioUnder Common, Properties name your folder. Under Parent Folder select your Source from the drop-down menu. Under Parent Folder, pick the folder we created earlier, called TheProcessFolder. The Process Folder is where your Vehicle Passenger information will be stored.

Continue to scroll down the Properties Tab.  Under Make Unique Select No in the drop-down menu. Under Flow Prefix check the Include Flow Run Id in the Name box and check the Use Extension Data box. 

Under Inputs, select Extension Data and choose Build Data.

Set all to Ignore except for FirstName, LastName, and SeatPosition. Choose Select From Flow and pick the corresponding names from the flow. 

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

Input and output Mapping for the Setup Process Folder.

Now that the flow is complete, we need to run the flow. You can do this either by clicking Debug in the flow designer, or you can close the flow designer, right click on Flow 1, and select Run Flow. You will now have 5 items in your process folder that we will put into our CSV file in the next part.

 

 

Part 2

Create Data Objects from Excel Data

Now that the data had been created, start by creating a flow that will display all of the data onto a CSV spreadsheet. In the Designer Studio select Create Flow from the blue Action Panel across the bottom of the Designer Studio, choose the flow type, select Create.

Name the flow and select Create.

 

 In the Flow Designer begin by adding Steps from the Steps Tab on the right panel.

Fetch All Step

In the Steps Tab locate the Fetch All Step from your Flow Execution Data Structure. Choose Steps>User Defined Types>Flow Data Extensions>Vehicle Passenger>Fetch All (Car passengers), drag and drop step into Designer Studio.

Drag Fetch All Step and configure the properties. Under Common Properties>Outputs rename Output and set the value to Car Passengers.

 

Create Data Step 

In the Steps Tab locate Create Data Step. Choose, Data>Create Data drag and drop step into Designer Studio. Under Data Definitions select show editor and create a data type for your CSVData Step.

 

In the Properties Tab under Common Properties change Name to CSVData. Under Inputs and CSVData choose Constant and set the value to First Name, Last Name, Seat Position. Under Outputs, CSVData choose Rename and set the value to CSVData.

 

ForEach Step

In the Steps Tab locate Create Data Step. Choose, Flow Management>ForEach Step, drag and drop step into Designer StudioIn the Properties Tab under Data select the drop down under Type and choose the Flow Execution Data Structure Folder. 

Under Inputs> Collection, Choose Select From Flow. Under Outputs>Item choose Rename and set the value as Car Passenger

Custom Merge [NVelocity] Step

In the Steps Tab locate Custom Merge [NVelocity]. Choose, Data>Text>Custom Merge [NVelocity], drag and drop step into Designer StudioIn the Properties Tab under Common Properties and under Merge Setup>Template, create custom variables using the $ symbol to make these items dynamic.  

Under Inputs and CSVData choose Select From Flow, Pick CSVData. Under First Name choose Select From FlowPick Car Passenger and select First Name. Under Last Name choose Select From FlowPick Car Passenger and select Last Name. Under Seat Position choose Select From FlowPick Car Passenger and select Seat Position.  Under Output and under Merge String choose Rename and set the value to CSVData.

Create Text File

In the Steps Tab locate Create Text File. Choose, Integration>File>Create Text File, drag and drop step into Designer Studio.  In the properties tab look under Inputs and data choose Select From Flow and select CSVData. Under file Name choose Constant and set the value to Passengers.csv.

Load File Step

In the Steps Tab locate Load File, choose, Integration>File>Load File, drag and drop step into Designer Studio. Under Inputs and file Name choose Constant and set value to Passengers.csv. Under Outputs and Output choose Rename and name it NewFile.

Show Form

In the Steps Tab locate Show Form, choose, Forms/Interaction>[Pick or Create Form], drag and drop step into Designer Studio. Select Pick or Create Form Step choose pick and select a previously made custom form. Under Inputs and New File choose Select From Flow and select New File.

When your flow is complete it should have the following steps. The done pathway from the Custom Merge [NVerlocity] Step should be mapped back to the ForEach step. The ForEach Step’s done pathway should connect to the Create Text File. 

 

Now that the flow is complete, we need to run the flow. You can do this either by selecting Debug in the flow designer, or you can close the Flow Designer, right click on Create File Flow, and select Run Flow. At the end of the flow, your Download File Form will pop up and you can select the download button to download and view your new CSV file. Select the Done button to end the flow.

Select Done and view the CSV spreadsheet. 

 

For more information regarding Excel and CSV spreadsheets see attached link: https://documentation.decisions.com/?s=Excel

Additional Resources