Convert data from ArcGis 10.5 to Excel


How to convert Excel to GIS data in QGIS using Spreadsheet Layers plugin

How to convert Excel to GIS data in QGIS using Spreadsheet Layers plugin

Let’s say you have some survey of Parks, and the data is in Excel format, for example, because it was done by non-GIS people. You need to take a quick look, to see if everything is correct, maybe even make a basic map, without creating more files, and leave it available for non-GIS people to edit. You can use the “Spreadsheet Layers” plugin to convert Excel to GIS in QGIS, creating spatial data from X and Y fields and even use some of the basic tools you could use in a shapefile for example. The advantages are that you don’t need to replicate the same data over and over again, and it’s more accessible to non-GIS folks.

Step 1:

Open an empty project. Click on Plugins menu, then Manage and Install Plugins…

Step 1 b

Type “spreadsheet”, and install the plugin “Spreadsheet Layers”.

Step 1 c

As you can see a new button is added to Add Layer toolbar.

Step 2:

Check your excel data before importing to QGIS. Then close it, the file cannot be opened at the same time.
TIP: For a better performance start the table in the first row and column.
Step 2 d

Step 3:

Step 3 e
Add spreadsheet layer, browse until you find your excel file. As you can see some fields were filled, other are empty.

Step 3 g

Now there are some configurations to be made:
1. Change the Layer name to “Parks”
2. Activate End of file detection (It inserts the last row with data, avoiding empty rows in the layer)
3. In our case the data has Geometry, so activate it. In some cases, we could import non-spatial data, for example for joining to spatial layers
TIP: Usually it recognizes the X and Y fields, but make sure it’s correctly chosen.

Step 3 h
4. Activate Show fields in attribute table so the coordinates will appear on the layer
5. Choose the correct Reference System

Step 3 i
6. Choose the correct field data type for each column (coordinates can be left as String), then OK. A spatial layer is created on QGIS.

Step 3 j
TIP: It’s very important that you define the correct field data type, if you leave a numerical column as a string, you won’t be able to use that column for calculations or graduate styling.

Step 4:

Now you can add a basemap, and manage the created layer. I have created a label based on the Park name and created a graduated style (size method) based on the number of trees in each park.

Step 4 o

If you want you could convert to a shapefile, import into some spatial database or just keep the data in Excel format. Some of the options selected are kept in the virtual layer created in the same folder as your excel file. I used as example a Park survey, but it could be used in many other subjects. Enjoy!


Author: Adelcides Varela