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:

enter image description here

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

Answer 2

There are also some plugins for QGIS that provide data import through Excel files and they permitt some customization of input format. You can check MMQGIS or XYTools, these two I've worked with and perfom excellent. Some more might be available.