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 |