Problems with Exporting X-Y Data with ArcMap 10.1 – Lessons on formatting Excel data first
In the past, I have never had many issues with extracting X-Y data from excel files and using it in ArcMap. However, I’ve recently been having difficulty properly exporting GNIS points from an Excel file (pipe delineated text file saved in a .xlsx worksheet) to a shapefile. I’ve made sure to set the Lat/Long fields in Excel to Numeric Data types and set the geographic projection in ArcMap to WGS 1984, the data’s native coordinate system, as I display the XY Data. The data appears to export just fine, but when brought into the same ArcMap session, the extent acts strangely when zooming in on in. An error finally occurs when adding the data to a different ArcMap session – The error reads:
“Warning, inconsistent extent!
One or more of the added layers has an extent that is not consistent with the associated spatial reference information.
Re-projecting the data in such a layer may lead to unexpected behavior.”
Well, I think I have more problems than simply being concerned about projection transformations!! After much searching on the web, it appears that yet again, Excel is partially to blame (and maybe some user error. OK, mostly user error). Because of unexpected characters (spaces, in my case), the data was unable to save properly. My solution was to perform two search and replaces in the excel spreadsheet to format the data and remove these “unexpected” characters.
The first was to replace any spaces at the beginning of a cell; the second was to replace any spaces at the end of a cell with nothing (i.e. delete the space). I achieved this by using the wildcard (*) function in the Find/Replace dialog. To delete the spaces at the beginning, the Find What field should be filled in as *[space]; a [space]* will delete the spaces at the end of a cell. This function will only remove one space for each time the Find and Replace is run. It might have to be run multiple times in order to eliminate all the problematic characters.
For formality sake, I also ensured that my field names had an appropriate length of 10 characters. Although, any field names longer will be truncated, it is better to be safe than sorry.
----
For basic information on adding X-Y data to ArcMap 10.1, check out the ESRI Help file:
http://resources.arcgis.com/en/help/main/10.1/index.html#//00s50000001z000000
Remember that the X field is for Longitude and the Y field is for Latitude!
