Creating Data Objects from Excel Data

Last Updated: 07/15/2016 Introduced in Verision: 2.0

The Import Excel or CSV component iterates through the rows of a spreadsheet and attempts to create a data object from that row. The Import Excel or CSV component can be found in the Toolbox tab, under the category Data > List > Excel and CSV.

Example

In our example flow, we will create CarPassenger objects from a spreadsheet. This example assumes that the required forms have already been designed. Our spreadsheets will be comma-delimited and contain three columns: first name, last name and seat position.

editExcelFileResult

 

Because CarPassenger objects are folder entities, some properties (such as the ID of the Car folder that contains the CarPassengers) will not typically be supplied in a spreadsheet. Rather than creating CarPassenger entities directly from our spreadsheet, we will instead create intermediary data objects that are not saved in the database. These intermediate data objects will then be used to create our CarPassenger entities.

For our intermediate data object, we will begin in the System > Common Data Elements > Data Structures folder, where we will select the Add > Defined Data Structure [Advanced] action.

addDefinedDataStructure

 

In the resulting Add DefinedDataStructure pop-up, we will give our intermediate data object three properties: FirstName, LastName and SeatPosition. In the Advanced section, we will select NotDatabaseStored in the Storage Option dropdown, and then we will save our data structure.

defineStructure

 

 

Next, we will create the action to import CarPassengers. We will navigate to our Car folder’s behavior actions folder at System > Common Data Elements > Folder Behavior Actions > Transportation.CarBehaviorFolder. We will click the Create Flow button.

createAction

 

We will name this new flow “Import Passengers” and click OK to open it in the Flow Designer.

nameFlow

 

 

We will begin by placing the form with which the user will upload their CSV file. We will find [Form] Select Excel File Form in the Flow Designer’s start-up window, under the category Flows, Rules, Forms and Reports > Forms [Interaction] > [Current Folder]. Select it and click Add to add it to the workspace.

 

 flowStarts

Next, we will place the form with which the user will see the contents of the CSV file they uploaded and make any last-minute changes. We will find [Form] Edit Excel File Form in the Toolbox tab, under the category [Forms] Interaction > [Root Folder] > Designer Entities.

dragEditForm

 

In the Mapping Editor, we will map UploadedFile to CSVData.

mappingToEditFile

 

 

 

Next, we will transform our CSV data into CarPassengerIntermediate objects. We will find the Import Excel or CSV component in the Toolbox tab, under the category Data > List > Excel and CSV.

dragImportExcel

 

In the Properties panel, we will open the Output Data Type selector and select CarPassengerIntermediate.

pickTypeForImportOutput

 

In the Input Data section, we will select an Input File Type of CSV. We will start on row 1, use commas as the CSV delimiter, and set the expected Number of Columns to “3”.

input3Columns

 

Under the Output Data section, a Mapping section will appear. It will contain three unmapped variables – one for each column. We will select the first column and click the Edit button. In the resulting Edit object pop-up, under the Property section, we will select FirstName in the Name dropdown and click Save.

mapFirstNameColumn

 

In the same way as we just did, we will map the second and third columns to the LastName and SeatPosition properties.

mapAllCols

 

In the Mapping Editor, we will map CSVData to File Data.

mapCSVDataToImportFile

 

To handle the three most common errors of importing CSV data, the Import Excel or CSV data component has three error outcomes: Less Columns Error, Extra Columns Error, and Value Conversion Error. Each outcome contains variables we can use to create descriptive error messages. To create these error messages, we will use the Create or Copy Data component, which can be found in the Toolbox tab, under the Data category. First, we will handle the Less Columns Error outcome.

dragLessColumnsErrorCreate

 

We will define a String called “ErrorMessage”.

defineErrorMessageString

 

In the Mapping Editor, we will give ErrorMessage a mapping type of Text Merge.HTML. In the Merge Text Editor, we will create our message: “Too few columns were found in row [Line Number]. Actual number of columns found: [Actual Columns Number].” We will also color this message red, and then click OK to close the Merge Text Editor.

mappingToLessColumns

 

We will create our other error messages in the same way. For the Extra Columns Error outcome, our error message will be: “Extra columns found in line [Line Number]. Number of columns found: [Actual Columns Number].”

extraColumnsMapping

 

For the Value Conversion Error outcome, our error message will be: “Value conversion error. Unable to convert “[Source Value]” to [Target Type].”

mappingForValueConversionError

 

To display our error messages on our form, we will select [Form] Edit Excel File Form and, in the Mapping Editor, map ErrorMessage to ErrorMessage.

mapErrorToForm

 

Next, we will iterate through the CarPassengerIntermediate entities that we’ve created and, for each one, create a CarPassenger. We will find the ForEach Step component in the Toolbox tab, under the Flow Management category.

dragForEach

 

In the Collection section, we will open the Type selector and select CarPassengerIntermediate.

collectionTypeForEach

 

In the Mapping Editor, we will map Imported Data to Collection.

mapImportedDataToCollection

 

Next we will create our CarPassenger objects. We will find the Create CarPassenger component in the Toolbox tab, under the category Integration > Folder Entity Data Structures > CarPassenger.

dragCreatePassenger

 

In the Mapping Editor, we will map FolderId to FolderId and EntityFolderId. We will map Item.FirstName, Item.LastName and Item.SeatPosition to their respective properties of the new CarPassenger, and we will also change the mapping type of EntityName to Text Merge.Plain.

mappingToCreatePassenger

 

In the Merge Text Editor, we will map our entity’s name as “[Item.FirstName] [Item.Last Name]”.

mergeTextEntityName

 

 

After creating our new CarPassenger, we want to return control to the ForEach Step. When all of our CarPassengerIntermediate objects have been processed, our flow will end.

flowCompleted

 

This completes our flow so we will save it and close the Flow Designer.

saveFlow

 

To test our new flow, we will create a CSV spreadsheet containing the properties of four CarPassengers.

29.-spreadsheet.png

Back in the portal, we will navigate to a Car folder and select the Import Passengers action.

importPassengersAction

 

In the resulting Select Excel File form, we will select our CSV file and click Upload.

selectFileResult

 

In the Edit Excel File form, we will see the contents of our file. We will select the Create Passengers button to turn these rows into CarPassenger entities.

editExcelFileResult

 

The CarPassenger entities will appear in our Car folder, with the properties that were defined in our CSV file.

finalResult

 

Additional Resources