Creating Data Objects from Excel DataLast 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.
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.
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.
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.
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.
We will name this new flow “Import Passengers” and click OK to open it in the Flow Designer.
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.
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.
In the Mapping Editor, we will map UploadedFile to CSVData.
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.
In the Properties panel, we will open the Output Data Type selector and select CarPassengerIntermediate.
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”.
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.
In the same way as we just did, we will map the second and third columns to the LastName and SeatPosition properties.
In the Mapping Editor, we will map CSVData to File Data.
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.
We will define a String called “ErrorMessage”.
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.
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].”
For the Value Conversion Error outcome, our error message will be: “Value conversion error. Unable to convert “[Source Value]” to [Target Type].”
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.
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.
In the Collection section, we will open the Type selector and select CarPassengerIntermediate.
In the Mapping Editor, we will map Imported Data to Collection.
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.
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.
In the Merge Text Editor, we will map our entity’s name as “[Item.FirstName] [Item.Last Name]”.
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.
This completes our flow so we will save it and close the Flow Designer.
To test our new flow, we will create a CSV spreadsheet containing the properties of four CarPassengers.
Back in the portal, we will navigate to a Car folder and select the Import Passengers action.
In the resulting Select Excel File form, we will select our CSV file and click Upload.
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.
The CarPassenger entities will appear in our Car folder, with the properties that were defined in our CSV file.