topic Re: Excel XY Data to Shapefile in Data Management Questions

Excel XY Data to Shapefile

VicenteSolis1 — Wed, 26 Oct 2016 17:20:03 GMT

I have a question about Excel XY Data to Shapefile. The Excel file I am working with has over 100,000 points, each with its own ID and lat/long. When I convert the data to a shapefile, the shapefile does not show all information. The data is crash data and for example, the IncidentID column's data is deleted when it converts to a shapefile.

Any ideas how to fix this? Thank you.

Re: Excel XY Data to Shapefile

DarrenWiens2 — Wed, 26 Oct 2016 17:23:26 GMT

Lots of potential problems, but most common is spaces or other special characters in field names. Remove them in Excel and try again.

Re: Excel XY Data to Shapefile

VicenteSolis1 — Wed, 26 Oct 2016 17:27:36 GMT

The column that is was talking about is labeled "IncidentID", no special characters in it.

Re: Excel XY Data to Shapefile

DarrenWiens2 — Wed, 26 Oct 2016 17:34:18 GMT

The field names must be good for all columns, not just the misbehaving one. In any case, see here for more: Formatting a table in Microsoft Excel for use in ArcGIS—Help | ArcGIS for Desktop

Re: Excel XY Data to Shapefile

VicenteSolis1 — Wed, 26 Oct 2016 17:35:08 GMT

Problem solved, got it to work by saving it as a CSV file.

Re: Excel XY Data to Shapefile

VicenteSolis1 — Wed, 26 Oct 2016 18:01:45 GMT

Darren,

I have another question now. The CSV worked fine, a majority of data transferred without issue. However, the data/time data didn't show up. In Excel, the information is in m/d/yyyy h:mm. Does GIS not accept this?

Re: Excel XY Data to Shapefile

DarrenWiens2 — Wed, 26 Oct 2016 18:04:20 GMT

Hmmm... shapefiles definitely don't support the time component of date/time, but I would have guessed that it could parse out the date component. Either try exporting the CSV point data as a GDB feature class (which do support date/time) or parse out the date in Excel and try again, exporting to shapefile.

edit: on second read, does the date/time not show in CSV file when you bring it into ArcMap?

Re: Excel XY Data to Shapefile

DanPatterson_Retired — Wed, 26 Oct 2016 18:06:11 GMT

Assuming the information was transfered in the csv file properly, if the destination output file was to a shapefile, no... if it was to a geodatabase, yes... Check the help topics on date and time for your version of ArcMap

Re: Excel XY Data to Shapefile

VicenteSolis1 — Wed, 26 Oct 2016 18:07:46 GMT

It doesn't show, it shows as Null.

Re: Excel XY Data to Shapefile

VicenteSolis1 — Wed, 26 Oct 2016 18:09:05 GMT

I did transfer the point data straight to a geodatabase, that's where it showed up as Null.

Re: Excel XY Data to Shapefile

DanPatterson_Retired — Wed, 26 Oct 2016 18:16:58 GMT

the date time format requirements and settings are in the help file.. This is for 10.4

Fundamentals of date fields—Help | ArcGIS for Desktop

Re: Excel XY Data to Shapefile

VicenteSolis1 — Wed, 26 Oct 2016 18:20:38 GMT

I am working on 10.2.2, would that be the issue?

Re: Excel XY Data to Shapefile

DanPatterson_Retired — Wed, 26 Oct 2016 18:24:14 GMT

You can check that help file to see if there have been any changes. Go to...

Documentation | ArcGIS for Desktop

Look towards the bottom left for access to online help for legacy versions of arcmap

Re: Excel XY Data to Shapefile

DavidSemitekol3 — Fri, 28 Oct 2016 13:39:17 GMT

Hello Vicente,

Maybe I can help. Excel stores dates in a serial format so often and almost always these dates do not transfer over into ArcGIS. One solution is to split the date into three columns in Excel before exporting it to a CSV and importing it into Arc. You can reference the workflow here https://www.extendoffice.com/documents/excel/3267-excel-split-date-into-day-month-year.html

Also, if you have lat/long in your CSV file it is good practice to first display the x/y data and then export the data into a feature layer in a geodatabse or create a shapefile. I have found that this often activates more functionality with the layer.

If you are not sure how your date is being stored in Excel you can test it by clicking on the cell with the date and then clearing the formatting. If the date turns into numbers then it is being stored as a serial date.

Re: Excel XY Data to Shapefile

VicenteSolis1 — Fri, 28 Oct 2016 14:39:46 GMT

Thanks David. I'm working throug it here and there between projects and one thing I have found is if I add a table directly to the geodatabase in ArcCatalog, everything seems to show up just fine. I'm assuming I can use the geodatabase table to create XY data and turn into a feature class, correct?

Re: Excel XY Data to Shapefile

DavidSemitekol3 — Fri, 28 Oct 2016 14:48:04 GMT

Yes, you should be able to. Right-click on the table from your TOC and select "Display XY data". Just remember to make sure that you have a coordinate system defined in the options window. I've found that WGS84 is a good standard.

Also take note that sometimes it is better to start with a fresh document with no other layers. Add just the table and then display the XY data and then export it to either a shapefile or a geodatabase. Then you can open your original working document and add the new layer. Sometimes there is a bug in the system that prevents the XY data from being displayed correctly if there is another coordinate system being used in your current document.

Re: Excel XY Data to Shapefile

VicenteSolis1 — Fri, 28 Oct 2016 14:51:15 GMT

Yup, I've noticed those bugs before. I'll give it a shot today and hopefully have it all worked out.

Re: Excel XY Data to Shapefile

TedKowal — Fri, 28 Oct 2016 14:55:11 GMT

Speak of the devil..... i have just received last years crash data from the state.... it is in an excel spread sheet as with every year.

After getting frustrated with excel, I simply import the data into an empty personal geodatabase and then create a feature or feature class from the access table..... a lot less of a hassle and keeps the data intact!

-- aside--

If your crash data is coming from your state DOT -- generally there is LRS information within the data ie the Route and Milepoint. If you obtain the state route (LRS) line work you can event map the crash data directly with the route number and milepoint........ which makes life a heck of alot easier!

Re: Excel XY Data to Shapefile

VicenteSolis1 — Fri, 28 Oct 2016 15:01:58 GMT

Ted,

Good info to know.