<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8">
<meta name="Generator" content="Microsoft Word 15 (filtered medium)">
<style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:Consolas;
panose-1:2 11 6 9 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
p
{mso-style-priority:99;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
pre
{mso-style-priority:99;
mso-style-link:"HTML - wstępnie sformatowany Znak";
margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.HTML-wstpniesformatowanyZnak
{mso-style-name:"HTML - wstępnie sformatowany Znak";
mso-style-priority:99;
mso-style-link:"HTML - wstępnie sformatowany";
font-family:Consolas;}
p.msonormal0, li.msonormal0, div.msonormal0
{mso-style-name:msonormal;
mso-margin-top-alt:auto;
margin-right:0cm;
mso-margin-bottom-alt:auto;
margin-left:0cm;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
p.HTML-esimuotoiltu, li.HTML-esimuotoiltu, div.HTML-esimuotoiltu
{mso-style-name:HTML-esimuotoiltu;
mso-style-link:"HTML-esimuotoiltu Char";
margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
span.HTML-esimuotoiltuChar
{mso-style-name:"HTML-esimuotoiltu Char";
mso-style-priority:99;
mso-style-link:HTML-esimuotoiltu;
font-family:Consolas;}
span.Stylwiadomocie-mail22
{mso-style-type:personal;
font-family:"Calibri",sans-serif;
color:windowtext;}
span.Stylwiadomocie-mail24
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:#1F497D;}
.MsoChpDefault
{mso-style-type:export-only;
font-size:10.0pt;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:70.85pt 70.85pt 70.85pt 70.85pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang="PL" link="blue" vlink="purple">
<div class="WordSection1">
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I think a single record wouldn't help you. If I would copy only one record to another empty database it could be exported to .xlsx.
We have a postgres database and a table without any primary key, so QGIS uses a tid. The overall size of the database is huuuge (billions of records), so the tid reaches high values and apparently overflows int4.<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Export to CSV goes fine, so it seems OGR’s CSV driver uses int8, but when exporting to XLSX or ODS you get that error. Maybe it’s just
as simple as change id datatypes in affected drivers?<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p></o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Rahkonen Jukka (MML) <jukka.rahkonen@maanmittauslaitos.fi>
<br>
<b>Sent:</b> Monday, January 10, 2022 2:24 PM<br>
<b>To:</b> Kopszak Marta - Partner Hurt <Marta.Kopszak@orange.com>; Even Rouault <even.rouault@spatialys.com>; gdal-dev@lists.osgeo.org<br>
<b>Subject:</b> Re: [gdal-dev] Bug in OGR library<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p class="MsoNormal"><span lang="FI" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="FI" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US">Now it starts to make sense. Could you paste the contents of a single row that you can’t write into xlsx format? You can capture it
with ogrinfo “ogrinto [database connection] -sql "select * from my_table limit 1". Or maybe better to convert that row into GML format and attach that to your mail.<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="FI" style="font-size:11.0pt;font-family:"Calibri",sans-serif">Lähettäjä:</span></b><span lang="FI" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Kopszak Marta - Partner Hurt <<a href="mailto:Marta.Kopszak@orange.com">Marta.Kopszak@orange.com</a>>
<br>
<b>Lähetetty:</b> maanantai 10. tammikuuta 2022 15.07<br>
<b>Vastaanottaja:</b> Rahkonen Jukka (MML) <<a href="mailto:jukka.rahkonen@maanmittauslaitos.fi">jukka.rahkonen@maanmittauslaitos.fi</a>>; Even Rouault <<a href="mailto:even.rouault@spatialys.com">even.rouault@spatialys.com</a>>;
<a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> RE: [gdal-dev] Bug in OGR library<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="FI"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I have 2 bilions of rows in the whole database and I try to export only one table which has 56 columns and 423 thousands of rows so
it’s not the issue with Microsoft Excel limitation. I can’t write even a single row (error
<i>Feature creation error (OGR error: negative FID are not supported) </i>occurs immediately),<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Marta Kopszak<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="FI" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US">Hi,<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="FI" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US">Could you clarify a bit? You have billion rows in the database table but xlsx and ods can support at maximum a bit more than one million
rows (1,048,576). You can’t imagine to have success with converting the whole table. Or is your problem that you can’t write even a single row from the source table because the value of the ID key is too big for xlsx/ods formats?<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US">-Jukka Rahkonen-<o:p></o:p></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span lang="FI" style="font-size:11.0pt;font-family:"Calibri",sans-serif">Lähettäjä:</span></b><span lang="FI" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> gdal-dev <<a href="mailto:gdal-dev-bounces@lists.osgeo.org">gdal-dev-bounces@lists.osgeo.org</a>>
<b>Puolesta </b>Kopszak Marta - Partner Hurt<br>
<b>Lähetetty:</b> maanantai 10. tammikuuta 2022 14.34<br>
<b>Vastaanottaja:</b> Even Rouault <<a href="mailto:even.rouault@spatialys.com">even.rouault@spatialys.com</a>>;
<a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
<b>Aihe:</b> Re: [gdal-dev] Bug in OGR library<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="FI"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Hi Even,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Thanks for your replay. More info about the problem:<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Error:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">
<br>
<i>Save error:<o:p></o:p></i></span></p>
<p class="MsoNormal"><i><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Export to vector file failed.<o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Error: Feature write errors:<o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Feature creation error (OGR error: negative FID are not supported)<o:p></o:p></span></i></p>
<p class="MsoNormal"><i><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></i></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">The database has more than 2 bilons of records (record – I mean record in the database table). The error occurs when I export a single
table witout the primary key to .xlsx. <o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">I suppose that when the table doesn’t have the primary key it use tid – the tuple identifier, and the size of tid may exceed the size
of a 32-bit integer. It cause the problem to export this table to .xlsx format by OGR library.
<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Regards,<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US">Marta Kopszak<o:p></o:p></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1F497D;mso-fareast-language:EN-US"><o:p> </o:p></span></p>
<div>
<div style="border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> gdal-dev <<a href="mailto:gdal-dev-bounces@lists.osgeo.org">gdal-dev-bounces@lists.osgeo.org</a>>
<b>On Behalf Of </b>Even Rouault<br>
<b>Sent:</b> Monday, January 10, 2022 11:42 AM<br>
<b>To:</b> <a href="mailto:gdal-dev@lists.osgeo.org">gdal-dev@lists.osgeo.org</a><br>
<b>Subject:</b> Re: [gdal-dev] Bug in OGR library<o:p></o:p></span></p>
</div>
</div>
<p class="MsoNormal"><o:p> </o:p></p>
<p>Marta,<o:p></o:p></p>
<p>What is the bug exactly ?<o:p></o:p></p>
<p>"2 billion records" : by record, you mean cells, like a sheet with 2048 columns and 1048576 rows ? By quickly looking at the code I can't see an obvious limitation, but that doesn't mean anything. Closer investigation would be needed. Please file an issue
at <a href="https://github.com/OSGeo/gdal/issues/new">https://github.com/OSGeo/gdal/issues/new</a> with exact details how to reproduce (number of rows/features, and columns/fields in particular).<o:p></o:p></p>
<p>But even if the writer side would work, the OGR reader side would probably have issues as the whole spreadsheet will be loaded into RAM and for 2 billion cells, I would expect at least 32 or more GB of RAM to be needed. Perhaps spreadsheet software are able
to cope with that in a smarter way.<o:p></o:p></p>
<p>Even<o:p></o:p></p>
<div>
<p class="MsoNormal">Le 10/01/2022 à 11:27, Kopszak Marta - Partner Hurt a écrit :<o:p></o:p></p>
</div>
<blockquote style="margin-top:5.0pt;margin-bottom:5.0pt">
<div>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Dear Sirs,<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">I ran into an error when exporting data through the OGR library to the .xlsx and .ods format. The bug occurs when there are more than 2 billion records in the database. I know the
problem was solved for other formats, e.g. csv. Will the problem be solved also for other formats (e.g. xlsx, .ods)? If this is not the correct e-mail, please forward this e-mail to the appropriate person.<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"> <o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Kind regards,<o:p></o:p></p>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto">Marta Kopszak<o:p></o:p></p>
<table class="MsoNormalTable" border="0" cellspacing="3" cellpadding="0">
<tbody>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt"></td>
</tr>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><a href="https://www.orange.pl/" target="_blank"><b><span style="color:windowtext;text-decoration:none">Marta Kopszak</span></b><span style="color:windowtext;text-decoration:none"><br>
Współpracownik</span></a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></p>
</td>
</tr>
<tr>
<td style="padding:.75pt .75pt .75pt .75pt">
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><a href="https://www.orange.pl/" target="_blank"><span style="color:windowtext;text-decoration:none">Rozwój Sieci Stacjonarnej, Wydział Rozwoju Planowania<br>
Orange Polska, gen. Romualda Traugutta 55, 50-416 Wrocław | </span>RODO - informacja o danych<span style="color:windowtext;text-decoration:none">
</span></a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></p>
</td>
</tr>
</tbody>
</table>
<p class="MsoNormal" style="mso-margin-top-alt:auto;mso-margin-bottom-alt:auto"><a href="https://www.orange.pl/" target="_blank"><span style="color:windowtext;text-decoration:none"> </span></a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></p>
</div>
<p class="MsoNormal"><a href="https://www.orange.pl/" target="_blank"><span style="color:windowtext;text-decoration:none"><br>
</span></a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></p>
<pre><span style="font-size:10.0pt;font-family:"Courier New""><a href="https://www.orange.pl/" target="_blank"><span style="color:windowtext;text-decoration:none">_______________________________________________</span></a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></span></pre>
<pre><span style="font-size:10.0pt;font-family:"Courier New""><a href="https://www.orange.pl/" target="_blank"><span style="color:windowtext;text-decoration:none">gdal-dev mailing list</span></a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></span></pre>
<pre><span style="font-size:10.0pt;font-family:"Courier New""><a href="https://www.orange.pl/" target="_blank">gdal-dev@lists.osgeo.org</a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></span></pre>
<pre><span style="font-size:10.0pt;font-family:"Courier New""><a href="https://www.orange.pl/" target="_blank">https://lists.osgeo.org/mailman/listinfo/gdal-dev</a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></span></pre>
</blockquote>
<pre><span style="font-size:10.0pt;font-family:"Courier New""><a href="https://www.orange.pl/" target="_blank"><span style="color:windowtext;text-decoration:none">-- </span></a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></span></pre>
<pre><span style="font-size:10.0pt;font-family:"Courier New""><a href="https://www.orange.pl/" target="_blank">http://www.spatialys.com</a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></span></pre>
<pre><span style="font-size:10.0pt;font-family:"Courier New""><a href="https://www.orange.pl/" target="_blank"><span style="color:windowtext;text-decoration:none">My software is free, but my time generally not.</span></a><span class="MsoHyperlink"><span style="color:windowtext;text-decoration:none"><o:p></o:p></span></span></span></pre>
</div>
</body>
</html>