Create Excel Data from Object Data
  • Updated on 10 Oct 2018
  • 7 minutes to read
  • Print
  • Dark
    Light

Create Excel Data from Object Data

  • Print
  • Dark
    Light

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. 
vehiclepassenger.jpg

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 toCanvas 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 locateButton and download file , drag and drop components on the form.  https://documentation.decisions.com/creating-your-first-form/. 
Formbasic.jpg

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 blueAction Panel across the bottom of the Designer Studio, choose your flow type, select Create.
cflow.jpg

Name your flow and select Create.
name-flow.jpg

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

For Loop Step 1
In the Steps Tab locateFlow 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. 
for-loop-step-1.jpg

Generate Random First Name Step
In the Steps Tab locateData>Test>TestData, drag and drop step into Designer Studio. Set Settings under Common Properties.
generate-FN.jpg

Generate Random Surname Step
In the Steps Tab locate Data> Text> TestData . Set Settings under Common Properties.
lN.jpg

Get Random Int with Min and Max
In the Steps Tab locateData>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.
MinMax.jpg

Set up Process Folder Step
In theSteps Tab locate Flow Management>Data, drag and drop step into Designer Studio . Under Common, Properties name your folder. Under Parent Folder select your S ource from the drop-down menu. UnderParent Folder, pick the folder we created earlier, called TheProcessFolder. The Process Folder is where your Vehicle Passenger information will be stored.
specify1.jpg

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 check the Use Extension Data box. 
processfolder1.jpg

Under Inputs, select Extension Data and choose Build Data .
build-data.jpg

Set all toIgnore except for FirstName, LastName, andSeatPosition. Choose Select From Flow and pick the corresponding names from the flow. 
First-Name-Last-Name-Seat-Position.jpg

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.
create-Excel-data-for-example-flow.jpg

Input and output Mapping for the Setup Process Folder.
mapping.jpg

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 s elect Create Flow from the blue Action Panel across the bottom of the Designer Studio, choose the flow type, select Create.
cflow.jpg{height ="527" width="822"}
Name the flow and select Create.
Create-File-Flow.jpg{height ="491" width="819"}

In the Flow Designer begin by adding Steps from the Steps Tab on the right panel.
empy-flow.jpg

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 .
Fetch-All.jpg

DragFetch All Step and configure the properties. Under Common Properties>Outputs rename Output and set the value to Car Passengers.
fAlldata.jpg

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.
csvdata.jpg

In theProperties Tab under Common Properties change Name toCSVData. Under Inputs and CSVData choose Constant and set the value toFirst Name, Last Name, Seat Position. Under Outputs, CSVData choose Rename and set the value toCSVData.
CSVexcel-1.jpg

ForEach Step
In theSteps Tab locate Create Data Step. Choose, Flow Management>ForEach Step, drag and drop step into Designer Studio . In the Properties Tab under Data select the drop down underType and choose the Flow Execution Data Structure Folder. 
vp-example.jpg

UnderInputs> Collection , ChooseSelect From Flow. UnderOutputs>Item choose Rename and set the value as Car Passenger
ForEAch1.jpg

Custom Merge [NVelocity] Step
In theSteps Tab locateCustom Merge [NVelocity]. Choose,Data>Text>Custom Merge [NVelocity], drag and drop step into Designer Studio . In theProperties Tab under Common Properties and under Merge Setup>Template, create custom variables using the $ symbol to make these items dynamic.  
Merge-Setup.jpg

Under Inputs and CSVData choose Select From Flow, Pick CSVData . UnderFirst Name choose Select From FlowPick Car Passenger and select First Name. UnderLast Name choose Select From FlowPick Car Passenger and select Last Name . UnderSeat Position choose Select From FlowPick Car Passenger and select Seat Position .  Under Output and under Merge String choose Rename and set the value toCSVData.
input-output.jpg

Create Text File
In theSteps Tab locate Create Text File. Choose, Integration>File>Create Text File, drag and drop step into Designer Studio. Inthe properties tab look u nder Inputs and data choose Select From Flow and select CSVData. Under file Name choose Constant and set the value toPassengers.csv.
textfile.jpg

Load File Step
In the Steps Tab locate Load File, choose,Integration>File>Load File, drag and drop step into Designer Studio. UnderInputs and file Name chooseConstant and set value to Passengers.csv. UnderOutputs and Output choose Rename and name it NewFile.
loadfile.jpg

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

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

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 selectRun Flow. At the end of the flow, your Download File Form will pop up and you can select thedownload button to download and view your new CSV file. Select the Done button to end the flow.
image-1.png

Select Done and view the CSV spreadsheet. 
image-2.png

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

Was this article helpful?