in implementation guides ~ read.
Bi Dev Guide Ext Data Format

Bi Dev Guide Ext Data Format

DOWNLOAD

First things first !
To download this implementation guide, click the download button below.
If you need more information about the implementation guide, you can read the Table of Contents below.

Download

Tableau CRM External Data

Format Developer Guide
Salesforce Spring

salesforcedocs
Last updated February

Copyright salesforcecom inc All rights reserved Salesforce is a registered trademark of salesforcecom inc

as are other names and marks Other marks appearing herein may be trademarks of their respective owners

CONTENTS

External Data Metadata Overview
External Data Metadata Format Reference

EXTERNAL DATA METADATA OVERVIEW

To upload external data into a Tableau CRM 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 commaseparated value CSV format

An optional metadata file which describes the structure of the data file in JSON format

Note Providing a metadata file is recommended Otherwise every field is treated as text
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 thats described next

NameAmountCloseDate

opportunityA

opportunityB

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 Field Names

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

fileFormat

charsetName UTF

fieldsDelimitedBy
fieldsEnclosedBy
fieldsEscapedBy
linesTerminatedByrn
numberOfLinesToIgnore

objects

connector AcmeCSVConnector
description
fullyQualifiedName SalesData
label Sales Data
name SalesData
fields

description

External Data Metadata Overview

fullyQualifiedName SalesDataName
label Account Name
name Name
isSystemField false
isUniqueId false
isMultiValue false
type Text

description
fullyQualifiedName SalesDataAmount
label Opportunity Amount
name Amount
isSystemField false
defaultValue
isUniqueId false
type Numeric
precision
scale

description
fullyQualifiedName SalesDataCloseDate
label Opportunity Close Date
name CloseDate
isSystemField false
isUniqueId false
type Date
format MMddyyyy
fiscalMonthOffset

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 arent 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 JohnSmith is valid John Smith is valid but the second value is
Smith John Smith isnt valid

External Data Metadata Overview

The maximum numeric value is and the minimum is
Dates must conform to specific formats and they must match the formats exactly For more information see Date Formats

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
For more information about field names see Field Name Restrictions

External Data Limits
The following limits apply to all supported editions
Limit

Value

Maximum file size per external data uploads

GB

Maximum file size for all external data uploads in a rolling hour GB

period
Maximum number of external data jobs per dataset that can be
run in a rolling hour period

Maximum number of characters in a field

Maximum number of fields in a record

including up to date fields

Maximum number of characters for all fields in a record

EXTERNAL DATA METADATA FORMAT REFERENCE

The metadata describes the structure of external data files The metadata file is in JSON format The json file consists of main sections

file format object information and field information Include all required fields when you create a record but you can leave out optional
fields

The File Format Section
The file format section of the metadata file specifies information about the format of the data file including the character set and delimiter
character
Field Name

Type

Required Description

charsetName

String

No

The character set of the csv file If this field is included it must be set

to UTF

Example

charsetName UTF

fieldsDelimitedBy

String

No

The character that separates field values in the csv file Any single
character is supported
Example
fieldsDelimitedBy

fieldsEnclosedBy

String

No

The character that can be used to enclose fields in the csv file Any
single character that isnt the fieldsDelimitedBy value is
supported If a double quote is used within a field escape it by
preceding it with another double quote
Example
fieldsEnclosedBy

fieldsEscapedBy

String

No

The character used to escape or bypass the creation of a row of data
based on detecting the fieldsDelimitedBy value The default
is a double quote which would surround the data to escape
Example
fieldsEscapedBy

Example
This is an alternate escaping option to fieldsEnclosedBy To
illustrate if fieldsDelimitedBy is comma
fieldsEnclosedBy is double quote and

External Data Metadata Format Reference

Field Name

Type

Required Description
fieldsEscapedBy is backslash these two lines would result in

the same thing
colcolwithcommascol
colcolwithcommascol

linesTerminatedBy

String

numberOfLinesToIgnore Number

No

Deprecated Do not use

No

The number of lines for the parser to ignore Allows you to specify a
header
When the csv file doesnt have a header set to
When the csv file has a header set to
Example
numberOfLinesToIgnore

