26

How can I convert an excel file with x, y columns to a point shapefile?

There are some somewhat optional requirements in addition to the correct creation of a shapefile:

  1. Column types (as per Excel's format specifiers) should be retained (especially date types)
  2. Column names should be taken from the header
  3. I would like to do this from the command-line
  4. If I can include heterogeneous spatial references for the points in a third column I would be very happy :)
Improve this question
1

9 Answers 9

Reset to default
24

I'd recommend using OGR/GDAL, which is part of the GDAL library. OGR supports a virtual format which allows specification via an XML file. If you convert your Excel worksheet into a CSV, you can generate a VRT to access the data.

Assuming you have something like this example.csv:

Lat,Long,Year,Name
34.0,-120.0,2010-05-01,Off Santa Rosa Island

You can create a VRT example.vrt as follows:

<OGRVRTDataSource>
    <OGRVRTLayer name="example">
        <SrcDataSource>example.csv</SrcDataSource>
        <GeometryType>wkbPoint</GeometryType>
        <LayerSRS>WGS84</LayerSRS>
        <GeometryField encoding="PointFromColumns" x="Long" y="Lat"/>
    </OGRVRTLayer>
</OGRVRTDataSource>

Starting in GDAL 1.7, you can additionally specify the datatypes of attribute fields using the <Field> element inside of <OGRVRTLayer>, like so:

    <Field name="date" src="Year" type="Date" />

Keep in mind that shapefiles store attributes in the DBASE IV format which has less flexibility in data types than Excel. Once you've got your VRT file specified, you can use the normal OGR toolchain to convert the data into a Shapefile:

ogr2ogr -f "ESRI Shapefile" example.shp example.vrt

Unfortunately, #4 is not possible — the shapefile specification allows a single projection (viewable here in example.prj after the last step).

Improve this answer
4
  • The only down-side of this is that I have to figure out the data-types of the columns programmatically in order to write the <Field> tag. I guess I'll figure that one out on my own :)
    – fmark
    Jul 26, 2010 at 1:33
  • Right. If you want an automated solution and have access to ArcGIS, you can do this with the Add XY Coordinates tool tinyurl.com/38jm55k instead, which should do that step for you.
    – scw
    Jul 26, 2010 at 2:00
  • An important consideration is getting your Excel file into a format suitable for ESRI shapefile first - there are limitations on column names, the length of names and if you're using the Add XY tool the way in which ESRI automates the field type which could cause issues. webhelp.esri.com/arcgisdesktop/9.2/…
    – spg
    Jul 26, 2010 at 4:24
  • 3
    Online service to help build a properly formatted vrt file: vrtbuilder.appspot.com
    – matt wilkie
    May 25, 2015 at 23:15
9

