Take the 2-minute tour ×
Geographic Information Systems Stack Exchange is a question and answer site for cartographers, geographers and GIS professionals. It's 100% free, no registration required.

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 :)
share|improve this question
    
have a look on this gis.stackexchange.com/questions/86369/… –  AndroidNijx Feb 13 at 7:30
add comment

9 Answers

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

share|improve this answer
    
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 '10 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 '10 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 '10 at 4:24
add comment

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.

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

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.

share|improve this answer
1  
If you use Python, you could run a geoprocessing tool from the command line. –  jswise Aug 2 '10 at 17:42
add comment

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.

share|improve this answer
    
Do I need to do anything with the top columns that are not part of the data? –  Zoran Aug 23 '12 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 '12 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 '12 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 '12 at 13:14
    
I've done this before, but not very often and just could not recall how to do it. Yes, the header. excel does give issues on many joins as that is the majority of the database most of my clients use. –  Zoran Aug 23 '12 at 13:58
add comment

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.

share|improve this answer
    
I'm actually tending that way - I'll use OGR instead of writing the shapefile directly though! –  fmark Jul 28 '10 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 '10 at 17:51
add comment

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

share|improve this answer
add comment

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.

share|improve this answer
    
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 '10 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 Barça Aug 3 '10 at 15:34
add comment

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

share|improve this answer
add comment

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

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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