How to get tabular data from Excel to ArcGIS

Back to the   GIS Tutorials & Help Page...                         Back to the   SAL Home Page...

See also Adding X-Y Data to ArcGIS

~ ~ ~ ~ ~

In ArcGIS version 9.1 an Excel spreadsheet (.xls) cannot be read directly by ArcMap or ArcCatalog. As of version 9.2 ArcGIS will read an Office 2003 Excel spreadsheet directly (be sure to add the individual Worksheet not the entire Workbook). ArcGIS does not appear to be able to open an Office 2007 (.xlsx) file format.

(See also ESRI article using Excel data - this file is also stored on the J:\ drive: J:/saldata/tut_GIS/Excel_Data_Groundwater.pdf ).

ArcGIS 9.1 requires either a .dbf or a .txt file. In addition, there are often other cases when you would like to get the data out of Excel into ArcGIS. Exporting from Excel to either of these formats is not always straight forward (Microsoft doesn't always export things as you'd like). Moreover, Excel 2007 does not export to .dbf format.

If you have Office 2007 you will need to either save your Excel file as an Excel 2003 format (if you have ArcGIS 9.2) and open that, or save it as a .csv or .txt file.

EXPORTING FROM EXCEL

Often, to get files to work properly in ArcGIS you will need to reformat some of the data and/or the field headers. ArcGIS needs:

        Short field names without spaces (8 characters max)

        No blank rows

        Data formatted in standard rows and columns for the entire spreadsheet

To export from Excel:

        Choose Save As from the File menu

        In the Save As dialog box, choose one of the following in the Save as type box

            Text (Tab delimited) (*.txt)

               CSV (Comma delimited) (*.csv)    note, this is also a text file

               DBF 4 (cBase IV) (*.dbf) - 

                    NOTE THAT OFFICE 2007 DOES NOT EXPORT TO DBF FORMAT

        When you use Save As Excel will warn you that you are leaving the wonderful world of Excel (which of course was your goal). Simply click OK

        and then Yes to continue.

        Close your file (close Excel) - which will prompt the same error warnings all over... you can either opt to not save changes (as you close the file) or select OK and Yes again to re-save the file

Once your data has been saved to something other than an .xls file, it is a good idea to check the results. Make sure that:

        For text or csv files, make sure it is TAB or COMMA delimited (not space)

        Has only one header row containing the names of the fields

        Uses only standard alpha-numeric characters in the field names

        Has no more than 8 characters and no spaces in the field names

        Has no blank rows

        Has no extra rows of information below the data

Potential problems with Excel's export to .dbf:

        Decimals lost (rounded off and converted to integer)

        Field names truncated to 8 characters

        Text field converted to numbers

        All field names converted to all caps (so CityZipCodes becomes CITYZIPCOD)

        If exporting to a .dbf fails, try exporting to a .csv or .txt file (or vice versa)

If you get this message while attempting to Save As from Excel:

        Cick OK

        Manually select a cell with data

        Use Save As again...

~~~~~~~~~~~

Note:

If you've exported your .xls file to a .txt (or .csv) file it may be beneficial to convert that to a .dbf using ArcGIS. If you add a .txt file to ArcMap and then Open it, you can choose Export from the Options button to export your .txt file to a .dbf file. Due to the fact that Excel seems to have problems with exporting to .dbf (more so than exporting to a .txt or .csv file) this two step conversion make be the best option.

Back to the   GIS Tutorials & Help Page...                         Back to the   SAL Home Page...

ISIA | HML | SAL | Website (stefan) | Lab Manager (Dave Knutson)