You could use the Delimited Text plugin that comes with QGIS (http://qgis.org) to load the text file and then save it as a shapefile.

Improve this answer
3
  • I am often plagued by parser errors using this tool. Any advice?
    – fmark
    Jul 27, 2010 at 0:12
  • 1
    Do you have specific examples?
    – gsherman
    Jul 27, 2010 at 1:18
  • @fmark - I get these errors as well, and its usually due to MS Excel handling the csv
    – dassouki
    Jul 27, 2010 at 11:49
6

Although not from the command line, you could do this within ArcGIS App. Since 9.3.1, just add the XLS directly into ArcMap. Make an XY Event Layer from the XLS (i.e. pick the X, Y Columns and set ur spatial reference) Then export this XY Event Layer to a new .shp This could easily be automated with a simple python/VBA script or a model in modelbuilder. Happy to help on this basic workflow if you need more info.

Improve this answer
1
  • 1
    If you use Python, you could run a geoprocessing tool from the command line.
    – jswise
    Aug 2, 2010 at 17:42
6

I looked for but could not find a help page on Display XY Data in the web help but the process is simple.

  1. Use Add Data (or drag and drop from Catalog window) to browse for your worksheet (within the spreadsheet) and add it to your map
  2. Right-click to choose and use Display XY Data to add an event layer
  3. Right-click on the event layer just created and use Data|Export Data to create a shapefile

You only need ArcView (Basic) license level of ArcGIS (for) Desktop to do this.

Improve this answer
6
  • Do I need to do anything with the top columns that are not part of the data?
    – Zoran
    Aug 23, 2012 at 4:22
  • Which two columns are not part of your data? The procedure I described should use whichever long/lat columns you choose to generate the point geometries and then, I think, store the rest as attributes of them. Best thing is to try it and if result is not as expected, then describe your observed vs expected.
    – PolyGeo ♦
    Aug 23, 2012 at 4:40
  • Think I misread top for two - and am assuming you mean the top row (of headings). They should just become field names after the Add Data.
    – PolyGeo ♦
    Aug 23, 2012 at 4:50
  • Not sure if this is still the case, but spaces in the header names in the Excel file used to be a real headache for Add XY.
    – Chad Cooper
    Aug 23, 2012 at 13:14
  • 1
    Loading Excel files directly into ArcGIS works wonderfully well -- except when it doesn't! Things to watch for: dates mangled, numbers truncated. First troubleshooting step is always to export to CSV with all columns defined as text and load that instead to see of problem perseveres. That caveat aside. Display as X/Y works very well.
    – matt wilkie
    May 25, 2015 at 23:13
4

There's always the adventurous option. You could write the shape file yourself. ESRI publishes the spec for shapefiles, which you can read here:

http://www.esri.com/library/whitepapers/pdfs/shapefile.pdf

You would prob also need to write a DBF file. You can read about the format here:

http://www.clicketyclick.dk/databases/xbase/format/dbf.html#DBF_STRUCT

This is undoubtedly overkill, but it would give you the advantage of having control over the whole process.

Improve this answer
3
  • I'm actually tending that way - I'll use OGR instead of writing the shapefile directly though!
    – fmark
    Jul 28, 2010 at 0:28
  • 2
    Excel can probably write the DBF directly, although I've had varying levels of success using those DBFs as the DBF component of a shapefile.
    – mwalker
    Jul 28, 2010 at 17:51
  • Excel does not write dbf files out.
    – user30184
    Jun 16, 2014 at 12:33
3

I personally prefer using FME from Safe Software (http://www.safe.com), which not only lets me plot points from XY values, but provides me with the flexibility to transform the coordinates from one projection to another before plotting, specify the type of delimiter, starting line (in case the text file has header information), attach attributes in case the text file have X,Y, Attribute information for the point data. In case you like to visualise this data in Google Earth you can write them to KML format while specifying the KML point style as well. ~SRG

Improve this answer
2

If you want to do some other processing on the x, y data, you could load the data into postgres, use geomfromtext with concatenation to create a geometry data type and then use pgsql2shp to export to shape. This approach will also allow you to retain heterogeneous spatial reference system data in another column, which you could then use to convert all your points into a common reference system within the db before exporting to shape. I realize that this involves more steps than ogr/gdal but it will give you a lot more flexibility.

Improve this answer
2
  • I've used this approach in the past, and it's a good one. I think my consistent stumbling block here is detecting the excel data types reliably....
    – fmark
    Aug 3, 2010 at 2:19
  • 1
    I suppose you could write something in excel vba to rearrange your columns first so that date, x, y were always the first 3 columns. Without some kind of column naming conventions, I think it would be difficult to completely automate what you have in mind, though.
    – John Powell
    Aug 3, 2010 at 15:34
1

What about loading your CSV files into spatiallite and then using OGR or QGIS to export to a shapefile.

http://www.gaia-gis.it/spatialite-2.2/index.html

You can even export from spatialite into a shapefile directly.

http://www.gaia-gis.it/spatialite-2.2/spatialite-2.2_tutorial.html#t3.1

Improve this answer
2
  • Spatialite can import Excel files directly if they are of the old .xls format (Excel 97-2003). X and Y coordinates must be converted inte geometries with SQL after initial import.
    – user30184
    Jun 16, 2014 at 12:36
  • If you don't mind using Java you could use the POI project to rip apart the excel files and then use GeoTools to write the new shapefile
    – TheSteve0
    Jun 25, 2014 at 21:18
0

For a more basic transformation you can use the 2Dpointreplacer transformer in FME 2013. You need to point in which columns the X and Y values are located and then FME will transform these values into for instance a shape file with points.
Column names and types can be set as required

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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