How to create a polygon from Excel coordinate file

Question

Currently I have an Excel file with 2 columns.

• area number.

• coordinates.

The coordinate column contains several X and Y points those are seperated with a comma. Next an example how the points are noted:

5.72567034 -3.61719203,5.72567034 -3.61719203,5.72565126 -3.61720133,5.72562551 -3.61721635,5.72560167 -3.61723351,5.72557211 -3.61725235, etc.

point 1: 5.72567034 (longitude) & -3.61719203 (longitude) {,}

point 2: 5.72567034 -3.61719203,

point 3: 5.72565126 -3.61720133,

I want to extract this data to ARCMap 10.6 and make polygon shapefiles out of it. The idea is to extract a polygon from points (points to polygon). but I am not able to create points out of these file. Because all the points of one area are in the same cell. When I try to put every point in one cell by doing in Excel "text to column" it exceeds the width of the worksheet (meaning >256 points per area). And thus by doing this I would lose some data. Then there is this problem that there are over 1000 areas of which this process should be done so manually is not an option.

Does anyone have a solution?

Answer 1

You can just edit your coordinate field by putting POLYGON(( at the beginning and )) to the end of each entry. Afterwards you can save it as *.txt file and use the 'import layer from textfile' function of QGIS to import your polygons as Well Known Text (WKT). From there you can save them as Shapefiles etc. You have to make shure, that the last and the first coordinate pair are the same to generate a valid polygon.

I guess Arcmap has also importfunctions for WKT.

Answer 2

I would first bring the file into a text editor which can find and replace special characters (e.g. Not Windows Notepad. Try Notepad ++ or other good editor). I'd replace all the commas with carriage return. This would put all the coordinate pairs on a separate line. I'd then replace the spaces by either a tab stop (for reading in Excel) or a comma (for saving as a CSV). In your first column (column A) I'd probably put a common ID for all coordinate pairs of one polygon once I brought it back into Excel. And as Julian pointed out above, make sure your starting and ending coordinate pairs for each polygon are the same.

Once the table is cleaned up it can be brought into your GIS software.