4

I have an Excel document with lots of coordinates in the following format:

lat: 2.03° S
lon: 80.00° W

Is it possible to import this data directly into ArcMap or do I have forcefully to transform it into pure numeric before loading it?

  • can you use python? – jbalk Dec 21 '17 at 6:00
  • @jbalk yes sir. i can. But i would prefer something in the user interface (like a tool or a clickable option) – user3357358 Dec 21 '17 at 15:10
8

You can do it using python. Add your excel to ArcMap, modify the #-lines and then execute code below in the Python window. Im using the Regular expressions (re) module to find the float part (for example 2.1) of each coordinate and a Dictionary to translate W to -1, E to 1 and so on.

import arcpy, re

temp = r'in_memory\excel'
outlayer = 'out_lyr'

sp = arcpy.SpatialReference(4326) #Change EPSG code
latlon = ['lat','lon'] #Change to match the names of lat and long fields in excel
sheet_name = "Blad1$" #Change to match the name of excel sheet

arcpy.CopyRows_management(sheet_name, temp)

coords = ['X','Y']
for field in coords:
    arcpy.AddField_management(in_table=temp, field_name=field, field_type='DOUBLE')

d = {'W':-1,'E':1, 'N':1, 'S':-1}

with arcpy.da.UpdateCursor(temp,coords+latlon) as cursor:
    for row in cursor:
        row[0] = d[row[2][-1]] * float(re.findall("\d+\.\d+", row[2])[0])
        row[1] = d[row[3][-1]] * float(re.findall("\d+\.\d+", row[3])[0])
        cursor.updateRow(row)

arcpy.MakeXYEventLayer_management(table=temp, in_x_field='X', in_y_field='Y', 
                                 out_layer=outlayer, spatial_reference=sp)

If you want to make the layer permanent copy it to disk.

Input: enter image description here Output: enter image description here

2

With the data you have you will want real numbers only.

You will have to remove the letters and multiply values by negative one where appropriate and also remove the degrees symbol.

1

Found it!

http://pro.arcgis.com/en/pro-app/tool-reference/data-management/convert-coordinate-notation.htm

This tool allows one to convert coordinates that contain letters (even those fearsome degree symbols). Thanks everybody for your help

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.