3 ms·

I've been bitten by this exact issue (albeit on non critical data), save a file as XLS it will silently drop rows beyond 65k - save it as XLSX and it's all good

by aphrax 6y ago

I've been bitten by this exact issue (albeit on non critical data), save a file as XLS it will silently drop rows beyond 65k - save it as XLSX and it's all good..

shadowgovt 6y ago
The silent part there is the real surprising part to me. You'd think the exporter would die noisily if it still had data in the buffer and it ran out of file.
- TeMPOraL 6y ago
  I'm betting the exporter isn't running out of file, but out of loop counter.
  - shadowgovt 6y ago
    I mean, same difference. "I cannot generate a file representing state of data in RAM" is a 'fail noisy' error, not a 'fail silent' error.
    - TeMPOraL 6y ago
      (EDIT: numerous comments point out that Excel displays an explicit warning when truncating data above 256x65k. This comment was written under the assumption that it doesn't.) Not if it was a bug in the code. 65k sounds suspiciously close to the limit of 16 bit unsigned int. I'm guessing the exporter looked roughly like: int exportRow( ... ) { if( /* can't export or no more rows */ ) { return 0; } /* export row */ return ++someInternalCounter; } void export( ... ) { unsigned short nextRow = 0; do { nextRow = exportRow(...); } while(nextRow > 0); } In the above example, the export would silently stop after 65k entries. The way people write C in the wild, this wouldn't surprise me in the slightest. And with Microsoft being all about backwards compatibility, Excel probably defers to some ancient and long forgotten code when exporting to XLS.
NelsonMinar 6y ago
Silent? Are you positive? Not doubting you but if so; WTF?!
- rvba 6y ago
  I just tried to reproduce it and got a big warning message that says that data will be lost. You can disable this message (per file basis), if for some reason you dont want to see it. As far as I remember this message existed since Excel 2007 (which introduced .xlsx format), so the guy is simply lying.
  - aphrax 6y ago
    Certainly not lying but it was a few years ago. Not sure which version but not 365
- aphrax 6y ago
  Yep, I'm positive but as said elsewhere this is a few years back. I recall sending the data out and getting some pretty stern feedback (this is why I recall it) Didn't make the mistake after that...
hermitcrab 6y ago
Until you reach 1,048,576 and then XLSX silently drops rows.

by aphrax 6y ago

I've been bitten by this exact issue (albeit on non critical data), save a file as XLS it will silently drop rows beyond 65k - save it as XLSX and it's all good..

shadowgovt 6y ago

The silent part there is the real surprising part to me. You'd think the exporter would die noisily if it still had data in the buffer and it ran out of file.

TeMPOraL 6y ago
I'm betting the exporter isn't running out of file, but out of loop counter.
- shadowgovt 6y ago
  I mean, same difference. "I cannot generate a file representing state of data in RAM" is a 'fail noisy' error, not a 'fail silent' error.
  - TeMPOraL 6y ago
    (EDIT: numerous comments point out that Excel displays an explicit warning when truncating data above 256x65k. This comment was written under the assumption that it doesn't.) Not if it was a bug in the code. 65k sounds suspiciously close to the limit of 16 bit unsigned int. I'm guessing the exporter looked roughly like: int exportRow( ... ) { if( /* can't export or no more rows */ ) { return 0; } /* export row */ return ++someInternalCounter; } void export( ... ) { unsigned short nextRow = 0; do { nextRow = exportRow(...); } while(nextRow > 0); } In the above example, the export would silently stop after 65k entries. The way people write C in the wild, this wouldn't surprise me in the slightest. And with Microsoft being all about backwards compatibility, Excel probably defers to some ancient and long forgotten code when exporting to XLS.

NelsonMinar 6y ago

Silent? Are you positive? Not doubting you but if so; WTF?!

rvba 6y ago
I just tried to reproduce it and got a big warning message that says that data will be lost. You can disable this message (per file basis), if for some reason you dont want to see it. As far as I remember this message existed since Excel 2007 (which introduced .xlsx format), so the guy is simply lying.
- aphrax 6y ago
  Certainly not lying but it was a few years ago. Not sure which version but not 365
aphrax 6y ago
Yep, I'm positive but as said elsewhere this is a few years back. I recall sending the data out and getting some pretty stern feedback (this is why I recall it) Didn't make the mistake after that...

hermitcrab 6y ago

Until you reach 1,048,576 and then XLSX silently drops rows.