4

I'm importing multiple excel files into QGIS (version 2.18.7) by dragging and dropping them into QGIS. The excel files don't contain any coordinates but data that I need to join to vector layers.

My problem is, that sometimes, seemingly at random, QGIS chooses not to use the entries in the first row as the field names. Instead the attribute table labels the field field 1, field 2 and so on. I guess I could save all the excel files as CSV files but I'd prefer not to add an additional step.

Is there some setting or import method that I'm missing?

Improve this question
6
  • Is there any pattern? What does the first cell contain in files that work and those that don't? Are they all in the same excel format?
    – lynxlynxlynx
    Mar 6, 2018 at 14:25
  • 4
    Try importing those files who are "faulty" via the "add text file" dialogue, there are a lot more possible settings there.
    – Erik
    Mar 6, 2018 at 14:27
  • @ErikLohmann, you should expand your comment into an answer. It's clear and succinct.
    – jcarlson
    Mar 6, 2018 at 15:12
  • I am kind of doing the same task discussed in this post: I get multiple (.xls, *.xlsx) files, which I need to process in QGIS Bonn to export in ASCii files. I would like to automate the process by using GDAL 2.2. Therefore, I am trying to use ogr2ogr to convert files from *.xlsx to a sort of vector file (.shp), and then to add a specific projection (EPSG:28354). Perhaps, this approach is wrong, I am very new in GDAL/Python, and that's why so far I cannot run a simple line. I am trying to find a guideline in gdal.org/gdal_utilities.html and qgis.org/en/site/forusers/in
    – Cristo Facundo Pérez
    Aug 2, 2018 at 10:25
  • See also (same question really): gis.stackexchange.com/questions/203954/…
    – Rostranimin
    Aug 10, 2018 at 15:07

8 Answers 8

Reset to default
4

Unfortunately, I believe the extra step to export the Excel file to a .csv file will be necessary.

There have been issues with regards to importing Microsoft Office files in various GIS packages. A lot of this can be due to the drivers for 64 bit operating systems.

It may be worth the experiment to see if what you are attempting will work in a 32 bit version of the operating system.

The one nice thing with exporting a .csv file is the ability to set up a watch file during the text file import, then any changes made to the text file will be reflected in the joined data without having to recreate the join within QGIS.

Improve this answer
1

A possible, yet a bit more work-intensive solution is to load those "faulty" files via the "add text dialogue" into Qgis. The dialogue offers a lot of settings which might solve your problem, e.g. setting the coding of the file, the separators or header rows which are to be omitted.

Improve this answer
2
  • As far as I can tell the add text doesn't work with .xlsx and .xls files.
    – DriveThomas
    Mar 7, 2018 at 8:24
  • I could have sworn it worked for me two days ago... but you're right.
    – Erik
    Mar 7, 2018 at 8:56
1

Had the same problem, found answer in gis.stackexchange.com (Atriplex):

In Settings -> Options -> System, you can add the environment variable OGR_XLSX_HEADERS with the value FORCE, then restart QGIS. Note that this will force QGIS to read the first line as headers for all xlsx files in all your projects.

Explanation: OGR-configuration options

Improve this answer
0

This issue arises also if one of the column headers is numeric (for instance a year). So the solution is then to change the column header to something non-numeric.

For instance this csv will have the same problem:

test,2025
1,2
Improve this answer
3
  • not sure about this. I've experienced the same issue as discussed here, having a non-numeric value inside the first cell (name of a province). Field 1 and field 2 where appearing instead when joining. Instead of having the table start with a non-numeric value, I made the first column of the table a numeric value column, with "fictional" numbers in ascending order. "Fictional" because I won't be using them after, there just here to prevent the field 1, field 2 from appearing when joining a non-csv file. Works ace.
    – Tim56
    May 27, 2020 at 11:00
  • Interesting finding, but this has not much to do with my suggestion. I just explained that this issue also arises when there is a numeric value in the column header and dragging the csv into QGIS. This can easily be replicated with the example csv. Apparently there are more ways that the issue can arise. I wanted to share this because this actually happens even in QGIS 3.12 so it could be a possible solution.
    – Michel
    May 28, 2020 at 19:23
  • In QGIS 3.26 I have no issues with numeric field names in Excel. But you need at least one numeric column in your sheet.
    – Trnovstyle
    Sep 19, 2022 at 10:08
0

As mentioned by other contributors, this problem can arise from many issues. I was new to this, but I trouble-shooted the issue by checking the .xlsx column headers.

Turns out I had an empty column somewhere between my sets of data. I deleted the empty column and this resolved the issue immediately, meaning I can continue to use the formatting and formulas benefiting from a .xlsx file (would have hated having to go to .csv).

Improve this answer
0

I had the same problem. I saw that the attribute table from Excel (with desired field names appearing as the first record) had two or three additional blank fields to the right of the table. I went back to Excel to delete some blank columns to the right of my data, and reloaded it in QGIS and voilá, the field names appear in the right place.

Improve this answer
0

The trick is to add at least one numerical field as last column

Improve this answer
2
  • Your answer could be improved with additional supporting information. Please edit to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers in the help center.
    – Community Bot
    May 13, 2022 at 9:16
  • Worked for me. From my testing with QGIS 3.26 the only thing that mattered was to have at least one numeric column. Position of the numeric field is not important - it does not have to be last column.
    – Trnovstyle
    Sep 19, 2022 at 10:10
0

You can add any *.ods, *.xls, *.xlsx files as layers via the "Spreadsheet Layers" plugin (Layer->Add layer-> Add Spreadsheet Layer). In the plugin window check the box "Header at first line".

Improve this answer
1
  • Works nice, but has the disadvantage, that imported layers can not be edited in QGis
    – ludwig
    Nov 20 at 14:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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