Hi all,
Thank you Nyall for checking the situation around codepage and
freexl/OGR.
Let's plan to improve the situation around spreadsheet data loading in
future OGR and QGIS versions.
We can start with a QEP collecting all the requirements around the
spreadsheet data loading and an easy conversion of a non-geometry layer
to a geometry layer.
Then we can have a look what FreeXL/OGR could improve and what needs to
be done in QGIS.
After we have this collection of requirements, we can see what this
would cost, who could implement what and who can fund the work.
@Rosa: thank you for your suggestion with Python/Pandas. I already found
a solution through batch conversion (.xls to .xlsx) first (with some VBA
code).
So my situation is ok now, but I think we should improve the situation
for our users in the future.
Thanks all for the input,
Andreas
On 2021-07-15 10:33, Nyall Dawson wrote:
On Thu, 15 Jul 2021, 6:02 pm Andreas Neumann, <a.neum...@carto.net>
wrote:
Isn't this limitation ultimately that GDAL isn't reading the encoding
correctly? (Or perhaps it's a limitation in the underlying freexl
library...)
Yes - I also assume it is a limitation of OGR or FreeXL. I was hoping
that some secret OGR opening option, environment variable or sidecar
file could do the trick ...
I just checked -- it's a gdal bug. Freexl supports reading the codepage
from the xls file, but gdal doesn't do this and doesn't respect the
codepage when reading string values.
Never mind - I found a solution to convert my hundreds of .xls to .xlsx
(with VBA code in Excel) and when I load the .xlsx, the encoding is
loaded fine in QGIS.
For reference: here is the VBA-Code for the batch conversion in Excel:
https://www.extendoffice.com/documents/excel/1349-excel-batch-convert-xls-to-xlsx.html#a2
In general, I think that the handling of spreadsheet file loading in
QGIS could be much improved - similar to the plugin "Spreadsheet
Layers" from Arnaud
(https://plugins.qgis.org/plugins/SpreadsheetLayers/version/2.0.1/).
My assumption is that Excel/Openoffice files are so popular among our
users that it would really nice if we could improve the situation
susbstantially. Otherwise we are always forcing our users to save the
spreadsheet files to CSV first - which has a lot of loading options,
but has it's own limitations.
Yeah, I agree. I'd like to see an easy way to turn ANY non spatial layer
into a spatial layer via some "virtual" geometry column. Maybe for non
spatial sources we could add a "geometry" tab in the layer properties
which lets users pick the X/y/z/m/wkt etc fields....
Thanks anyway - my problem is now solved by previous conversion of the
files to .xlsx.
Andreas
_______________________________________________
QGIS-Developer mailing list
QGIS-Developer@lists.osgeo.org
List info: https://lists.osgeo.org/mailman/listinfo/qgis-developer
Unsubscribe: https://lists.osgeo.org/mailman/listinfo/qgis-developer
