Control File Configuration

Contents

• Basic control file setup
  • Header row / column list
  • Date/time formatting
  • Location column and geocoding configuration
  • Ignoring columns
• Complete control file settings

Basic control file setup

The control file is a JSON-formatted file that is used to configure a Standard DataSync job that uses the FTP or HTTP means of transfering data. Control files are specific to the dataset you are updating.

An example of a typical control file:

{
  "action" : "Replace",
  "csv" :
    {
      "useSocrataGeocoding" : true,
      "columns" : null,
      "skip" : 0,
      "fixedTimestampFormat" : ["ISO8601","MM/dd/yy","MM/dd/yyyy"],
      "floatingTimestampFormat" : ["ISO8601","MM/dd/yy","MM/dd/yyyy"],
      "timezone" : "UTC",
      "separator" : ",",
      "quote" : "\"",
      "encoding" : "utf-8",
      "emptyTextIsNull" : true,
      "trimWhitespace" : true,
      "trimServerWhitespace" : true,
      "overrides" : {}
    }
}

This guide will describe how to use the different options within the control file.

Header row/column list

The columns and skip options enable configuration of how the columns within the CSV/TSV align with those of the dataset.

columns: List of column names in the following format ["col_id1","col_id2",..]. If null, the first line of the CSV/TSV after any skipped records is assumed to contain the column names. If specified, columns must be an array of strings, and must not contain nulls.

IMPORTANT NOTE: the column names, whether provided in “columns” or in the first row of the CSV/TSV, must be the API field names, not the display name of the columns.

skip: Specifies the number of rows to skip before reaching the header.

Common combinations of columns and skip:

If the first line of the CSV/TSV is the list of column identifiers:

"columns": null,
"skip": 0,

If the first line of the CSV is the columns incorrectly formatted, for example with human-readable names instead of column identifiers:

"columns": ["first_name","last_name","age"],
"skip": 1,

If the first line of the CSV/TSV is data (there is no header row):

"columns": ["first_name","last_name","age"],
"skip": 0,

Date/time formatting

Timestamp Format Options

The floatingTimestampFormat and fixedTimestampFormat options specify how date/time data is formatted in the CSV/TSV file. floatingTimestampFormat applies to (“Date & Time” datatype columns). fixedTimestampFormat functions similarly but applies (“Date & Time (with timezone)” datatype columns). If the format does not specify a time zone, the zone may be given via the timezone option. If no zone information is provided, UTC is assumed.

Both floatingTimestampFormat and fixedTimestampFormat accept a string (e.g. “ISO8601”) or a JSON-formatted list of formats including “ISO8601” and any date/time “Joda time” format-string. Joda time syntax is documented in detail here: http://www.joda.org/joda-time/apidocs/org/joda/time/format/DateTimeFormat.html. Note that time specification “z” cannot be used; it is an output only formatter.

Example syntax to accept four of the most common date/time formats:

"fixedTimestampFormat" : ["ISO8601","MM/dd/yy","MM/dd/yyyy","dd-MMM-yyyy"],
"floatingTimestampFormat" : ["ISO8601","MM/dd/yy","MM/dd/yyyy","dd-MMM-yyyy"],

This would accept any of the following example date/time data values: “2014-04-22”, “2014-04-22T05:44:38”, “04/22/2014”, “4/22/2014”, “4/22/14”, and “22-Apr-2014”.

If you want to allow a date with or without a time value (e.g. both “2014-04-22” and “2014-04-22 9:30:00”), you would use:

"fixedTimestampFormat" : ["yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss"],
"floatingTimestampFormat" : ["yyyy-MM-dd", "yyyy-MM-dd HH:mm:ss"],

Timezone option

timezone specifies the timezones for FixedTimestamps (“Date & Time (with timezone)” columns). This only has an effect if the timestamp format does not specify a time zone.

You can set this to one of the following:

1. “UTC”
2. A timezone name (e.g. “US/Pacific”). The list of accepted names can be found at http://joda-time.sourceforge.net/timezones.html. Please avoid the 3-letter variants as these are ambiguous (e.g. MST is both Mountain Standard Time and Malaysia Standard Time).

Location column and geocoding configuration

The syntheticLocations option allows configuring a Location datatype column to populate from address, city, state, zipcode or latitude/longitude data within existing columns of the CSV/TSV.

For example:

"syntheticLocations" : {
  "location_col_id" : {
    "address" : "address_col_id",
    "city" : "city_col_id",
    "state" : "state_col_id",
    "zip" : "zipcode_col_id",
    "latitude" : "lat_col_id",
    "longitude" : "lng_col_id"
  }
}

The following fields are optional: “address”, “city”, “state”, “zip”, “latitude”, and “longitude”. Those that are are not provided are omitted from the generated location. The column field names must exist in the CSV. In the above example, a Location datatype column with the identifier location_col_id would pull in the “address” from the column with identifier address_col_id and the “city” from column with identifier city_col_id.

The synthetic location location_col_id should not be present in the CSV. If it is, you can ignore this column using the ignoreColumns option.

When you provide any combination of location information but do not fill in latitude or longitude then Socrata geocodes the information automatically to generate the latitude and longitude values. For more information on Socrata geocoding, please see this guide.

IMPORTANT: If you are providing the latitude and longitude values as inputs to the Location column (i.e. you are NOT using Socrata’s geocoding), you should set the useSocrataGeocoding option to false. If you are not providing the latitude and longitude, you should to set this to true. This will minimize the number of perceived changes to the dataset, decreasing the time it takes to complete your job. If you are constructing multiple Location columns and they require different useSocrataGeocoding settings, you may use the overrides option.

Ignoring Columns

The ignoreColumns option allows you to exclude columns within the CSV/TSV. This may be necessary if the dataset lacks a column within the CSV or if a synthetic location is provided in the CSV, but you would still like it constructed from individual address fields.

ignoreColumns: List of column names in the following format ["col_id1","col_id2",..]. These must be present in columns.

Complete control file settings

The control file is comprised of the Action setting and the CSV or TSV settings.

Actions

The action is given by one of the following strings:

CSV or TSV Settings

The following are options available to both CSV files or TSV files within the csv or tsv object:

Example of using column-level overrides:

{
  "action" : "Replace",
  "csv" : {
    "useSocrataGeocoding" : true,
    "fixedTimestampFormat" : "ISO8601",
    "floatingTimestampFormat" : "ISO8601",
    "timezone" : "UTC",
    "overrides" : {
      "my_time_column" : {
        "timestampFormat" : "YYYY-MM-dd HH:mm:ss",
        "timezone" : "US/Central"
      },
      "my_text_column" : {
        "emptyTextIsNull" : true
      }
      "my_location_column" {
        "useSocrataGeocoding" : false
      }
    }
  }
}