If you find these tutorials useful and would like to link to them from your own pages,please contact the Drew Spatial Data Center Director, Dr. Catherine Riihimaki via email. You are invited to leave comments or ask questions in any of the Comment areas of each tutorial page.

For information about formatting data downloaded from the U.S. Census Bureau, see ArcMap - Downloading Census Data for Use in ArcGIS.

For Data Values (numbers, percentages, etc.)

  1. Be sure that there are no spaces before or after your values.  ArcMap tries to read these as part of the value field.
  2. All null/empty fields must be converted to 0. 
  3. ID numbers must be in General format - no numbers or text
    1. If your numbers are formatted as General, but are left justified, Excel is storing them as text.  These will not import properly into ArcGIS.  To fix this problem, do the following:
      1. In an empty cell of the spreadsheet, type the number 1
      2. Copy the cell with the number 1 you just created
      3. Highlight all of the General cells with text formatting - they will be left justified.
      4. Go to the Home tab, click on the black arrow underneath the Paste button and choose Paste Special.  The Paste Special dialog box will appear.
      5. In the Paste Special dialog box under the Paste options, choose Values.  Under the Operations options choose Multiply.
      6. Click OK.  (Adapted from http://en.allexperts.com/q/Excel-1059/Excel-Cell-Format-Issue.htm)
    2. If your numbers are right justified and the cells are formatted as General, they should be able to  be used properly as ID numbers in ArcGIS
  4. make sure all values for quantities are formatted as numbers.  You must preserve leading zeros.
  5. If there is an error message in cells for number stored as text, change each of these cells so that this error message no longer occurs.
  6. Everything formatted as general shows up in ArcCatalog as Double

Preserving Leading Zeros

If zip codes or other information that has leading zeros is formatted in General format, they will not import correctly into ArcGIS. 

Preserve Leading Zeros as Text

  1. Use these directions for preserving leading zeros in fields that can be used as text, such as zip codes.
  2. In a new column with the General format, type the following formula: =TEXT(CellofFirstValue,"00000").  There should be a number of zeros between the quotation marks equal to the number of digits in the numerals for which you want to preserve the leading zeros. 
    1. For example, a zip code has 5 digits, and the first zip code you are going to import tothe column is in cell B2.  You would write the formula this way: =TEXT(B2,"00000") 
  3. Copy and paste this formula into all cells into which you want to import the zip codes with leading zeros.  Do this by copying the cell, not by copying the text in the formula bar with the cursor.
  4. In a second new column, format the cells for TEXT.
  5. Copy the contents of the entire first column by right clicking on the column header (this will be a letter) and choosing Copy. 
  6. In the second column you formatted for text, click on the column header and choose Paste special.  The Paste special dialog box will appear.
  7. In the Paste Special dialog box under Paste, click the radio button next to the Values option.  Under Operation, leave the radio button next to None checked.  Click Okay.  This should paste the number with the leading zero intact for each cell. 
  8. Delete the original column and the column with the text formula from the spreadsheet.  
  9. Rename the newly formatted column with the desired name.

For Addresses (Geocoding)

  1. 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..
  2. Make sure that addresses are split into different fields for street address, city, and postal code. 
  3. Street addresses should have no periods in them, even for abbreviated terms like Ave, W, E, or St.
  4. Use common abbreviations like Ave, W or St.  You may also spell the words out, ie: Lane or Circle.
  5. 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.

If there are mistakes in your data for geocoding, there are ways to rectify them.  See the tutorial information on reconciling unmatched and tied addresses for help.

Unknown macro: {multi-excerpt}

Importing Tab-Delimited (.txt) Files to Excel

  1. Open MS Excel
  2. Click on the Office button and choose Open.  The Open File dialog box will appear. Find the file you want to open.  You may need to navigate to the place where the file you want to open is stored.
  3. Highlight the file you want to open by clicking on it once. 
  4. Click the Open button in the bottom, right hand corner of the screen.
  5. The Text Import Wizard dialog box will appear.

File Import Format

Use the following settings

  • Original data type: Delimited
  • Start Import at row: 1
  • File origin: 20127: US-ASCII

Click Next


Use the following settings:

  • Delimiters: Tab (with Tab Delimited files.  Other file types and delimiters will vary)
  • Text qualifier: "  You may need to change this.  Check the Data preview at the bottom of the screen.  The lines represent columns in an Excel file.  If the text is displaying properly, leave the Text qualifer field as-is.  If the text in each column is not displaying properly, choose a text qualifier from the pull down menu until the text in columns displays in the correct fashion.

Click Next

Column Import Format

Use the following settings:

  • Column data format: Text
    The default import format is General.  This will convert numeric values to numbers, date values to dates, and remaining values to text.  This is a problem when importing GPS latitude/longitude points or long identifier strings from Census data, because the General format will convert the values in these columns to scientific notations.  To make sure Excel imports all columns as Text, do the following:
  1. In the Data Preview, click on the heading of the first column - it should say "General."
    (click image to enlarge)

  2. Scroll the bar over to the far, right side so that the last column in your data set displays.
  3. Hold down the Shift key on your keyboard and click the header of the last column.  Release the Shift key.  This should highlight all the columns. 
  4. In Column data format, click the radio button next to Text.  This will import all of the columns in text format.
    (click image to enlarge)

  5. Click Finish
  6. Save the newly imported file in Excel format to the desired location on your computer, USB flash drive or network drive.

  • No labels