Directly use data from Excel file

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.

15 thoughts on “Directly use data from Excel file”

  1. 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.

  2. 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!

  3. 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.

    1. 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.

  4. Hi!
    I get an “Invalid data source” error message when trying to open a xls file as a vector layer.

    What is the problem?

  5. 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?

  6. 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.

  7. 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.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>