[GIS] Add an Excel file with a list of coordinates to QGIS as a layer

Question

I've added delimited text layers to QGIS before with columns consisting latitude and longitude separately. But Now I have an Excel file like:

station no parcel no    parcel coordinates 
63         215          X1,Y1; X2,Y2; X3,Y3; X4,Y4

So I have all the parcel coordinates in one column for every parcel. I need to add this data with parcels to my previous shapefile with station no data. But I couldn't figure out how to import this coordinates to QGIS.

Answer 1

You can often make a WKT (Well Known Text) column in Excel without too much fiddling, which effectively creates a spatial definition for points, lines or polygons within a single field.

What you want to do is create a WKT string, in the format:

POLYGON((X1 Y1, X2 Y2, X3 Y3, X4 Y4, X1 Y1))

You can create new columns in Excel with the below formulas. The first ('clean') substitutes the commas for spaces, then the semicolons for commas; the second ('wkt') creates a WKT string (concatenating text with &) - the only tricky part is finding and repeating the first coordinate pair to close the polygon:

• clean: =SUBSTITUTE(SUBSTITUTE(B2, ",", " "), ";", ",")

• wkt: ="POLYGON(("&C2&","&MID(C2,1, FIND(", ",C2)-1)&"))"

This can be saved as CSV, and the WKT field selected for import.