External Data Metadata Overview

To upload external data into a CRM Analytics dataset, make sure that you have prepared your data and metadata files.

You can load external data into a dataset by preparing two files.

A data file, which contains the external data, in comma-separated value (CSV) format
An optional metadata file, which describes the structure of the data file in JSON format

Providing a metadata file is recommended. Otherwise, every field is treated as text.

Note

The data and metadata files are used to populate a dataset with the external data.

CSV Example

The following CSV example contains data that conforms to the .json metadata file that’s described next.

1Name,Amount,CloseDate
2opportunityA,100.99,6/30/2014
3opportunityB,99.01,1/31/2012

The first row in the CSV file lists the field names for your dataset. Each subsequent row corresponds to a record of data. A record consists of a series of fields delimited by commas. For information on creating valid field names, see External Data Metadata Format Reference.

JSON Example

The following JSON example represents a SalesData object with three fields: Name, Amount, and CloseDate. The example corresponds to the preceding CSV example.

1{
2    "fileFormat": {
3        "charsetName": "UTF-8",
4        "fieldsDelimitedBy": ",",
5        "fieldsEnclosedBy": "\"",
6        "fieldsEscapedBy":""
7        "linesTerminatedBy":"\r\n"
8        "numberOfLinesToIgnore": 1
9    },
10    "objects": [
11        {
12            "connector": "AcmeCSVConnector",
13            "description": "",
14            "fullyQualifiedName": "SalesData",
15            "label": "Sales Data",
16            "name": "SalesData",
17            "fields": [
18                {
19                    "description": "",
20                    "fullyQualifiedName": "SalesData.Name",
21                    "label": "Account Name",
22                    "name": "Name",
23                    "isSystemField": false,
24                    "isUniqueId": false,
25                    "isMultiValue": false,
26                    "type": "Text"
27                },
28                {
29                    "description": "",
30                    "fullyQualifiedName": "SalesData.Amount",
31                    "label": "Opportunity Amount",
32                    "name": "Amount",
33                    "isSystemField": false,
34                    "defaultValue": "0",
35                    "isUniqueId": false,
36                    "type": "Numeric",
37                    "precision": 10,
38                    "scale": 2,
39                },
40                {
41                    "description": "",
42                    "fullyQualifiedName": "SalesData.CloseDate",
43                    "label": "Opportunity Close Date",
44                    "name": "CloseDate",
45                    "isSystemField": false,
46                    "isUniqueId": false,
47                    "type": "Date",
48                    "format": "MM/dd/yyyy",
49                    "fiscalMonthOffset": 0
50                }
51            ]
52        }
53    ]
54}

CSV Format

The External Data API uses a strict format for field values to optimize processing for large sets of data. Note the following when generating .csv files.

If a field value contains a control character or a new line the field value must be contained within double quotes (or your fieldsEscapedBy value). The default control characters (fieldsDelimitedBy, fieldsEnclosedBy, fieldsEscapedBy, or linesTerminatedBy) are comma and double quote. For example, "Director of Operations, Western Region".
If a field value contains a double quote, escape the double quote by preceding it with another double quote (or your fieldsEscapedBy value): for example, "This is the ""gold"" standard".
Field values aren’t trimmed. A space before or after a delimiting comma is included in the field value. A space before or after a double quote generates an error for the row. For example, John,Smith is valid. John, Smith is valid, but the second value is " Smith". "John", "Smith" isn’t valid.
The maximum numeric value is 36,028,797,018,963,967 and the minimum is -36,028,797,018,963,968.
Dates must conform to specific formats, and they must match the formats exactly. For more information, see External Data Metadata Format Reference.
At least one column in the CSV file must contain dimension values.
If column headers are specified, the number of column headers must equal the number of columns in each record.

External Data Limits

For more information on limits for all supported editions, see External Data Limits in CRM Analytics Limits.