You are here:
Use CSV Files as Data Sources
In the Data 360 runtime, you can use CSV files to upload large volumes of data to your org. In definitions where the runtime object type is either Data Lake Object or Data Model Object, you can upload CSV files to definitions as data sources. Configure the definition to suit your requirements, and then write back the results to either a data lake object (DLO), a data model object (DMO), or a Salesforce object.
Required Editions
| View supported editions. |
| User Permissions Needed | |
|---|---|
| To configure files as data sources: | View permissions information. |
Prepare Data Source Files
Definitions can only process data from CSV files when the files are structured in a particular way. Here's are a few points to consider when preparing CSV files:
- The file must contain a column that acts as the primary key for the rows.
The primary key helps the definition identify each row uniquely and helps you transform the data from the file in nodes like Filter and Group and Aggregate.
- The file must only consist of columns are of data types supported by Data 360.
- The file's size must be up to 75 MB.
Here's the columns and rows of a sample CSV file:
| ID | Name | Industry | Size | CreatedDate | LastActivityDate |
|---|---|---|---|---|---|
| 1 | Ursa Major Solar | Renewable Energy | 100 | 2024-05-15 | 2024-08-15T14:30:00Z |
| 2 | Cloud Kicks | Retail | 200 | 2024-05-16 | 2024-09-16T09:15:23Z |
| 3 | Northern Trail Outfitters | Retail | 5,000 | 2024-05-16 | 2024-05-17T18:00:00-05:00 |
| 4 | The Hive Foundation | Non Profit | 50 | 2024-05-18 | 2024-05-20T12:05:45Z |
| 5 | Main Stage Analytics | Consulting | 80 | 2024-12-22 | 2024-12-27T07:22:10-08:00 |
To get definitions up and running quickly, you can create OpenAPI specification-based YAML files with the metadata of the CSV files that you want to use as data sources. The YAML files help the definition understand the structure of the CSV files. The YAML files consist of the metadata and the CSV files contain the actual data that must be transformed. Here are a few points to consider when preparing the YAML file:
- The column names that you specify in the YAML file must exactly match the names of the columns in the CSV file that the YAML is for.
- The YAML must be structured in the following format:
Format Sample openapi: 3.0.3 components: schemas: <name of the schema>: type: <schema type> properties: <name of first column in CSV file>: type: <data type of the first column> <name of second column in CSV file>: type: <data type of the second column> <name of third column in CSV file>: type: <data type of the third column> <name of fourth column in CSV file>: type: <data type of the fourth column> <name of fifth column in CSV file>: type: <data type of the fifth column>openapi: 3.0.3 components: schemas: Account: type: object properties: AccountID: type: integer AccountName: type: string Industry: type: string AnnualRevenue: type: integer CreatedDate: type: string format: date LastModifiedDate: type: string format: date-time
The YAML file is optional. While configuring a data source node, you can manually add the fields from the CSV file schema.
Decide Your Definition's Data Source Design
The way you configure a data source node to use CSV files as data source can differ based on the purpose of the definition. Here are the different ways in which you can configure data source nodes to accept CSV files based on your requirements:
| Requirements | How to Configure the Data Source Node |
|---|---|
| The definition requires data from multiple CSV files. | Create a data source node for each CSV file that you want to use as data source. A definition can consist of up to five data source nodes that use CSV files as source. |
| The definition must only be run one time to transform data from CSV files and write back the results. | In the data source nodes that use CSV files as data sources, specify the IDs of the content documents that contain the CSV files that the nodes must use as data sources. |
| The definition must be run multiple times over time with data from different CSV files. | In the data source nodes that use CSV files as data sources, use File Identifier type input variables to specify the ID of the content documents that contain the CSV files that the nodes must use as data sources. When the definition is run, you can specify content document IDs that must be used as data sources as input variable values. |
Upload CSV Files in Salesforce
Definitions only use CSV files that are uploaded as content documents in Salesforce Files. If you want to create a definition that must be run one time, upload the definition's data source CSV file in Salesforce Files, and then create your definition. If you want to run a definition multiple times, then set up a process to upload the CSV files that are used as data sources for each run before starting the run.
Create a Data Source Node with for a CSV File
- On the Configuration tab of a Data Source node, select CSV File, and then click Define CSV Schema.
-
Define the schema of your CSV file in either of these ways:
- If you've created a YAML file to represent the metadata of the CSV file, upload the YAML file.
- If you haven't create a YAML file, manually add fields that represent the CSV file columns, and add the data type and alias for each field.
For both these methods, make sure that the field names are an exact match of the column headers in the CSV file. - Save your file schema.
- For the Primary Key field, select the CSV file column that uniquely identifies each row of the file.
-
For Content Management File ID, either:
Input Variable Select the File Identifier type input variable that will provide the content document ID of the CSV file. Value Enter the ID of the content document that contains the CSV file to use as data source. The ID of content documents are available on the URL of the File records that represent content documents. In the URL, the 18 digit alphanumeric that starts with 069 is the document ID. To learn about how to view the details of a file, see View and Edit File Details in Lightning Experience in Preview Files and Edit File Details.
- Save the node.
Join Data from Different Sources
A definition can consist of different data sources such as CSV files, or Data 360 objects, and Salesforce objects. After you add all data source nodes that are required in a definition, use the Join nodes to combine related data from different sources. For example, if a CSV file-based data source has additional details for accounts and another Salesforce object-based data source node brings data from the Account object, you can use the primary key from the CSV file and the ID from the Account object to join the data.
