Re: [Qgis-user] Importing spreadsheet: Strange behaviour
Hi Micha, thank you. And where in this theoretical situation exactly do you use drag and drop of spreadsheets to the canvas in this process? Bernd Am 13.03.22 um 09:56 schrieb Micha Silver: On 13/03/2022 10:37, Bernd Vogelgesang wrote: Hi Micha, can you elobrate a bit more on these situations where the data has no column header, cause I don't know them. Well, just a few theoretical examples: I might have several tables of data from some sensor (i.e. rainfall, temperature) with dates, long/lat and column headers. Merging all the tables would cause the column headers to get repeated for each table. I usually just remove *all* column headers, then import, and point to the correct columns for long/lat. When I extract values from a multiband raster at point locations (for some ML model) I get just columns of values; the actual column names are not important. Hope that clarifies. Bernd Am 13.03.22 um 09:35 schrieb Micha Silver: On 13/03/2022 10:22, Bernd Vogelgesang via Qgis-user wrote: Hi Andrea, thanx for clarifying. imho this is not a "wise" design decision. Maybe I am too less a pro, but i can't think of any situation when someone seriously working with table data does NOT want to have a column header. What would be the advantage of that? There are situations where the data table contains only data: no column headers. Ok, if this is a GDAL-design decision, then at least QGIS should have default settings in the environment preventing this. An ordinary user will never ever expect this behaviour nor find this setting by himself. Cheers, Bernd Am 13.03.22 um 08:55 schrieb Andrea Giudiceandrea: *Bernd Vogelgesang* /Sat Mar 12 14:13:04 PST 2022/ So: All-string tables will not be imported correctly. Always e.g. add an id-field with number Hi Bernd, this is by design: https://github.com/OSGeo/gdal/blob/1c5bc766513c4407f702bd8cd8bfa958ed61cfea/ogr/ogrsf_frmts/xlsx/ogrxlsxdatasource.cpp#L597-L598 Am 12.03.22 um 23:04 schrieb Jonas Küpper:// >/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) / Hi Jonas, you can set a GDAL/OGR configuration option, like OGR_XLSX_HEADERS, via the "Settings" -> "Options" -> "System" -> "Environment" setting panel in QGIS. Regards. Andrea Giudiceandrea ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info:https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe:https://lists.osgeo.org/mailman/listinfo/qgis-user -- Micha Silver Ben Gurion Univ. Sde Boker, Remote Sensing Lab cell: +972-523-665918 ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Importing spreadsheet: Strange behaviour
On 13/03/2022 10:37, Bernd Vogelgesang wrote: Hi Micha, can you elobrate a bit more on these situations where the data has no column header, cause I don't know them. Well, just a few theoretical examples: I might have several tables of data from some sensor (i.e. rainfall, temperature) with dates, long/lat and column headers. Merging all the tables would cause the column headers to get repeated for each table. I usually just remove *all* column headers, then import, and point to the correct columns for long/lat. When I extract values from a multiband raster at point locations (for some ML model) I get just columns of values; the actual column names are not important. Hope that clarifies. Bernd Am 13.03.22 um 09:35 schrieb Micha Silver: On 13/03/2022 10:22, Bernd Vogelgesang via Qgis-user wrote: Hi Andrea, thanx for clarifying. imho this is not a "wise" design decision. Maybe I am too less a pro, but i can't think of any situation when someone seriously working with table data does NOT want to have a column header. What would be the advantage of that? There are situations where the data table contains only data: no column headers. Ok, if this is a GDAL-design decision, then at least QGIS should have default settings in the environment preventing this. An ordinary user will never ever expect this behaviour nor find this setting by himself. Cheers, Bernd Am 13.03.22 um 08:55 schrieb Andrea Giudiceandrea: *Bernd Vogelgesang* /Sat Mar 12 14:13:04 PST 2022/ So: All-string tables will not be imported correctly. Always e.g. add an id-field with number Hi Bernd, this is by design: https://github.com/OSGeo/gdal/blob/1c5bc766513c4407f702bd8cd8bfa958ed61cfea/ogr/ogrsf_frmts/xlsx/ogrxlsxdatasource.cpp#L597-L598 Am 12.03.22 um 23:04 schrieb Jonas Küpper:// >/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) / Hi Jonas, you can set a GDAL/OGR configuration option, like OGR_XLSX_HEADERS, via the "Settings" -> "Options" -> "System" -> "Environment" setting panel in QGIS. Regards. Andrea Giudiceandrea ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info:https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe:https://lists.osgeo.org/mailman/listinfo/qgis-user -- Micha Silver Ben Gurion Univ. Sde Boker, Remote Sensing Lab cell: +972-523-665918 -- Micha Silver Ben Gurion Univ. Sde Boker, Remote Sensing Lab cell: +972-523-665918 ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Importing spreadsheet: Strange behaviour
Hi Micha, can you elobrate a bit more on these situations where the data has no column header, cause I don't know them. Bernd Am 13.03.22 um 09:35 schrieb Micha Silver: On 13/03/2022 10:22, Bernd Vogelgesang via Qgis-user wrote: Hi Andrea, thanx for clarifying. imho this is not a "wise" design decision. Maybe I am too less a pro, but i can't think of any situation when someone seriously working with table data does NOT want to have a column header. What would be the advantage of that? There are situations where the data table contains only data: no column headers. Ok, if this is a GDAL-design decision, then at least QGIS should have default settings in the environment preventing this. An ordinary user will never ever expect this behaviour nor find this setting by himself. Cheers, Bernd Am 13.03.22 um 08:55 schrieb Andrea Giudiceandrea: *Bernd Vogelgesang* /Sat Mar 12 14:13:04 PST 2022/ So: All-string tables will not be imported correctly. Always e.g. add an id-field with number Hi Bernd, this is by design: https://github.com/OSGeo/gdal/blob/1c5bc766513c4407f702bd8cd8bfa958ed61cfea/ogr/ogrsf_frmts/xlsx/ogrxlsxdatasource.cpp#L597-L598 Am 12.03.22 um 23:04 schrieb Jonas Küpper:// >/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) / Hi Jonas, you can set a GDAL/OGR configuration option, like OGR_XLSX_HEADERS, via the "Settings" -> "Options" -> "System" -> "Environment" setting panel in QGIS. Regards. Andrea Giudiceandrea ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info:https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe:https://lists.osgeo.org/mailman/listinfo/qgis-user -- Micha Silver Ben Gurion Univ. Sde Boker, Remote Sensing Lab cell: +972-523-665918 ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Importing spreadsheet: Strange behaviour
On 13/03/2022 10:22, Bernd Vogelgesang via Qgis-user wrote: Hi Andrea, thanx for clarifying. imho this is not a "wise" design decision. Maybe I am too less a pro, but i can't think of any situation when someone seriously working with table data does NOT want to have a column header. What would be the advantage of that? There are situations where the data table contains only data: no column headers. Ok, if this is a GDAL-design decision, then at least QGIS should have default settings in the environment preventing this. An ordinary user will never ever expect this behaviour nor find this setting by himself. Cheers, Bernd Am 13.03.22 um 08:55 schrieb Andrea Giudiceandrea: Bernd Vogelgesang Sat Mar 12 14:13:04 PST 2022 So: All-string tables will not be imported correctly. Always e.g. add an id-field with number Hi Bernd, this is by design: https://github.com/OSGeo/gdal/blob/1c5bc766513c4407f702bd8cd8bfa958ed61cfea/ogr/ogrsf_frmts/xlsx/ogrxlsxdatasource.cpp#L597-L598 Am 12.03.22 um 23:04 schrieb Jonas Küpper: > 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) Hi Jonas, you can set a GDAL/OGR configuration option, like OGR_XLSX_HEADERS, via the "Settings" -> "Options" -> "System" -> "Environment" setting panel in QGIS. Regards. Andrea Giudiceandrea ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user -- Micha Silver Ben Gurion Univ. Sde Boker, Remote Sensing Lab cell: +972-523-665918 ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Importing spreadsheet: Strange behaviour
Hi Andrea, thanx for clarifying. imho this is not a "wise" design decision. Maybe I am too less a pro, but i can't think of any situation when someone seriously working with table data does NOT want to have a column header. What would be the advantage of that? Ok, if this is a GDAL-design decision, then at least QGIS should have default settings in the environment preventing this. An ordinary user will never ever expect this behaviour nor find this setting by himself. Cheers, Bernd Am 13.03.22 um 08:55 schrieb Andrea Giudiceandrea: *Bernd Vogelgesang* /Sat Mar 12 14:13:04 PST 2022/ So: All-string tables will not be imported correctly. Always e.g. add an id-field with number Hi Bernd, this is by design: https://github.com/OSGeo/gdal/blob/1c5bc766513c4407f702bd8cd8bfa958ed61cfea/ogr/ogrsf_frmts/xlsx/ogrxlsxdatasource.cpp#L597-L598 Am 12.03.22 um 23:04 schrieb Jonas Küpper:// >/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) / Hi Jonas, you can set a GDAL/OGR configuration option, like OGR_XLSX_HEADERS, via the "Settings" -> "Options" -> "System" -> "Environment" setting panel in QGIS. Regards. Andrea Giudiceandrea ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Importing spreadsheet: Strange behaviour
*Bernd Vogelgesang* /Sat Mar 12 14:13:04 PST 2022/ So: All-string tables will not be imported correctly. Always e.g. add an id-field with number Hi Bernd, this is by design: https://github.com/OSGeo/gdal/blob/1c5bc766513c4407f702bd8cd8bfa958ed61cfea/ogr/ogrsf_frmts/xlsx/ogrxlsxdatasource.cpp#L597-L598 Am 12.03.22 um 23:04 schrieb Jonas Küpper:// >/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) / Hi Jonas, you can set a GDAL/OGR configuration option, like OGR_XLSX_HEADERS, via the "Settings" -> "Options" -> "System" -> "Environment" setting panel in QGIS. Regards. Andrea Giudiceandrea___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Importing spreadsheet: Strange behaviour
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 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 ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Importing spreadsheet: Strange behaviour
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 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 ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
Re: [Qgis-user] Importing spreadsheet: Strange behaviour
Bernd and list, On Fri, Mar 11, 2022, 12:08 Bernd Vogelgesang via Qgis-user < qgis-user@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 > ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
[Qgis-user] Importing spreadsheet: Strange behaviour
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? Cheers, Bernd ___ Qgis-user mailing list Qgis-user@lists.osgeo.org List info: https://lists.osgeo.org/mailman/listinfo/qgis-user Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-user
