Bug 108279 - export to xlsx shows only 65k rows when imported into SPSS
Summary: export to xlsx shows only 65k rows when imported into SPSS
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Calc (show other bugs)
Version:
(earliest affected)
Inherited From OOo
Hardware: All All
: medium normal
Assignee: Eike Rathke
QA Contact:
URL:
Whiteboard: target:5.5.0 target:5.4.0.1 target:5.3.4
Keywords:
Depends on:
Blocks:
 
Reported: 2017-06-01 12:05 UTC by Andy
Modified: 2017-06-07 16:06 UTC (History)
0 users

See Also:
Crash report or crash signature:


Attachments
xlsx saved by LO causing data loss when imported into SPSS (1.33 MB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-06-01 12:06 UTC, Andy
Details
the same xlsx saved by Excel that work OK when imported into SPSS (1.87 MB, application/vnd.openxmlformats-officedocument.spreadsheetml.sheet)
2017-06-01 12:07 UTC, Andy
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Andy 2017-06-01 12:05:58 UTC
Description:
When you save a spreadsheet file to XLSX format, in Libreoffice or ms Excel the rows are shown in full when reopening. Instead if yuu impport the xlsx file into SPSS software, only the first 65k rows of the sheet will be imported. This does not happen when saving the file with MS Excel, so the fault lies with the xlsx export procedure in LO. If you are not aware of this and do not check thoroughly the results obtained in SPSS, you could end up with HUGE loss of data.

Steps to Reproduce:
1. The file "more than 65000.xlsx" created with LO 5.3.1.2, has 80.001 rows. These are all saved and shown also if you open the file in MS Excel.
2.Now open it with SPSS (22 or 23): only the first 65k rows will be imported, any further data will be lost
3. The file "more than 65000 resaved by microsoft.xlsx" if the same as above, but it was re-saved using MS Excel 2013 without any change.
4. Now if you import the latter into SPSS, all the rows will be correctly imported, not only the first 65k!

Actual Results:  
xlsx created or saved by LO have something inside that prevents SPSS from reading all data, which is instead read if the xlsx file was saved by MS Excel.
Since Excel 97-2003 supported only 65k rows, maybe there is something in the xlsx file that confuses SPSS into thinking it is an older file?

Expected Results:
All data within the spreadsheet range (1 million rows) should be read correctly by SPSS


Reproducible: Always

User Profile Reset: No

Additional Info:


User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:53.0) Gecko/20100101 Firefox/53.0
Comment 1 Andy 2017-06-01 12:06:42 UTC
Created attachment 133781 [details]
xlsx saved by LO causing data loss when imported into SPSS
Comment 2 Andy 2017-06-01 12:07:18 UTC
Created attachment 133782 [details]
the same xlsx saved by Excel that work OK when imported into SPSS
Comment 3 Eike Rathke 2017-06-01 18:26:47 UTC
It's probably (can't check as I don't have SPSS) the <dimension> element (here in ./xl/worksheets/sheet1.xml) that is
<dimension ref="A1:B65536"/>
instead of
<dimension ref="A1:B80001"/>

Apparently SPSS ignores any data beyond but sane applications don't.
Comment 4 Eike Rathke 2017-06-01 18:48:01 UTC
Note that the first file https://bugs.documentfoundation.org/attachment.cgi?id=133781 actually contains only 65536 rows, so the dimension thing is just an assumption. Apparently you attached a wrong file.
Comment 5 Commit Notification 2017-06-01 18:52:42 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=259c2409310814ae62c8d0aefef19a71e8d88dbe

Resolves: tdf#108279 dimension rows are 32-bit and don't wrap at 64k

It will be available in 5.5.0.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 6 Eike Rathke 2017-06-01 19:02:28 UTC
Pending review
https://gerrit.libreoffice.org/38330 for 5-4
https://gerrit.libreoffice.org/38331 for 5-3
https://gerrit.libreoffice.org/38332 for 5-3-4
Comment 7 Commit Notification 2017-06-01 20:25:53 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=e81a6b8a7531cb9f3066234cc4cda53ed7ecd8a9&h=libreoffice-5-4

Resolves: tdf#108279 dimension rows are 32-bit and don't wrap at 64k

It will be available in 5.4.0.1.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 8 Commit Notification 2017-06-01 21:27:37 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=190c0d9ed02a5ed0659b07955b839bb16b1ba638&h=libreoffice-5-3

Resolves: tdf#108279 dimension rows are 32-bit and don't wrap at 64k

It will be available in 5.3.5.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 9 Commit Notification 2017-06-06 13:02:32 UTC
Eike Rathke committed a patch related to this issue.
It has been pushed to "libreoffice-5-3-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=8273f90c8438f7ee7ecf15cb7825c71855b2fff5&h=libreoffice-5-3-4

Resolves: tdf#108279 dimension rows are 32-bit and don't wrap at 64k

It will be available in 5.3.4.

The patch should be included in the daily builds available at
http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
http://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.