topic Re: Excel File in Data Management Questions

Excel File

ABDALLAMOHAMED — Fri, 27 Mar 2015 03:31:12 GMT

hello ..I have my data in excel file and I wanted to convert it to points, add xy coordinates and finally create a shape file.. I need your help please...Considering this is my first time to deal with this type of data.

Thanks a lot

ABDALLA

Re: Excel File

JayantaPoddar — Fri, 27 Mar 2015 04:40:03 GMT

Hi,

1) Convert XY table to XY event layer

Choose appropriate spatial reference (e.g. GCS_WGS_1984)

2) Export the XY event layer to feature class.

Re: Excel File

ABDALLAMOHAMED — Fri, 27 Mar 2015 12:34:03 GMT

Jayanta..Thanks for your reply. I don't have xy table, but I have excel file with crime data in 10 counties. My question is how to add xy coordinates and convert it to a point.

Thanks a lot

Re: Excel File

VictorKlinkenberg — Fri, 27 Mar 2015 12:41:12 GMT

Hi Abdalla,

What is available in your table - only county names? if that is the case, see if you can find the coordinates for the counties and add them to the table (one column x one column y).

perhaps give an example of your data?

Re: Excel File

JayantaPoddar — Fri, 27 Mar 2015 12:53:05 GMT

You can use the excel sheet as a XY table. XY table is just a generic term.

Re: Excel File

ABDALLAMOHAMED — Fri, 27 Mar 2015 12:55:06 GMT

Hi Victor..I have crime data from 10 counties in Colorado. The table only has years and types of crime e.g murder, homicide etc. it doesn't have any xy coordinates. I'm attaching a sample with this message

Thanks a lot

Re: Excel File

ABDALLAMOHAMED — Fri, 27 Mar 2015 12:58:52 GMT

ok let me try it and see

Re: Excel File

VictorKlinkenberg — Fri, 27 Mar 2015 13:17:54 GMT

Hi Abdalla,

I have had a look at you table, I have rearranged it a little, have a look at the attachment. simple table, no white spots, no spaces in column names, extra field county. if you add all other records you have to this table you can import it as table in arcmap. then either link it to a shapefile or add x y info of every county.

I have added the new xls and a shapefile (derived from Colorado.gov: The Official State Web Portal) - polygon - there are tools to convert it to points.

Hope this works

Victor

Re: Excel File

ABDALLAMOHAMED — Fri, 27 Mar 2015 13:45:40 GMT

Hi Victor...Okay, I'm going to add all other records, import it to ArcMap, then joint it to a shapefile. I will let you know about the result.

Thanks a lot for your help.. Really appreciated

ABDALLA

Re: Excel File

VictorKlinkenberg — Fri, 27 Mar 2015 14:44:33 GMT

just in case you aren't sure how to proceed (previous posts were rather desperate):

download the data i sent to new folder

rename the excel file and the excel sheet so that it doesn't contain spaces or other weird symbols. just use letters, numbers and "_"

open arcmap

button add data - select shapefile 'colorado counties'

button add data - select 'local crime...' excel file - the first sheet

you should see the colorado counties as polygons. right click in table of content, - open table

in left upper corner button - menu - add field, name it X and type = float

again add field, name it Y and type= float

again add field, name it "name_short" or similar

in table view, right click X - calculate geometry - x centroid

in table view, right click Y - calculate geometry - Y centroid

in table view, right click "name-short" field, - calculate field: vb script fill in: Left( [Name], (Len( [Name] )-7 )) - this will result in a field with only "pueblo" as name rather than pueblo county

in arcmap tabel of content: rightclick your excel table - join - use colorado counties name short and excele sheet name field to join.

right click tabl in table of content - display x y data, using x and y fields which you can select.

voila: one point for every county every year with data appended.

you could also choose to append the table data to the shapefile of county polygons - prettier pictures

remember though there is one polygon per county and there is a large number of county/year records.

Re: Excel File

ABDALLAMOHAMED — Thu, 02 Apr 2015 18:52:56 GMT

Thanks a lo victor I got it to work with me..appreciated

ABDALLA