Import Excel or CSV

Updated on 22 Apr 2025
4 Minutes to read

Print
Share
Dark
Light

Article summary

Did you find this summary helpful?

Thank you for your feedback!

Step Details
Introduced in Version	---
Last Modified in Version	8.21.0
Location	Data > Excel & CSV

The Import Excel or CSV step Imports/loads data from CSV or Excel files. In turn, this imported data is then converted into the user-determined Output Data Type. The step operates off the assumption that the imported data has properties corresponding to the File Columns. The Input File Type is selected via the Property of the same name. Users then define which Row in the spreadsheet the step reads from via the Start On Row Input. Information from the File's Columns is determined via the Number of Columns Input and by Mapping Properties for each. Due to its effectiveness and speed, this step functions well for importing large amounts of CSV data.

Password Protection

Sheets with password protection will fail in every version before v8.21 or v9.2.

Properties

Inputs

Property	Description	Data Type
File Data	File to Import.	File Data

Input Data

Property	Description	Data Type
Input File Type	Defines the type of file used as an input to the step. Currently, the options are Csv, Excel_xls, Excel_xlsx and AutoSelect.	---
Start on Row	Define which Row is the first row to begin importing data from.	Int32
CSV Delimiter	Determines which delimiter is used in the CSV files.	---
Ignore Quotes (Do not treat as escaped)	Ignores quotes within the data that is imported.	Boolean
Input File Encoding	Set which encoding type the File has, ASCII or UTF8.	---
Number of Columns	The number of columns within the Excel or CSV file.	Int32
Worksheet Index	This is the index of a particular sheet in an Excel file. This is only available for Excel_xls, Excel_xlsx, and AutoSelect options under Input File Type .	Int32
Document Password (only in v8.21+ and v9.2+)	For password protected files, the password will need to be supplied in this field. This is only available for Excel_xls, Excel_xlsx, and AutoSelect options under Input File Type.	String

Import Raw Lines

Property	Description	Data Type
Import Raw Lines	Import the data within the file as raw lines.	Boolean
Into Property	Pick one of the properties on the datatype being mapped to and have that property take in all the values of the excel sheet as a comma-separated list.	---

Output Data

Property		Description	Data Type
Output Data Type		Define the data type to import the Excel or CSV data to.	---
	Mapping	Define which columns equate to which data fields.	---

Error Handling

By default each option under Error Handling is set to Add Outcome which provides another path after the Step in the Flow.

Property	Description	Data Type
Extra Columns in Input	Will provide an error if extra columns are found in the input (Add Outcome: provides an outcome path, Throw Exception will throw an exception on reaching error, Ignore and Continue: will add an outcome path and ignore the error.)	---
Less Columns in Input	Will provide an error if the columns found are less than expected (Add Outcome: provides an outcome path, Throw Exception will throw an exception on reaching error, Ignore and Continue: add an outcome path and ignore the error.)	---
Value Conversion in Input	Will send an error if a value can not be converted (Add Outcome: provides an outcome path, Throw Exception will throw an exception on reaching error, Ignore and Continue: add an outcome path and ignore the error.)	---

Outputs

Property		Description	Data Type
Imported Data		Returns the data that was successfully imported	List of [Data Type]
Extra Columns Error
	Actual Columns Number	Returns the number of columns	Int32
	Input Line	Returns the input line or error	Int32
	Line Number	Returns the line number of the error	Int32
Less Columns Error
	Actual Columns Number	Returns the number of columns	Int32
	Input Line	Returns the input line or error	Int32
	Line Number	Returns the line number of the error	Int32
Value Conversion Error
	Column Number	Returns the column number of the error	Int32
	Error Message	Returns the error message	String
	Input Line	Returns the input line or error	Int32
	Line Number	Returns the line number of the error	Int32
	Source Value	Returns what the source value is	String
	Target Type	Returns the expected data type	String

Example

The following example will create a Flow Structure named "CorpLocations", then import the below Excel file and map the rows/columns to the Flow Structure's data members.

The spreadsheet used in this example:

Corporate Locations.xlsx

In a Project, click CREATE DATATYPES/INTEGRATIONS in the Global Action Bar. Select Flow Structure and click CREATE.
Name the structure "CorpLocations" and give it the following String [Text] data members: "City", "State", "NumOfEmployees". Click SAVE.
Back in the Designer Folder, click CREATE FLOW from the Global Action Bar and select Flow. Name the Flow and click CREATE.
Click the Done path of the Start step. Navigate to All Steps [Catalog] > Data > Excel and CSV and select the Import Excel or CSV. Click ADD.
On the Properties panel, set the Input File Type field to Excel_xls, Start On Row field to "2", and Number of Columns to "3".
Leave Worksheet Index blank as the file being used only has one worksheet.
In the OUTPUT DATA category, select CorpLocations in the Output DataType dropdown.
A new sub-section called Mapping appears within the OUTPUT DATA category. Due to "3" being typed in the Number of Columns, three invalid columns appear.
Select the pencil icon for the first column. In the Edit Mapping window, under the PROPERTY category, select City in the Name dropdown menu. Click OK.
Configure the two remaining columns, State and NumOfEmployees in the same way.
Under the INPUTS category, select Constant as the mapping type for the File Data field. Browse to the desired file and click Open to load the file as input.
Connect the outcome paths of the Flow to the End step. Click Save to save changes to the Flow.

Debug

Click Debug in the top action panel.
Click START DEBUGGING. After the debugger runs, click the Import Excel or CSV step, select Execution 1, and select View Input/Output Data. In the window, the Excel file's data was mapped to the proper Flow Structure properties.