5

There seem to now be many methods for working with an Excel spreadsheet in QGIS. Some allow read-only, and some require one Excel format or another. Some also have historically been unreliable (e.g. where trivial changes to the Excel sheet break the QGIS project).

What are the main different methods for working DIRECTLY with Excel at QGIS version 2.18?

Clearly there are more convoluted methods - Excel data can be exported and then imported to many other formats (e.g. csv), but I'm asking about more direct methods of making a direct link with the original Excel file (so changes are seen in QGIS). Often other people wish to maintain live data in Excel - and GIS professionals need (if being efficient) to maintain live links to that live data.

I (think I) know of the following (but have questions about each). I suspect that the best answer will simply list the basic facts about each of the various methods - these and others.

  1. Save using older .xls format. Bring into QGIS using a virtual xls table in a Spatialite database. This I think results in a read only table but it is possible to define that the first row has column headings.
  2. Save using .xlsx format. Drag into QGIS or open using 'add vector layer'. No easy way to define if top row is column labels.
  3. Use the 'Spreadsheet Layers' plugin. It is possible to define a header row, and x/y data. The plugin works by creating a VRT file which sits in parallel to the original file. Presumably this VRT file could be moved (if the file reference in this VRT file continues to point correctly at the original file). The VRT file records details about the spreadsheet (data types etc).

In all cases the header row data (i.e. field names) in Excel needs to be handled carefully - odd characters (etc) can break things.

I realise that this question doesn't ask for a simple 'how to' answer, but working with Excel spreadsheets is trivial in some software so I believe that some clear comments about the facts about the various methods are justified. The information above is incomplete.

Improve this question
  • What do you want to import from excel? – Gerardo Jimenez Jul 28 '16 at 17:51
  • Personally just simple text and numerical data in a simple spreadsheet. No geometries, but I know others DO need geometries. I'm not sure it makes much difference does it, aren't there ways to create geometries from any x,y columns in all cases? If ability to handle x,y data columns varies then this should be mentioned. – Rostranimin Jul 28 '16 at 17:57
  • 1
    Check the XYTools plugin for QGIS , but are you looking to use QGIS to edit records / values in your Excel file? – DPSSpatial Jul 28 '16 at 17:59
  • For geometry or a database you can use excel and saving the file in csv format. Simple geometries like points are truly easy and there are many questions that handle the problem, for instance gis.stackexchange.com/questions/41962/…. The case of databases you have to include a csvt file that describes the database structure. This file is not hard to make it, since you can use a text editor to do it – Gerardo Jimenez Jul 28 '16 at 18:06
  • 1
    Thanks Gerado, but see the third paragraph of my question... I'm interested in direct links to Excel, not export of data from Excel with import into another system (which is indeed one option). There are many reasons why a direct link can be a better option - e.g. where the Excel spreadsheet is a live document that non-GIS staff have access to.. – Rostranimin Jul 28 '16 at 18:39
0

This is also more of a comment than an 'answer' per se.

The Spreadsheet Layers plugin is the best approach I have found with using Excel and QGIS - it does allow for live updates of the .xlsx itself (you don't need to modify the .vrt).

However, it does have limitations: I notice if you have unexpected symbols and formatting in the header area in Excel, and certainly if you start adding/moving/deleting columns, the way QGIS interprets the columns can become corrupted (so a column name might actually be a date or be in the wrong location compared to before...)

I would recommend having your data in Excel formatted as an Excel table to minimise this issue.

(Conversely, FYI, Spreadsheet Layers lets you edit the .xlsx data directly in QGIS but this will erase all formatting.)

The option we use at work is .csvs - though again Excel rears its ugly head in formatting some things even when you don't want it to (e.g. the value 8-12 gets interpreted as a date)

I think it is fair to say the reason why there is no easy, foolproof way to plug Excel spreadsheets into QGIS is that Excel spreadsheets are not databases - it can become a real spaghetti of inconsistent data and all sorts of formatting junk, not forced to be laid out in the standard row/column format of relational databases.

And GIS data needs that consistency.

Improve this answer
  • Can you elaborate on "I would recommend having your data in Excel formatted as an Excel table..." - I'm not clear on your meaning. Thanks. And by "lets you edit the .xlsx... but this will erase all formatting" I'm assuming you mean "erase all formatting in the spreadsheet" - meaning it returns things to plain fonts and colours? (That's just about clarity of words). CSV is clearly a useful way to work - but the problems it introduces at the (non-specialist) Excel user's end are too great so I'm seeking native-Excel solutions. Comment about consistency noted. – Rostranimin Nov 30 '18 at 13:06
  • Excel table: the tabular data is formatted and stored as a table object in Excel support.office.com/en-us/article/… Not at a computer right now but I believe you can find it under Insert > Table. Obviously you only want the one table per sheet, starting from A1. as for erase all formatting, yes it takes out nearly everything: fonts, colours, row width, possibly even column width. Another thing I neglected to mention is that it seems more likely to act up with workbooks that have multiple sheets, but hopefully that's improved – she_weeds Nov 30 '18 at 20:39
  • That's brilliant - I feel like we're working toward some answers now. This information (about the capabilities and limitations of Spreadsheet Layers) is clearly part of the bigger answer. Thanks. – Rostranimin Dec 1 '18 at 11:30
0

There is now a plugin which allows a Google sheet to be read by QGIS and written to. Point data only as far as I know. The merit of adopting Google Sheets is that editors are identified (if users are controlled by login) and it always the current version that is online.

Improve this answer
  • Could you tell the name of the plug-in please? – Kantan Jan 17 '19 at 10:21
  • Thanks Bill. Is this the 'GooGIS' plugin? github.com/enricofer/gdrive_provider – Rostranimin Jan 17 '19 at 10:29
  • @Rostranimin that looks like it, I can't recall if I have used it. Reading the intro, if it does what it says, should be good. Enjoy. – BJW Jan 22 '19 at 8:55
0

In the comments you say you need the attribute table to be constantly edited by non-GIS people. In a shapefile the attribute table is an independent document with a .dbf extension. So one way to implement this would be allowing people to edit a shapefile's DBF.

Now, Excel used to be able to handle DBFs, but it can't even open them anymore. Nevertheless, there's a very similar spreadsheet software that works with DBFs, LibreOffice's Calc (LibreOffice's spreadsheet editor). I assume there are others, but Calc's what I usually use to edit DBFs. For several reasons, the most prominent being it's really easy (interface is almost the same as Excel's before 2007, has the same functions libraries, etc), and it's free/open software.

To me, editing data this way is extremely easy, specially when you have to handle huge amounts of data and editing the table in QGIS is so cumbersome it's not an option.

I find this capability very useful. Mainly that's why I'm still using shapefiles (and for portability). Of course, this works only with SHPs (at least that I'm aware).

Bear in mind: a DBF should be carefully edited so as to not disrupt its configuration (titles, amount of rows/columns, etc.).

Another possibility: maybe you could have the staff work with an Excel spreadsheet and then save it as the shapefile's DBF.

Improve this answer
  • Thanks. Useful ideas there (personally I'd prefer to convert Excel users LibreOffice Calc for a whole host of reasons) - but I'm being specific in this question. Operating in the real world, MS Office is unfortunately still pretty standard and many of us have no alternative than to work with colleagues with limited technical flexibility. In technical terms GIS should go to them, rather than asking them to come to us. – Rostranimin Nov 30 '18 at 13:11
  • editing shapefile DBF is dangerous and shouldn't be done by people that dont understand the risk. (a simple sort can break the shapefile : the data and geometry don't have a common ID, the link between the two is only based on record order...) – J.R Jan 9 '19 at 16:08

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