<html><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /></head><body style='font-size: 10pt; font-family: Verdana,Geneva,sans-serif'>
<p>Hi all,</p>
<p>Thank you Nyall for checking the situation around codepage and freexl/OGR.</p>
<p>Let's plan to improve the situation around spreadsheet data loading in future OGR and QGIS versions.</p>
<p>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.</p>
<p>Then we can have a look what FreeXL/OGR could improve and what needs to be done in QGIS.</p>
<p>After we have this collection of requirements, we can see what this would cost, who could implement what and who can fund the work.</p>
<p>@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).</p>
<p>So my situation is ok now, but I think we should improve the situation for our users in the future.</p>
<p>Thanks all for the input,</p>
<p>Andreas</p>
<p id="reply-intro">On 2021-07-15 10:33, Nyall Dawson wrote:</p>
<blockquote type="cite" style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0">
<div id="replybody1">
<div>
<div dir="auto">
<div><br /><br />
<div class="v1gmail_quote">
<div class="v1gmail_attr" dir="ltr">On Thu, 15 Jul 2021, 6:02 pm Andreas Neumann, <<a href="mailto:a.neumann@carto.net" rel="noreferrer">a.neumann@carto.net</a>> wrote:</div>
<blockquote class="v1gmail_quote" style="margin: 0 0 0 .8ex; border-left: 1px #ccc solid; padding-left: 1ex;">
<div style="font-size: 10pt; font-family: Verdana,Geneva,sans-serif;">
<blockquote style="padding: 0 0.4em; border-left: #1010ff 2px solid; margin: 0;">
<div style="margin: 0; padding: 0; font-family: monospace;">Isn't this limitation ultimately that GDAL isn't reading the encoding<br />correctly? (Or perhaps it's a limitation in the underlying freexl<br />library...)</div>
<div style="margin: 0; padding: 0; font-family: monospace;"> </div>
</blockquote>
<div style="margin: 0; padding: 0; font-family: monospace;"> </div>
<p>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 ...</p>
</div>
</blockquote>
</div>
</div>
<div dir="auto"> </div>
<div dir="auto">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.</div>
<div dir="auto"> </div>
<div dir="auto">
<div class="v1gmail_quote">
<blockquote class="v1gmail_quote" style="margin: 0 0 0 .8ex; border-left: 1px #ccc solid; padding-left: 1ex;">
<div style="font-size: 10pt; font-family: Verdana,Geneva,sans-serif;">
<p>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.</p>
<p>For reference: here is the VBA-Code for the batch conversion in Excel: <a href="https://www.extendoffice.com/documents/excel/1349-excel-batch-convert-xls-to-xlsx.html#a2" target="_blank" rel="noopener noreferrer">https://www.extendoffice.com/documents/excel/1349-excel-batch-convert-xls-to-xlsx.html#a2</a></p>
<p>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 (<a href="https://plugins.qgis.org/plugins/SpreadsheetLayers/version/2.0.1/" target="_blank" rel="noopener noreferrer">https://plugins.qgis.org/plugins/SpreadsheetLayers/version/2.0.1/</a>).</p>
<p>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.</p>
</div>
</blockquote>
</div>
</div>
<div dir="auto"> </div>
<div dir="auto">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....</div>
<div dir="auto"> </div>
<div dir="auto"> </div>
<div dir="auto">
<div class="v1gmail_quote">
<blockquote class="v1gmail_quote" style="margin: 0 0 0 .8ex; border-left: 1px #ccc solid; padding-left: 1ex;">
<div style="font-size: 10pt; font-family: Verdana,Geneva,sans-serif;">
<p>Thanks anyway - my problem is now solved by previous conversion of the files to .xlsx.</p>
<p>Andreas</p>
</div>
</blockquote>
</div>
</div>
</div>
</div>
</div>
</blockquote>
<p><br /></p>
</body></html>