2

I'm importing Excel (.xlsx) files to postgres using the QGIS DB Manager "Import Layer/File..." tool. I've already successfully uploaded very similar Excel files without issue, but for one file in particular, DB Manager is ignoring the column headers and instead populating them as the first row of data instead of as the headers.

I have already looked for extra spaces and special characters, and the headers are in good shape.

Improve this question
4
  • 3
    I don't have an answer for you but xlsx headers can be pretty fickle even when you think it's fine. Sometimes I drag very similar sheets into QGIS and one has its headers detected properly while the other doesn't. I can't always figure out why. Have you tried formatting the data as a table in Excel and saving that? Removing all formatting? Maybe ensuring that the first column is numeric (if possible?) Deleted all empty rows and columns (which is not always straightforward)? (I would recommend an addon like ASAP Utilities to make data cleaning a lot easier)
    – she_weeds
    Commented Apr 14, 2020 at 2:02
  • 2
    I don't know why it happens, my solution was to export the xxx file as csv
    – Leo Cardona
    Commented Apr 14, 2020 at 2:48
  • My colleague and I have tried so many things at this point. It's bizarre. She even noticed that the field lengths in postgres are limited to 63 characters (and one of the field names was 68). I edited all field names to be under the limit, almost positive that would solve the problem, and it still hasn't.
    – Kristen G.
    Commented Apr 20, 2020 at 15:17
  • You may also check that none of the header are a reserved word (like DATE or FROM) or contains any space, accented character, special character,... or start with a number....
    – J.R
    Commented Nov 16, 2022 at 13:26

2 Answers 2

Reset to default
4

In Settings -> Options -> System, you can add the environment variable OGR_XLSX_HEADERS with the value FORCE (OGR documentation), then restart QGIS. Environment variables

Note that this will force QGIS to read the first line as headers for all xlsx files in all your projects.

Improve this answer
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
  • Please don't add the same answer to more than one question. If it is a valid answer to more than one question it suggests that the questions are likely to be duplicates.
    – PolyGeo ♦
    Commented Oct 10, 2022 at 10:20

Your Answer

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

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