[QGIS-Developer] Loading many Excel-Files in QGIS with correct encoding

[Andreas Neumann]

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.neumann at 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
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/qgis-developer/attachments/20210715/83d578de/attachment.html>