How can I convert an excel spreadsheet (.xls) to a shapefile programmatically?

Question

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.

Answer 1

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

Answer 2

You can try and use VBA. Just iterate through all your spreadsheet records and append appropriate record into shape file. VBA have documentation you can refer to. I'll give you one short example how to access data in Excel workbook:

Sub test()
For Each c In Worksheets("Sheet1").Range("A1:A10")
    If c.Value < 0.001 Then
        c.Value = 0
    End If
Next c
End Sub

UPD.: File write code sample:

Sub test()
Pi = 3.14
Set fso = CreateObject("Scripting.FileSystemObject")
Set ts = fso.CreateTextFile("c:\vbafile", True)
ts.WriteLine "Hello from excel!"
ts.WriteLine Pi
End Sub

Answer 3

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.

Answer 4

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')

Answer 5

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.