In response to my previous article about joining data from a csv file, I received a very good tip. From QGIS 1.8 Excel files can be opened directly in QGIS. Strange enough, nothing is mentioned about this in the ‘release notes’. It only works with the older XLS-format, not with the newer XLSX format.
You can open an Excel file with the menu option or button ‘Add new vector layer’. That is confusing for non-spatial data. Your Excel file should contain a column to be used to join both layers. Further, you need one or more columns with the data you want to display. Co-ordinates are not required. Set the file type filter to ‘All files’. Otherwise your Excel-file will not be visible. The dialog doesn’t contain a special filter for Excel files.
If all went well, you can now join the table to a spatial layer, like described in my previous article.
Join the Conversation
Leave a comment
Erg tof en handig. Nu nog met open office calc files!
Take care, when your paths have accented characters (which is very annoying), it will not read the files (at least, that is my experience on two different computers on Windows 7 and one on XP). My best guess is that it’s possible that it is not fully supported yet, so they decided against making this public.
Agree with Jasja. Strange selection between open and proprietary spreadsheet formats.
Someone pointed out that it is already possible to use OpenOffice/LibreOffice Calc files with the development version of QGIS (with GDAL/OGR >=2.0.0): http://www.gdal.org/ogr/drv_ods.html.
Working Fine!
Great to have this information! Was using .dbf format (from Excel to Access to QGIS) to import and join data tables, will be nice to be able to go directly from .xls (Excel). Thanks!
Hi
I still get an “Invalid data source” error message when trying to open a Xls file as a vector layer.
Does the Encoding choice have anything to do with this ? Using QS 1.8 and Windows XP.
OK got it right. The problem was that the joining attribute in Excel was a text string and the one in Qgis was an integer.
Hi!
I get an “Invalid data source” error message when trying to open a xls file as a vector layer.
What is the problem?
I am just a beginner (as of today) and a bit amazed by this “connectivity”. The xlsx-format is not new and should be supported. Ok, I can save extra copies of my spreadsheets in the old xls-format…
Q1:do we have a smart plugin to connect with (ranged) data in xlsx-format?
Q2: is it possible to tell the system that my first line contains field names?
Strange indeed that this is not well documented, nor well known! But an excellent tip, thank you Sake.
It’s good to see that field types (real / string) are recognized from the data.
Limitations I’ve found:
1. Field names are not read from the .xls (as noted by Henk), but default to Field1, Field2, etc.
2. Calculated values from Excel are not imported.
3. Oddly, .xlsx is not supported.
I have been able to add xlsx (MS excel 2010) files to QGIS 2.0 using the described method.
Hi! There is a method to open .xls files using the internals from qgis on a python plugin?
You could check the code of the XY Tools plugin:
https://plugins.qgis.org/plugins/xytools/
Wellicht een late reactie maar m.i. wel waardevol.
Binnen QGIS is inmiddels een plugin genaamd “Spreadsheet layers” hiermee kan je een spreadsheet inladen. Boven genoemde problemen komen daarmee te vervallen.
HI, i want to make a question:
I want to relate Excel table with Qgis, but not only to show the data i want to make filters in Excel’s table and it show in Qgis
Anyone knows how can i do this?