...
- Be sure that there are no spaces before or after your values. ArcMap tries to read these as part of the value field.
- All null/empty fields must be converted to 0.
- ID numbers must be in General format - no numbers or text
- make sure all values for quantities are formatted as numbers. You must preserve leading zeros.
- if error message for number stored as text, change so that this error message no longer occurs.
- Everything formatted as general shows up in ArcCatalog as Double
For
...
When Excel detects long number strings in any format other than Text or Number, it automatically displays them in Scientific format. ArcMap will not import columns formatted as anything but General for the purposes of creating spatial joins. Because of the DISPLAY of long number strings in General format as Scientific, ArcMap cannot read them. This is especially problematic in data files downloaded from the Census Bureau. Census Geographic Identifiers (ID field or Geo 2 field for data from before 2010) - crucial to joining Census data to TIGER shapefiles - always download in General format when imported to Excel.
You will first need to remove the unnecessary annotation at the beginning of the number string in the ID field (or Geo 2 field) so that it will properly match the STFID field in Census Bureau shape files.
- Highlight the ID/Geo2 column
- In the Home Tab, navigate to Editing → Find and Select
- Choose the Replace Option. The Find and Replace dialog box will open.
- Enter the value of the unwanted numbers and letters at the beginning of the string for ID/Geo2 into the Find what: field. The value is usually 1000000US, but double check to see which portion of the ID/Geo2 field and the STFID field do not match.
- Leave the Replace with field blank. This will simply delete the unwanted string of letters and numbers.
- Click Find Next. When the program highlights a cell, click Replace. Test this with a few cells to make sure that the program is only deleting the numbers and letters you want.
- If the first few test cells are successful, with only the ID/Geo2 column highlighted, click Replace All. A dialog box will appear letting you know from how many cells it deleted the specified string.
- Click OK, then click Close.
Do the following to resolve the problem of Excel storing these numbers as scientific notations:
- Create a new, empty column next to the ID field (Geo 2 field for Census data before 2010).
- Format the new column as General. Name it Geo 3
- Type in the formula =Left(cell# of cell in Geo 2 field, #of digits of number) This will convert the number to a regular, General format.
- Copy/paste the formula into every cell adjacent to a Geo 2 cell you need to convert
- Copy the entire newly formatted set of fields in the new column
- In same column, with all fields still highlighted, right-click and choose Paste Special. Choose Values from the available menu.
- Click OK. This should paste the actual Value of the Geo 2 field in General format into the new column and display it normally, rather than as a Scientific format.
- Use the Geo 3 field in your Spatial Join.
- This procedure will save you the effort of having to copy/paste the value of hundreds of Geo 2 cells.
For Addresses (Geocoding)
- Be sure that there are no spaces before or after the end of your address, city, zip code, or other letter and number strings. ArcMap tries to read these as part of the name, etc..
- Make sure that addresses are split into different fields for street address, city, and postal code.
- Street addresses should have no periods in them, even for abbreviated terms like Ave, W, E, or St.
- Use common abbreviations like Ave, W or St. You may also spell the words out, ie: Lane or Circle.
- Double check the spelling for streets and cities. Be sure that your spelling and abbreviations match exactly the official street name. Is it "Mount Carmel Ave" or "Mt Carmel Ave?" Database entries are matched exactly, so your entries for upload should match as exactly as possible.
...