[Qgis-user] Importing spreadsheet: Strange behaviour

[Bernd Vogelgesang]

Caramba!

That did the trick indeed! Added a field "dummy" and some numbers in the
next rows and gave it a try: Tada! All headers there.

So: All-string tables will not be imported correctly. Always e.g. add an
id-field with number

Thanx a ton!

Bernd

Am 12.03.22 um 23:04 schrieb Jonas Küpper:
>
> Hi Bernd,
>
> for what its worth: I experienced these Field1, Field2 troubles
> recently as well. The problem is that gdal apparently does not assume
> headers in a table, if all columns are string data. What usually fixes
> that for me is to add a dummy column where the first row (the header)
> is a string eg. "dummy" but all the subsequent rows are numbers. i
> stumbled over this behavior in the sources
> https://github.com/OSGeo/gdal/blob/1a30b3fab043a918e78cac7cc035ddc2670a9ba9/ogr/ogrsf_frmts/xlsx/ogrxlsxdatasource.cpp#L575
> .
>
> what should have fixed it as well would be to pass OGR_XLSX_HEADERS =
> FORCE to the provider while loading xlsx-files but a i couldn't find a
> way how to do that. (see https://gdal.org/drivers/vector/xlsx.html)
>
> Cheers
>
> Jonas
>
>
> On 11.03.2022 21:52, Bernd Vogelgesang wrote:
>> Hi Chris,
>>
>> mhm.
>> There are only two columns with ordinary names.
>> The data holds some special characters, and first I thought this
>> might influence the process as well. But the version where I deleted
>> those entries does not behave differently.
>> Very strange.
>>
>> Meanwhile, I exported the file to a csv, imported this one
>> successfully and then saved as gpkg. So at least, the data is in and
>> working.
>>
>>
>> Thanks,
>> Bernd
>>
>>
>> Am 11.03.22 um 21:38 schrieb chris hermansen:
>>> Bernd and list,
>>>
>>> On Fri, Mar 11, 2022, 12:08 Bernd Vogelgesang via Qgis-user
>>> <qgis-user at lists.osgeo.org> wrote:
>>>
>>>     Hi folks,
>>>
>>>     for quite some time, I hold frequently changing data in a xlsx-file.
>>>     Working in a spreadsheet application is so much more efficient
>>>     sometimes.
>>>
>>>     This spreadsheet easily imports by drag and drop, the import
>>>     dialog pops
>>>     up where to choose which tab to import, and finally the table is
>>>     there
>>>     with the first row as column heads.
>>>
>>>     Now, I'm getting just nuts.
>>>
>>>     I'm trying to do the same with other data, and whatever I try, it
>>>     imports the table ignoring the header row, so its Field1, Field2...
>>>
>>>     Findings so far:
>>>
>>>     I had a long file name. With this, there was no import dialog,
>>>     but the
>>>     first tab was imported immediately, but without the header row.
>>>
>>>     I saved the file with a short file name, and now the import
>>>     dialog asks
>>>     which tab to import. But still, the first data-row is ignored.
>>>
>>>     Dragging my other already existing file into that project works just
>>>     perfectly.
>>>
>>>     What could be the reason for this behaviour?
>>>
>>>
>>> Clutching at straws here...
>>>
>>> Sometimes when making a pivot table in LibreOffice calc I notice
>>> that column headings (first row) aren't interpreted as such.
>>>
>>> This seems to occur on the following scenarios:
>>>
>>> 1) a column has an en empty cell in row 1 (or missing title)
>>>
>>> 2) a column exists outside the area of interest with no header (this
>>> probably would not apply in your case, but maybe you have some
>>> calculations to the right of your data which don't have a column header)
>>>
>>> 3) there are some "strange" characters in one or more header fields
>>> (I can't recall precisely what constitutes "strange")
>>>
>>>
>>> What you might want to do is save a copy of your spreadsheet, then
>>> delete anything to the left or right of your columns, and check that
>>> your column names are all "legal variable" names ie begin with a
>>> letter, only letters and numbers
>>>
>>> I hope this helps; good luck!
>>>
>>> Chris
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-user/attachments/20220312/55a9a176/attachment.html>