The Objects Section
The objects section of the metadata file specifies information about the toplevel database object including objectlevel security

information display name and API name

Note The metadata file can contain only object definition
Field Name

Type

rowLevelSecurityFilter String

Required Description
No

The predicate thats used to apply rowlevel security on the dataset

When entering the predicate in the metadata file JSON escape double

quotes around string values
Example
rowLevelSecurityFilter OwnerId
UserId

For more information about creating the predicate see the Tableau

CRM Security Implementation Guide

connector

String

Yes

The string that uniquely identifies the client application
Example
connector AcmeCSVConnector

description

String

No

The description of the object Must be less than characters
Example
description The SalesData object tracks
basic sales data

External Data Metadata Format Reference

Field Name

Type

Required Description

fullyQualifiedName

String

Yes

The full path that uniquely identifies the record Must be less than
characters
Example
fullyQualifiedName CRMSalesData

For information on creating valid field names see Field Names
label

String

Yes

The display name for the object Can be up to characters
Example
label Sales Data

name

String

Yes

The unique API name for the object Can be up to characters

Example
name SalesData

For information on creating valid field names see Field Names
fields

Array

Yes

The array of fields for this object

The Fields Section
The fields section of the metadata file specifies information about each field in the record including data type and formatting information

Note The fields must be in the same order as the CSV columns are in

Field Name

Type

Required Description

fullyQualifiedName

String

Yes

The full path that uniquely identifies the field objectfield Must be
less than characters
Example
fullyQualifiedName SalesDataAmount

For information on creating valid field names see Field Names
label

String

Yes

The display name for the field Can be up to characters
Example
label Opportunity Amount

name

String

Yes

The unique API name for the field Can be up to characters

Example
name Amount

External Data Metadata Format Reference

Field Name

Type

Required Description
Note With Summer Plus we recommend that field names
in dataset use no more than characters Long field names
increase the likelihood of exceeding character limits when you
augment dataset because names are appended Field names
longer than characters are currently supported but we
recommend shortening them to avoid issues
For information on creating valid field names see Field Names

description

String

No

The description of the field Must be less than characters
Example
description The Amount field contains the
opportunity amount

isSystemField

Boolean

No

Indicates whether this field is a system field to be excluded from query
results
Example
isSystemField false

type

String

Yes

The type of the field Can be Text Numeric or Date
Example
type Numeric

defaultValue

String

No

The default value of the field if any All numeric types require a default
value

isUniqueId

Boolean

No

Indicates whether this field is the primary key for the record This field
is required for incremental extract Only field can be set to be the

unique ID

Note Only text fields can be unique IDs Numeric date and
multivalue fields cant be unique IDs
Example
isUniqueId false

isMultiValue

Boolean

No

For text fields only Indicates whether the field has multiple values
Applies only to Text fields
Example
isMultiValue false

multiValueSeparator

String

No

For text fields only The character that separates multiple values The
default is
If isMultiValue equals true specify a value
If isMultiValue equals false this field can be set to null

External Data Metadata Format Reference

Field Name

Type

Required Description
Example
multiValueSeparator

format

String

Yes for
The format of the date or numeric value See also Date Formats and
Date values Numeric Formats
only
Example
format ddMMyy HHmmss Date

Example
format Numeric
precision

Number

Yes for
Numeric
values

The maximum number of digits in a numeric value or the length of a
text value
For numeric values Includes all numbers to the left and to the right of
the decimal point but excludes the decimal point character Value
can be up to
For text values Value defaults to characters but can be up to
characters
Example
precision

scale

Number

Yes for
Numeric
values

The number of digits to the right of the decimal point in a numeric
value Must be less than the precision value
Example
scale

canTruncateValue

Boolean

No

Indicates whether to truncate a value when the value exceeds the
precision The default is true
If true truncates the value
If false the row is rejected
Example
canTruncateValue true

currencySymbol

String

No

For numeric fields only The character that signifies the values currency
Example
currencySymbol

decimalSeparator

String

No

