0

This question may sound like it's a bit vague, but I think that lots of people seem to face similar issues, and on this occasion a general answer seems better than a specific focused one:

I use QGIS. I need to be able to collaborate with colleagues who use typical office software - working with QGIS to visualise their data. I've struggled for months to find a robust and simple way to do so. Last time I tried I ended up reverting to using Mapinfo, despite the other problems/limitations this caused me with symbology.

I'm in a typical organisation - most colleagues are limited to using typical office tools. This includes MS Office (Excel, Access). They are familiar with these tools and unlikely to use anything new. Anything I do has to work within their boundaries or it'll fail before it starts. (I've converted several to using QGIS themselves - but for the majority this isn't an option).

I use QGIS. I'm competent with working with shapefiles, TAB files, CSV files, linking data layers, relationships, etc - but pretty much a newbie when it comes to coding, python and other database solutions.

I (and others I think) need a simple and robust method to allow colleagues to work with tables of data - data which includes a field which I can use to link it to GIS records. I don't even need to be able to cope with geometry fields in their data. It's my job to manage geometry... all I'm working toward is relationships/links in their data with an existing GIS layer.

I've completely failed to find a simple way to do this... repeatedly getting bogged down in trying (incompetently) to connect MS Access to text files, or QGIS (64bit, 2.8, Windows 7) to MS Access in one way or another (no I can't change to 32bit). I'm currently working with asking the Excel user to save as a csv file so that QGIS can recognise this, but this is messy for the Excel user who has to handle odd (for them) messages about csv saves. I had QGIS talking directly to MS Excel for a while: I'm not sure how I achieved it but I think it knew the column headings should be field names and I could even edit the Excel file using QGIS at one point. But things fail and fall apart regularly. After one save to the Excel table (using Excel I think) QGIS stopped recognising the column headings (taking them to be data). I can't find how to re-set this, so am faced with columns labelled "field 1" etc.

Is there any simple solution to this?

I can see from a range of more specific questions that many other people face the same issue (e.g. for example see questions about connecting Access to QGIS). On this occasion I'm free to create a new solution... so long as it's simple for me to set up and doesn't rely on colleagues learning anything new.

This simple task seems to me to be a key requirement of a good GIS system for use in the real world, which is why it seems worth such a wordy/general question


Previously I asked a vaguely similar question about enabling the editing of text data, and although not tested much I think I found a way to work with some office software using a dbf file (see my own answer) but I can't make dbf work with MS Office software at the moment.

  • One option since your users are wanting to only use Office products for tabular editing is for them to do there editing against ESRI personal geodatabase (.mdb). This db can be edited in MS Access and pulled into QGIS for joins. See this Q/A for how to add .mdb tables into QGIS, gis.stackexchange.com/questions/129514/… – artwork21 Sep 10 '15 at 11:54
  • This source seems easlier, just tried it using QGIS v2.6 northrivergeographic.com/qgis-accessing-personal-geodatabase – artwork21 Sep 10 '15 at 12:12
  • Sounds promising... the bit I'm missing is how to create an ESRI personal geodatabase in the first place (preferably with data imported from a current csv or xlsx file). Do I see some hints in those links of 64 bit issues too? I have access to MapInfo but not ArcGIS – Rostranimin Sep 10 '15 at 13:21
  • Simple answer is if you have ArcGIS you can create the .mdb and import tabular or non-tabular data into it. If not, this may be more challenging b/c this format is proprietary to ESRI. You may be able to use ogr2ogr libraries using OSGeo4W Shell commands to convert dbf or csv to mdb tables. – artwork21 Sep 10 '15 at 14:53
  • If they only need to work with the tables of data, you can send them only the .dbf file of your Shapefile. If they only need to visualise the data, you can save it in the KML format and tell them to open it with Google Earth. – Nahas Sep 10 '15 at 15:22
1

I would have a look at Combining MS Office with GIS

It looks like SQLite would be a good go-between, possibly better than CSV. The trick is to get MS Office to cooperate with SQLite, but some Googling tells me it has been done. I won't recommend any particular page, since I haven't tried any of the specific solutions myself.

| improve this answer | |
  • Thanks Dericke - I'd come to roughly the same conclusion. Were I capable of quickly setting up an SQLite database and working out how to connect Office to it I'd probably be sorted. I'm far from incapable with such things, but at the moment this has proved to be beyond me. Too many parts of the jigsaw to bring together to make it work without someone to help me. – Rostranimin Sep 10 '15 at 22:02
0

Concerning the integration of spreadsheet programs with QGIS, you might want to have a look at the Editable GeoCSV Plugin

This plugin allows files in GeoCSV format to be loaded, edited and saved in QGIS including geometry. This allows to integrate QGIS with spreadsheet programs like LibreOffice.

| improve this answer | |
  • Thanks Underdark - I'm aware of this plugin but haven't used it yet. I do have a working solution already using CSV (importing the CSV in the normal way). Whilst this leaves me unable to edit the CSV the biggest issue is that it forces my colleagues to work with CSV files from Office (specifically MS Excel). Whilst this is quite possible it relies on them dealing with awkward messages... Excel spits out warnings about how it can't save all features in a CSV file - and simple things like formatting on the spreadsheet don't work (limiting quite important functionality for my colleagues). – Rostranimin Sep 10 '15 at 22:09
  • @Rostranimin In my experience, simple Excel files can be loaded directly into QGIS using Add vector layer? Is that not an option for you? – underdark Sep 11 '15 at 19:24
  • I agree. Unfortunately I've not been able to determine what factors are at play with this. Things get unreliable. I had a project set up using an Excel spreadsheet, with a QGIS form based on the column headings. I edited the sheet in Excel. Next time QGIS didn't recognise the headings so form broke. Also Excel spreadsheets can be edited if simple - but minor changes (?in format?) make them uneditable. To work really well I need things to be reliable, or at the least to be able to instruct colleagues what they can/not do within Excel so as not to break the QGIS project setup. – Rostranimin Sep 13 '15 at 21:26
  • Here's the frustrating thing - a colleague edited the Excel spreadsheet and now QGIS recognises the column headings as headings again. If it continues to do this I have a working setup - if sometimes it sees headings as data but sometimes as headings then any attribute form for QGIS breaks each time it changes. – Rostranimin Sep 14 '15 at 11:27
  • And again it broke... this time we had two different ways to break the ability of QGIS to open the spreadsheet - 1) re-name a worksheet 2) re-name the file name. On both occasions the file could then not be opened in QGIS. On one occasion copying the data into a new spreadsheet worked - on the other changing the file name back to the original worked. – Rostranimin Sep 14 '15 at 11:51

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