topic Re: Adding Excel data to ArcMap in Data Management Questions

Adding Excel data to ArcMap

TheodoreRakel — Wed, 07 Dec 2016 18:36:46 GMT

I'm using Arc Map 10.2.1. I have an Excel spreadsheet with two sheets in it. One sheet I can bring into Arc Map without a problem and the other I cannot. They aren't much different. Here are some screen shots of the Excel sheets and the data in Arc Map. Does anyone know why the second sheet does not come into Arc Map properly? I've read the topics in Formatting a table in Microsoft Excel for use in ArcGIS—Help | ArcGIS for Desktop and can't see what is wrong.

Excel sheet 1

After bringing sheet 1 into Arc Map, here's the attribute table for Sheet1

Excel sheet 2

After bringing sheet 2 into arc map, here's the attribute table for Sheet2. Notice the fields are all null

Re: Adding Excel data to ArcMap

JoeBorgione — Wed, 07 Dec 2016 19:11:20 GMT

Any chance you could include a few rows of each sheet here so we can give it a try?

Re: Adding Excel data to ArcMap

TheodoreRakel — Wed, 07 Dec 2016 19:34:07 GMT

The excel file is attached.

Re: Adding Excel data to ArcMap

JayantaPoddar — Wed, 07 Dec 2016 19:49:10 GMT

The spaces in the Field Names (in Excel Sheet) may be causing the issue. Make sure there is no space in the Field Name (both before and after the FieldName text).

Re: Adding Excel data to ArcMap

Robert_LeClair — Wed, 07 Dec 2016 19:53:26 GMT

I'm working with ArcMap 10.4.1 and am able to bring in the data as an XY event layer. Question for you - what coordinate system are the numbers? State Plane? UTM? Any details would be greatly appreciated.

Re: Adding Excel data to ArcMap

JoeBorgione — Wed, 07 Dec 2016 19:57:28 GMT

That's downright weird. Using 10.3.1 I can open it at all as an xlxs, but I can as an xls, but that #2 work book is all null. Tried it in 10.0 and same thing.

I copied the contents of #2 into #1 and that worked. Beats me though....

edited moments later: Good Catch Jayanta- Didn't notice them!

Re: Adding Excel data to ArcMap

TheodoreRakel — Wed, 07 Dec 2016 19:58:54 GMT

NAD_1983_UTM_Zone_12N

Jayanta is correct, you can't have spaces before or after the field names

Re: Adding Excel data to ArcMap

MelitaKennedy — Thu, 08 Dec 2016 00:30:36 GMT

Just wondering, UTM using feet for the units, correct?

Thanks!

Melita

Re: Adding Excel data to ArcMap

TheodoreRakel — Thu, 08 Dec 2016 00:38:01 GMT

correct

Re: Adding Excel data to ArcMap

DEAF_PROBERT_68 — Thu, 08 Dec 2016 03:41:25 GMT

Yes, ArcGIS 10.3.1 does not like xlsx. So, I made a copy of it to .xls

Then Open ArcCatalog and I was able to view it.

Export it as a .dbf file and add it as NAD_1983_UTM_Zone_12N.

Open ArcMap and add the file to it.

You need to re-export it to shapefile.

I'm using ArcGIS 10.3.1.

Did you do this method ?

Re: Adding Excel data to ArcMap

TheodoreRakel — Thu, 08 Dec 2016 14:34:15 GMT

No I'm still using 10.2.1. What make it work for me was removing the spaces in the column headers, both before and after the column name. Then it worked.

Re: Adding Excel data to ArcMap

DEAF_PROBERT_68 — Thu, 08 Dec 2016 16:27:15 GMT

All righty, when you get the chance to upgrade 10.3.1 or later one day, you can always "post-it" sticker it on your computer or your head...Enjoy your day.

Re: Adding Excel data to ArcMap

CherylTrine — Fri, 09 Dec 2016 16:02:36 GMT

It is not entirely true that ArcGIS 10.3.1 doesn't like .xlsx

I am using ArcMap 10.3.1 and regularly use .xlsx without problems (except, or course when there are white spaces or other illegal characters in the column headings). Perhaps it is related to which version of Excel created the file; I have Excel 2013.

Re: Adding Excel data to ArcMap

JayantaPoddar — Fri, 09 Dec 2016 16:22:34 GMT

There has been connectivity issues with MS Excel 2007 onwards with ArcGIS Desktop which we used to resolve by installing 2007 Office System Driver: Data Connectivity Components

Didn't have a chance to try it lately though.

Re: Adding Excel data to ArcMap

TheodoreRakel — Fri, 09 Dec 2016 21:00:06 GMT

I have found one more subtle problem with bringing excel data into arc map. I can get the data added, create an XY layer from that data and save that layer out as a shapefile. However, when I export the data to a shapefile, some of the data is getting changed. I have the a column that is the customer ID in the excel data. Notice in the screen shots below how the 'customid' column changes values after the first row.

Data from Excel in arc map:

Data for the XY event layer:

Data for the exported shapefile

Notice how the customid of 1363830001 got changed to 1363830000 in the shapefile. And for every row after that.

Does anyone know how to keep this from happening when exporting an XY layer to a shapefile?

Re: Adding Excel data to ArcMap

JayantaPoddar — Fri, 09 Dec 2016 22:09:11 GMT

In the Excel sheet, change the datatype of "CustomID" field to TEXT (since it is unique and won't be used for any numeric calculation). Eventually, the ID values won't change when you convert it to Event Layer and Shapefile.

Re: Adding Excel data to ArcMap

TheodoreRakel — Fri, 09 Dec 2016 23:08:26 GMT

That helps, but it stores the customid in scientific notation once the value is over a certain size. Any idea how to prevent this from happening?

Re: Adding Excel data to ArcMap

TheodoreRakel — Fri, 09 Dec 2016 23:22:34 GMT

Sorry, my mistake. The excel spread sheet has these values in scientific notation. That's the problem.

Re: Adding Excel data to ArcMap

DEAF_PROBERT_68 — Tue, 13 Dec 2016 16:32:33 GMT

Here is what I found. I am doing the same thing here..

Understanding how to use Microsoft Excel files in ArcGIS—Help | ArcGIS for Desktop

Re: Adding Excel data to ArcMap

JayantaPoddar — Tue, 13 Dec 2016 16:46:46 GMT

Nice resource. Thank you for sharing.