For numeric fields only The character that separates digits in a decimal
number Can be used to handle international number formats where
the decimal separator is The default is
Example
decimalSeparator

External Data Metadata Format Reference

Field Name

Type

Required Description

fiscalMonthOffset

Number

No

For date fields only The difference in months between the fiscal year
and the calendar year For example if the fiscal year starts in January
the offset is If the fiscal year starts in October the offset is
Example
fiscalMonthOffset

Note This attribute also controls whether Tableau CRM

generates fiscal date fields To generate fiscal date fields set
fiscalMonthOffset to a value other than
See also Date Handling in Datasets
groupSeparator

String

No

For numeric fields only The character that separates digit groups in a
number Can be used to handle international number formats where
the group separator is The default is
Example
groupSeparator

isYearEndFiscalYear

Boolean

No

For date fields only Indicates whether the fiscal year is the year in which
the fiscal year ends or begins Because the fiscal year can start in one
calendar year and end in another specify which year to use for the
fiscal year
If true then the fiscal year is the year in which the fiscal year ends
The default is true
If false then the fiscal year is the year in which the fiscal year begins
Example
isYearEndFiscalYear true

This field is relevant only when fiscalMonthOffset is greater
than
See also Date Handling in Datasets
firstDayOfWeek

Number

No

For date fields only The first day of the week for the calendar year and
if applicable fiscal year Use to set the first day to be Sunday to set
the first day to be Monday and so on Use to set the first day to be
January The default is
Example
firstDayOfWeek

See also Date Handling in Datasets
isSkipped

Boolean

No

Indicates whether to skip the field when the data is uploaded
Example
isSkipped true

External Data Metadata Format Reference

Field Name Restrictions
Field names in the csv file and the metadata file
Can contain only alphanumeric and underscore characters
Must begin with a letter
Cant end with an underscore
Cant contain consecutive underscore characters except when ending with c casesensitive
Must be unique across all fields of the object

Numeric Formats
An example of a typical numeric value is which is represented as in the format field Youre required to
specify the precision and scale of the number The format is specified by using the following symbols
Symbol

Meaning

One digit Use to add leading or trailing s like for

Adds zero or one digit

Default symbol used as the decimal separator Use the decimalSeparator field to
set the decimal separator to a different symbol

Minus sign

Grouping separator Use the groupSeparator field to set the group separator to a
different symbol

Currency sign Use the currencySymbol field to set the currency indicator to a different
symbol

Note The format for numeric values when displayed in the UI defaults to No Format Existing formatting is removed For data

ingestion numeric values cant contain any formatting such as currency symbols or grouping separators For example
isnt a valid numeric value the correct value is
Valid characters when defining a numeric format are

External Data Metadata Format Reference

Date Formats
For Date fields specify the format of the date by using one of the following supported formats Dates must match the format exactly
and cant have any extra text For example if the date format is MMddyyyy hhmmss and the value is
the upload fails because the value has extra milliseconds
Note The date formats listed here are the twodigit versions for date fields that use leading zeros for example

AM If a date field doesnt have leading zeros use the onedigit version of the format For example use the format Mdyy

hms a for date values such as AM If you use a twodigit format for a field rows containing values with onedigit

date parts will fail
The timestamp part of each date format is optional
Format

Sample Value

yyyyMMddTHHmmssSSSZ

TZ

yyMMddTHHmmssSSSZ

TZ

yyyyMMddTHHmmssZ

TZ

yyMMddTHHmmssZ

TZ

yyyyMMdd HHmmss

yyMMdd HHmmss

ddMMyyyy HHmmss

ddMMyy HHmmss

ddMMyyyy HHmmss

ddMMyy HHmmss

ddMMyyyy hhmmss a

AM

ddMMyy hhmmss a

AM

ddMMyyyy HHmmss

ddMMyy HHmmss

ddMMyyyy hhmmss a

AM

ddMMyy hhmmss a

AM

MMddyyyy hhmmss a

AM

MMddyy hhmmss a

AM

MMddyyyy hhmmss a

AM

MMddyy hhmmss a

AM

HHmmss ddMMyyyy

HHmmss ddMMyy

***