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

Question

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

• AndroidNijx: have a look on this gis.stackexchange.com/questions/86369/…

Answer 1

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

• fmark: 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 :)
• scw: 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.
• spg: 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/…
• matt wilkie: Online service to help build a properly formatted vrt file: vrtbuilder.appspot.com
• Alireza S.N: I tried this with an xls file without converting to csv and it worked very well. Seems like it isn't necessary to convert anymore.

Answer 2

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.

• fmark: I am often plagued by parser errors using this tool. Any advice?
• gsherman: Do you have specific examples?
• dassouki: @fmark - I get these errors as well, and its usually due to MS Excel handling the csv

Answer 3

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.

• jswise: If you use Python, you could run a geoprocessing tool from the command line.

Answer 4

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.

• Zoran: Do I need to do anything with the top columns that are not part of the data?
• PolyGeo: 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: 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.
• Chad Cooper: 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.
• matt wilkie: 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.

Answer 5

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.

• fmark: I'm actually tending that way - I'll use OGR instead of writing the shapefile directly though!
• mwalker: 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.
• user30184: Excel does not write dbf files out.

Answer 6

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

Answer 7

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.

• fmark: 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....
• John Powell: 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.

Answer 8

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

• user30184: 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.
• TheSteve0: 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

Answer 9

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