Handling Column Types when Creating New Tables

When uploading TAB or shapefiles, the column types in the source file are used when creating the new table. For Excel, CSV, and GeoJSON files, the column types are autodetected after the file has been uploaded. The autodetection scans all records. The tables below show how types map for each case.

TAB Files

When uploading to a SQL Server destination

Data type read from the source Interpreted as Spatial data type
logical BOOLEAN
date DATE
datetime DATE_TIME
decimal DBASE_DECIMAL
float FLOAT
integer INTEGER
integer64 INTEGER
largeint LONG_INTEGER
smallint SHORT_INTEGER
char STRING
time TIME

When uploading to a TAB file

Data type read from source Interpreted as Spatial data type
The column types of the original TAB file are retained.

Shape Files

Data type read from the source Interpreted as Spatial data type
short Integer SHORT_INTEGER
long Integer LONG_INTEGER
float FLOAT
double DOUBLE
text STRING
date DATE_TIME

Excel Files

The type of each column is auto-detected on the server by scanning the data. Spectrum Spatial Analyst supports the following types.

Auto-detection logic Interpreted as Spatial data type

The column has a date, time, or date/time format applied in Excel.

Note: The format rather than the content of the column determines the type. If DATE_TIME is required, ensure that the format includes both date and time. If a column is formatted to show only time, it uploads as a TIME type, and dates are lost.

DATE_TIME, DATE or TIME
Logical (determined by looking for the words “true” of “false”) BOOLEAN

Number without decimal places. The interpreted type (integer or long) depends on the length or the number.

Note: Columns containing short numbers are always created as an integer so that larger numbers may be inserted when editing the table.

INTEGER or LONG
Number with decimal places DOUBLE
Any other valid value STRING

Treatment of formulas

When there is a formula in the Excel sheet, its value is used. If the formula generates an Excel error, the upload fails and returns the cell address in an error message. Excel errors are #DIV/0, #N/A, #NAME?, #NULL!, #NUM!, #REF! and #VALUE!

CSV and GeoJSON Files

The column type is auto-detected on the server by scanning the data. Spectrum Spatial Analyst supports the following types. Dates and times in these files are added as a string.

Auto-detection logic Interpreted as Spatial data type
Logical (determined by looking for the words “true” of “false”) BOOLEAN

Number without decimal places. The interpreted type (Integer or long) depends on the length or the number.

Note: Columns containing short numbers are always created as an integer so that larger numbers may be inserted when editing the table.

INTEGER or LONG
Number with decimal places DOUBLE
Other single value types (such as dates/times) STRING
Object types in GeoJSON Unsupported – the upload will fail