5

I have an excel spreadsheet I want to convert to an ESRI shapefile programmatically. It contains X and Y coordinates in two columns, as well as a variety of attribute data in other columns. The spreadsheet is in excel 97 format (i.e. not .xlsx).

I would like to be able to convert this to a point geometry shapefile, with each row's x,y pair representing a point. Ideally, I would like to have a third column specifying the coordinate system of the x,y coordinate pair, and have the excel file contain heterogenous coordinate systems.

How can I convert this excel spreadsheet (.xls) to a shapefile programmatically? Preferably in Python, but other implementations will be accepted.

fmark
  • 57,259
  • 27
  • 100
  • 107

5 Answers5

5

something like this?

import xlrd
book = xlrd_open_workbook("data.xls") 
sheet = book.sheet_by_index(0)  
data = [] #make a data store
for i in xrange(sheet.nrows):
  row = sheet.row_values(i)
  x=row[0]
  y=row[1]
  data.append(x,y)

import point_store
point_store.save('points-shifted.shp', [data], '+proj=longlat +ellps=WGS84 +datum=WGS84 +no_defs')
ebt
  • 1,358
  • 1
  • 13
  • 18
4

There is a Python tutorial on creating a shapefile using GDAL here:

http://invisibleroads.com/tutorials/gdal-shapefile-points-save.html

You'll just need to replace the source data with the points from the Excel file - as Fabian pointed out there are libraries to read Excel files (or save it as a DBF).

Alternatively if you have ESRI's ArcMap, save the Excel as a DBF file (I can't remember if ArcMap reads Excel directly), and then add this DBF as an "Event Layer" using the X,Y fields to represent the points. ArcMap will display these as features and you can then right-click and export the layer to a shapefile.

geographika
  • 6,458
  • 4
  • 38
  • 56
2

xlrd is a python module for reading Excel file, I haven't used it myself tough.

John Machin
  • 81,303
  • 11
  • 141
  • 189
Mad Scientist
  • 18,090
  • 12
  • 83
  • 109
1

You may want the GDAL/OGR libs to do this with Python, and after you've installed those it's easier to just use the ogr2ogr utility as explained at http://nautilus.baruch.sc.edu/twiki_dmcc/bin/view/Main/OGR_example#Converting_from_CSV_to_shapefile.

sgillies
  • 2,231
  • 15
  • 9
  • I'd prefer not to go the CSV route because I'd like to maintain the datatypes expressed by the excel format string. – fmark Jul 08 '10 at 03:51
0

Arcmap support the Python for the library named arcpy. As we know, the Pandas works like the Excel and it can read and handle data easily. Yes, sometimes it can be use to export to the file of .xls and .xlsx . I coded a function of interconversion betweent DataFrame of pandas and the shp of Arcmap. It like this:

 def Shp2dataframe(path):

    fields=arcpy.ListFields(path)

    table=[]

    fieldname=[field.name for field in fields]

    data=arcpy.SearchCursor(path)

    for row in data:

        r=[]

        for field in fields:

            r.append(row.getValue(field.name))

        table.append(r)

    return pd.DataFrame(table,columns=fieldname)


 '''Fuction:

make the table of pandas's DataFrame convert to the shp of esri

Input:

df -- pandas DataFrame from the shp converted

outpath -- the shp output path

geometryType -- the type of geomentey, eg:'POINT','POLYLINE','POLYGON','MULTIPOINT'

temple -- the temple, at most time it is used the DataFrame's shp

'''
def Dataframe2ShpTemplate(df,outpath,geoType,template):
out_path = outpath.replace(outpath.split('/')[-1],'')

out_name = outpath.split('/')[-1]

geometry_type = geoType

feature_class = arcpy.CreateFeatureclass_management(

    out_path, out_name, geometry_type, template)


desc = arcpy.Describe(outpath)

if template=='':

    fields = set(list(df.columns)+['Shape','FID'])

    originfieldnames = [field.name for field in desc.fields]

    for fieldname in fields:

        if fieldname not in originfieldnames:

            arcpy.AddField_management(outpath,fieldname,'TEXT')

for row in df.index:

    df['SHAPE@'] = df['Shape']

    cursor = arcpy.da.InsertCursor(outpath,[field for field in df.columns])

    cursor.insertRow([df[field][row] for field in df.columns])

print 'Pandas to shp finish!'

del cursor
shikanon
  • 1
  • 1