Skip to Main Content

Preparing Data for Input into Geospatial Applications: Best Practices When Using Excel Files with ArcGIS

This is a guide to help Virginia Tech community members prepare their data before inputting it into a geospatial software/application.

Cleaning GIS data in Excel before importing it into ArcGIS is essential for ensuring accurate analysis and visualization. Properly prepared data reduces the risk of common issues like misaligned coordinates, incorrect attribute values, and failed imports. This process involves removing duplicates, correcting inconsistent naming, standardizing column headers, and ensuring data types are appropriately formatted. These steps help maintain data integrity, improve processing efficiency, and ensure that spatial features are correctly interpreted within the GIS environment.

  • When creating spreadsheets, make sure fields are fewer than 255 characters

ArcGIS reads the first 255 field characters. Fields with more than 255 characters are converted to BLOB (data stored as a long sequence of binary numbers) fields and are not readable. Abbreviate, manually truncate, or split any fields longer than 255 characters.

  • Check the numeric field type before and after importing Excel data

ArcGIS typically converts spreadsheet numeric fields to double precision (Double), which may not meet your needs. If necessary, create new fields of the desired type and calculate values into them.

  • Check the format for date fields

ArcGIS uses the Lotus date/time format. In this format, the calendar date is represented by a whole number value that represents the number of days since January 1, 1900, plus one day (due to a bug in Lotus 123 and carried over to Excel). Time is represented as the decimal portion of a 24-hour day. If date/time data is important, format the input spreadsheet using a standard Excel date/time format.

  • Follow ArcGIS field naming rules when creating Excel column names

The first row of an Excel worksheet sets the name for each column. Column names become field names when an Excel worksheet is imported into ArcGIS. Always follow these naming rules:

  • Column/Field names must begin with a letter.
  • Column/Field names must contain only letters, numbers, and the underscore character.
  • Column/Field names must be no more than 64 characters. If a name is longer than 64 characters, ArcGIS retains the first 63 characters.
  • Column/Field names may not consist solely of reserved words. Do not use these words in field names. See the list of reserved words. ArcGIS typically adds a trailing underscore to reserved word field names added by copying and pasting from other sources.
  • Column/Field naming rules also apply to named ranges imported from Excel.

Article from: https://www.esri.com/news/arcuser/0312/best-practices-when-using-excel-files-with-arcgis.html