Excel Queries

[Mark Arnup]

Dear All

I was wounding if there was a way to perform a query of the attribute data from a ShapeFile or GeoPackage in QGIS, to an Excel Spreadsheet.

[Phil Wyatt]

Hi there Mark,

 

Sure is a way – you can do a simple filter of a layer and then use the Export, Save features as… menu to save as a CSV or excel file.

 

Cheers - Phil

--
You received this message because you are subscribed to the Google Groups "QGIS Australia User Group" group.
To unsubscribe from this group and stop receiving emails from it, send an email to australian-qgis-use...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/9d91ea15-be44-4ae4-8000-b402d7ac0285n%40googlegroups.com.

[michae...@gmail.com]

Pretty sure you can, but may need to use an ODBC driver.  The .db file of a shapefile is effectively a db file you should be able to query from excel, but I have not tried it. 

If you just want the data in excel manually I find using the copy button in the attribute table and pasting into excel is the easiest method.  I usually turn it into a table in excel then copy or paste any updates into that as required.

[Andrew Keith]

Hi Mark,

I select the rows I want and Ctrl-C Ctrl-V into excel. They are copied as WKT so you can easily manipulate data and re-import as a new WKT layer if it is useful.

Regards,

Andrew.






Andrew Keith
Asset Management Coordinator
p 02 6969 4836 | m 0423 745 618

Griffith City Council pays respect to Aboriginal and Torres Strait Islander Elders, past, present and future,
and acknowledges they are the custodians of this land. Council is committed to building our future together.

From:        "Phil Wyatt" <ph...@wyatt-family.com>
To:        <australian-qg...@googlegroups.com>
Date:        08/10/2021 02:17 PM
Subject:        RE: [Aus-NZ-QGIS-group] Excel Queries

---

.
To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/00c701d7bbf3%24058c3240%2410a496c0%24%40wyatt-family.com.

 

 

---

This e-mail, together with any attachments, is for the exclusive and confidential use of the addressee(s). Confidentiality is not waived if you are not the intended recipient. Any other distribution, use of, or reproduction without prior written consent is strictly prohibited.

Views expressed in this e-mail are those of the individual, except where specifically stated otherwise. If this e-mail has been sent to you in error, please delete the e-mail completely and immediately from your system. Although reasonable precautions are taken, Griffith City Council does not warrant or guarantee this message to be free of errors, interference, viruses or similar malicious code and does not accept liability for any consequences to the recipient opening or using this email or attachments. This email may be made available to third parties in accordance with the Government Information (Public Access) Act 2009.

This email was scanned and cleared by MailMarshal & Sophos AV

---

[Mark Arnup]

My intention was to use the spatial data from our GIS as our main data base, and then upload it to an asset register for asset valuations. This is to replace the antiquated financial spread sheets that are still being used today! The hope was to have a excel table with all the unit rates and asset type codes, that can be populated with just a refreash button, than then run the final report!

Mark Arnup
62 Reef St
Wedderburn 3518
Ph: (03) 5494 3358
Mob: 0417 510 853

02 6969 4836 | m 0423 745 618

You received this message because you are subscribed to a topic in the Google Groups "QGIS Australia User Group" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/australian-qgis-user-group/l0LTkTUokGk/unsubscribe.
To unsubscribe from this group and all its topics, send an email to australian-qgis-use...@googlegroups.com.
To view this discussion on the web, visit https://groups.google.com/d/msgid/australian-qgis-user-group/OFE441259F.0753037D-ONCA258768.0015534A-CA258768.0015CE32%40griffith.nsw.gov.au.

[bi...@williamson.fm]

Hello Mark and responders. 

I'd like to hear any improvements on my suggestions below. You can thank Camp To Camp for the plugin https://www.camptocamp.com/en

A spreadsheet table can be joined to a spatial table to provide on an ongoing workflow. The spreadsheet table does not have x y / spatial data in it. 

Generally the workflow is that edits are made in the spreadsheet table and viewed on a map and as reports in the spreadsheets. QGIS projects and map composers can be setup and saved and the underlying data changes as required. 

This works well if; 

 - the features in the spatial dataset are related to the excel table 1:1

 - no new rows are created or deleted by non-specialists

 - columns are not added, deleted or have their titles changed, in the joined columns of the spreadsheet table

Cell values can be changed, put to 0 or blank, can be formatted, the table can be sorted and filtered. Another spreadsheet table can read the values from this one to make a report. 

Joining a spreadsheet and a spatial table is done by;

 - having a key field / id which is the same in the spatial and non-spatial table. Like all keys, unique values are required.

 - having the spatial feature table ready (geopackage, postgis, kml etc) with a column of the key values 

 - loading the spreadsheet with the plugin Spreadsheet Layers (it then shows up in the menu Add Layers) https://www.youtube.com/watch?v=KWWQnYy7VYA 

 - creating a join in the spatial table referring back to the spreadsheet table.  https://www.qgistutorials.com/en/docs/3/performing_table_joins.html

Normally I don't "cache the join" and I don't "allow editable join". "joined fields" all on, "prefix" three characters and an underscore. QGIS can read spreadsheets without the plugin but it is more stable in a workflow with it. New rows and spatial features can be added 1:1 by being particular about the key, you will have to observe uniqueness as the spreadsheet won't readily enforce this. But no trouble really to add and delete many new rows and features, merge two features back to one if you use some grey matter. If doing this, copy each of the tables out to a new spreadsheet every now and then to compare the number of rows and test the join etc. 

If you want to go another level, put an Excel spreadsheet into Sharepoint. Keep your copy of the file "on this machine" and let users update spreadsheet data in a browser version you share to them (advise them not to break stuff....). You should also ask Sharepoint to "keep on this machine" the *.vrt file which the plugin created, unfortunately this can be a distraction for laymen and hopefully they don't delete it out of Sharepoint. 

Also with regard the response about copying WKT directly from the attribute table, to "include WKT" is a global setting in QGIS and some users therefore may have a different experience. 

hope that helps

Bill