MS Excel to an ArcGIS Geodatabase

Data can arrive in many formats. Often for the GIS guru (you) it'll be from Excel. It could be any version of Excel, it could be correctly formatted with notes, front page instructions and a glossary OR it could be have been put together by a digital Dr Frankenstein. Importing such a monster to a geodatabase can cause untold problems.

the problem...

I was approached recently as user had imported and Excel file into a geodatabase but then could not see the coordinates. They were there in Excel but had now vanished! The Null bells were tolling. Let's have a look at a few possibilities and their solutions:

1 field headings

Arc doesn't like fancy field (column) headings. Keep the number of characters low (I always aim for no more than 13). Don't use special characters (headings like [UTM* $East £] are too special). Don't use spaces. Underscores rule_the_roost here if you do need to separate words for readability.

2 empty cells

If the first few rows of the Excel sheet have empty cells in them this can confuse Arc. Arc reads the first few rows of a Excel sheet in order to assign data types. faced with empty cells it defaults to the text (string) data type which can lead to subsequent values not being read properly. This can be solved by sorting your datasheet to make sure the top few rows (I think it's the top 8 that count) are all filled with data. More drastically you could just delete rows with empty cells if that data is redundant or not important.

3 formatting

If the formatting is wrong in Excel it's going to cause problems in Arc. Dates in Excel to Arc are a whole new post but other irregularities can also cause problems. That was the case in this specific example so let's take a look at what went wrong. Here's a screen shot of the problem:

The green triangles in the top left corner of the cells indicate that the cell has a number stored as text. Excel can convert these cells for you. Just highlight all the cells in the column (NOT by clicking the field / column heading) and the yellow exclamation mark with a drop down appears. Ask Excel to fix the problem and we're one step closer.

Next highlight each column individually in Excel and check the properties. Ensure the formatting is set to something sensible.

All done? Now we can save the Excel file and get it into a geodatabase.

importing to an ArcGIS geodatabase...

First we need a geodatabase, so open ArcCatalog and navigate to your workspace, right click and create new geodatabase:

Now select your new GDB and right click it again to open the options and import>single table:

At this point a dialog box will open and you can choose which table to open. Navigate to your excel table and select it. You'll notice you can choose individual sheets other than the first one. There's some other things to fill out such as the name of the table once it's imported etc. As soon as you've selected the table to import, a list of fields will appear:

You'll notice in the example that the field or column [UTM_E_X] is showing a a (text) field. That's unlikely to be right for UTM coordinates. It's worth going back to your Excel sheet at this point and making sure that the formatting is right there. Check and double check, make sure you've saved it, make sure you're importing the right table. It may take several attempts to import a table successfully. If nothing is working and a (double) field is still showing as (text) then you can try to force it by changing the properties at the import stage. Select the field that is misbehaving and right click it, choose properties and change it to what you need:

After all that, your data should be imported correctly. Don't forget you should ALWAYS check tables after they are imported for any irregularities or weirdness.

If you're still having trouble or are having difficulty with another aspect of GIS contact us and we'll happily help you out.