10

When adding tables layers from Excel files, ArcGIS attempts to guess the data-types:

Microsoft Excel does not enforce field types for values during data entry like standard databases do. Therefore, the field type specified in Excel is not used in determining the field type exposed in ArcGIS. Instead, field type in ArcGIS is determined by a scan of the values in the first eight rows for that field. If the scan finds mixed data types in a single field, that field will be returned as a string field and the values will be converted to strings.

Does anyone know what heuristic rules are used here?

PolyGeo
  • 65,136
  • 29
  • 109
  • 338
fmark
  • 9,381
  • 9
  • 39
  • 45
  • 2
    In the past when I've had a requirement to work with Excel data, I've often been able to get the client to agree to using .csv files (which both arcgis and excel handle). – Kirk Kuykendall Jul 28 '10 at 23:49

2 Answers2

4

I'm not sure that this helps with your specific question, and you may well have already seen it, but see these links from Esri ArcGIS blog and help docs, both titled "Formatting an Excel table for use in ArcGIS":

highlights:

Excel workbook and worksheet names should not contain spaces.

Field names must:
   - start with a letter.
   - contain only letters, numbers, and underscores.
   - not exceed 64 characters.

ArcMap scans first eight rows to determine field type. 
If mixed types encountered field is converted to text.

Numeric fields are converted to the double data type.
ArcMap can only read the first 255 characters of a cell.
matt wilkie
  • 28,176
  • 35
  • 147
  • 280
PolyGeo
  • 65,136
  • 29
  • 109
  • 338
2

The last two sentences provide a pretty good idea on the heuristics they use: scan only the first eight rows for mixed data types, anything mixed is converted into strings. Without further information from ESRI or through lots of testing, we can't say much else about it since we don't have the source code, unfortunately.

The problem with using Excel for 'real analysis' work is that fundamentally its a two-dimensional layout program: data types are not enforced, and the distinction between data and its presentation isn't clearly made.

scw
  • 16,391
  • 6
  • 64
  • 101