Excel XY Data to Shapefile

3976
18
10-26-2016 10:20 AM
VicenteSolis1
New Contributor

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. 

0 Kudos
18 Replies
DanPatterson_Retired
MVP Esteemed Contributor

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 

0 Kudos
VicenteSolis1
New Contributor

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

0 Kudos
DanPatterson_Retired
MVP Esteemed Contributor

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

DavidSemitekol3
New Contributor II

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.

VicenteSolis1
New Contributor

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? 

0 Kudos
DavidSemitekol3
New Contributor II

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.

VicenteSolis1
New Contributor

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

0 Kudos
TedKowal
Regular Contributor II

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!

0 Kudos
VicenteSolis1
New Contributor

Ted,

Good info to know. 

0 Kudos