10

I am preparing a course for QGIS newbies, which includes handling Excel spreadsheet data in QGIS.

It's so easy: Just drag an XLS, XSLX or CSV from Windows Explorer to QGIS!

For data with no coordinates, I teach them 1:1 joins to shape files to obtain positions, e.g. a place name. For data with coordinate attributes in the CSV, I teach them how to use the "Add Delimited Text Layer" function, and the same goes for Excel XLS files using XY Tools plugin.

The problem is XLSX files, which can easily be dragged and dropped into QGIS and displayed as attributes. But I haven't found a way to plot points from (correct) coordinate information - without previous conversion to XLS or CSV. I would like the operation to be smooth and inviting for the QGIS newcomers.

Question: How can you drag-drop an Excel XLSX file and plot the coordinates using the fewest steps?

4 Answers 4

12
  1. drag-drop an Excel XLSX file onto the layer widget
  2. double click tool QGIS geoalgorithms\Vector creation tools\Point layer from Table, choose the new layer as Input layer and specify the columns representing X- and Y-field, run the process

Two steps using current QGIS versions.

2
  • Thank you very much. This trick could probably help in a lot of day-to-day GIS tasks. May 21, 2015 at 17:38
  • However, you should bare in mind, that the process involves construction of a new shape file or a temporary layer detached from the original XLXS file, so updates in an XLXS file through Excel is not updated in the point output. Unlike the "Add Delimited Text Layer", which let you update the QGIS point representation of a CSV if you check the "Watch file" parameter. May 21, 2015 at 17:44
5

Use the "Add spreadsheet layer" tool, which can be installed with these steps:

  1. Plugins > Manage and Install Plugins...
  2. Search or look for "Spreadsheet Layers", then click "Install plugin"

After a successful installation, you can find the tool beside the other "Add Layer" options.

See more details at the Plugin's homepage

4

In QGIS 3 you can now use a virtual layer.

  1. (Same as answer from @Detlev) drag-drop an Excel XLSX file onto the layer widget
  2. Layer -> Create Layer -> New Virtual Layer
  3. Import and select the layer/table from Step 1
  4. Create a query to generate geometry (example in screenshot) e.g. SELECT *, MakePoint(Longitude, Latitude, 4326) as geometry FROM poi

I've only just done this for the first time but it does seem to update to reflect edits in the xlsx (after a little while).

screenshot of Create a Virtual Layer dialog to create point layer from xlsx table

0

Shortest way (for point layer in a CSV format with X and Y coordinates):

  1. ctrl+shift+t to access the Add Delimited Text Layer window and select your CSV file

  2. click all required boxes to rightly associate coordinates to X and Y columns

  3. you can also check attribute data type and headers, I find it very important since you cannot do this with the drag-n-drop option, just a little more clicking.

That's it, no plugins or tools needed.

For other geometries like linestring or polygon you probably need to use WKT format, there is a checkbox for it in the same window.

I personally use Virtual Layers as indicated by @iKenSpatial (no need to embed it if already in the layers pane I think) but it might require some little spatialite knowledge.

The Add Spreadsheet Layer plugin is OK but it adds a .vrt file to the same directory, while CSV files are totally transparent and can be edited on-the-fly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.