Providing Data Types In CSV Headers

Column data types can be defined prior to upload into SpatialKey.  Simply enter “|[Data Type]|[Subtype]” after the header names in your CSV file and SpatialKey will automatically assign the provided data types during Upload.

We expect this feature will be especially useful for clients who frequently create datasets with the same structure – it will both reduce the amount of time the import takes (less time for data detection and stepping through the upload screens) and can increase accuracy and consistency.

Supported Data Types and Subtypes

Data Type Subtypes (if available) Example
STRING HeaderName|STRING
NUMBER INTEGER

DECIMAL

CURRENCY*

ID

PERCENT

 HeaderName|NUMBER|INTEGER

HeaderName|NUMBER|DECIMAL

HeaderName|NUMBER|CURRENCY*

HeaderName|NUMBER|ID

HeaderName|NUMBER|PERCENT
DATE YEAR

MONTH

DAY

HOUR

MINUTE

SECOND

 HeaderName|DATE|YEAR

HeaderName|DATE|MONTH

HeaderName|DATE|DAY

HeaderName|DATE|HOUR

HeaderName|DATE|MINUTE

HeaderName|DATE|SECOND
LATITUDE HeaderName|LATITUDE
LONGITUDE HeaderName|LONGITUDE
STREET HeaderName|STREET
CITY HeaderName|CITY
ZIP HeaderName|ZIP
COUNTY HeaderName|COUNTY
STATE HeaderName|STATE
COUNTRY HeaderName|COUNTRY

 

*An additional currency type can be specified for currency columns.  The possible codes are listed below:

  • HeaderName|CURRENCY|USD
  • HeaderName|CURRENCY|GBP
  • HeaderName|CURRENCY|EUR
  • HeaderName|CURRENCY|CNY
  • HeaderName|CURRENCY|JPY
  • HeaderName|CURRENCY|AUD
  • HeaderName|CURRENCY|CAD
  • HeaderName|CURRENCY|CHF

Data Types and Subtypes Example

If the headers in your CSV file looks like this…

Account Name, Address, City, Postcode, Limit, Tiv

You would change them to include the type information like this…

Account Name|String, Address|Street, City|City, Postcode|Zip, Limit|Number|Integer, Tiv|Number|Integer

Important Items to Consider

  1. The header names that display in SpatialKey will not include the data type and subtype information.  Given the example above, your field names would be “Account Name”, “Address”, “City”, “Postcode”, “Limit”, and “Tiv” once that data is Uploaded into SpatialKey.
  2. If you misspell a type, the data after the “|” will be ignored.  E.g. “Account Name|STNG” would be treated as if only “Account Name” was provided in the header and you would have the option to map the type during upload.
  3. Data types and subtypes can be specified for all data uploaded into SpatialKey – policy data, shapefiles, etc.

Geocoding Fields

  1. If you specify fields with data type of LATITUDE and LONGITUDE, we will automatically use this as the geometry of the data.
  2. If you do not specify fields with data type LATITUDE and LONGITUDE fields, but do set any combination of STREET, CITY, ZIP, COUNTY, STATE, COUNTRY fields, we will automatically geocode based on those fields.
  3. If you specify LATITUDE and LONGITUDE, and any combination of STREET, CITY, ZIP, COUNTY, STATE, COUNTRY fields:
    • We will use the LATITUDE and LONGITUDE values where they are not null or 0.
    • Where the LATITUDE and LONGITUDE fields are null or 0, we will use the STREET, CITY, ZIP, COUNTY, STATE, COUNTRY fields to perform geocoding to try to get a better LATITUDE and LONGITUDE.
  4. Geocoding with other options like FIPS or another X/Y coordinate system are not supported when you use this feature.

Which Geocoder to Use?

If you specify address fields to use for geocoding, SpatialKey will use your organization’s default geocoder.  If you want to use a different geocoder you can add additional information into the headers to specify which geocoder to use.

For the columns that will be used for geocoding, add an additional “|” character and then specify which geocoder to use.  Note that the geocoder only needs to be specified for one of the provided address fields.

For example: HeaderName|STREET|[Geocoder]

Valid geocoders that you can specify are: SKFREE, MQPREMIUM (if you license MapQuest geocoding) and BING (if you license Bing geocoding).  Consult the more detailed geocoder information to compare the different geocoders.  Before using a premium geocoder consider whether your organization has enough geocoding transactions available for your dataset and that you and your organization have permissions for the premium geocoder you specify.