Zip64 compatibility with MS Excel and DuckDB Excel

[FlipperPA]

I'm trying to add 64-bit ZIP support to DuckDB's Excel extension, which uses minizip-ng to create the xlsx file (which is just a ZIP archive). This is my first time working with minizip-ng, so apologies in advance for anything obvious I may have missed.

My work-in-progress PR is here: https://github.com/duckdb/duckdb-excel/pull/64/files

When using MZ_ZIP64_DISABLE to create the xlsx zip file, it works well, and results pulled from a database into an Excel zip are readable by several Excel readers I tested, including Microsoft Excel, Google Sheets, and Python polars. It won't read in OpenPyXL because of the lack of xl/sharedStrings.xml, but that's a shortcoming of OpenPyXL.

When I compiled using MZ_ZIP64_AUTO instead, it seems to create a Zip64 regardless of whether it is needed. This causes the resulting zip file not to open the Microsoft Excel, with an error about it being invalid.

Here is the head of the "bad" version:

$ head -c 64 new_export.xlsx | xxd
00000000: 504b 0304 2d00 0800 0800 0000 0000 0000 PK..-...........
00000010: 0000 ffff ffff ffff ffff 1800 1400 786c ..............xl
00000020: 2f77 6f72 6b73 6865 6574 732f 7368 6565 /worksheets/shee
00000030: 7431 2e78 6d6c 0100 1000 0000 0000 0000 t1.xml..........

Here is the head of the "good" version:

$ head -c 64 v1.3.0_export.xlsx | xxd
00000000: 504b 0304 1400 0800 0800 0000 0000 0000 PK..............
00000010: 0000 0000 0000 0000 0000 1800 0000 786c ..............xl
00000020: 2f77 6f72 6b73 6865 6574 732f 7368 6565 /worksheets/shee
00000030: 7431 2e78 6d6c bc9d 5f93 1c37 72ed 9faf t1.xml.._..7r...

Is there something I'm doing wrong while creating the file in zip_file.cpp? Thanks for your efforts creating this library, and for any insight you can send my way. It is much appreciated!

[nmoinvaz]

Best to disable MZ_ZIP_FLAG_DATA_DESCRIPTOR because it is incompatible with zips in zips. XLSX is a zip and you are putting in a zip, so the data descriptor streaming method has a hard time finding the end of the local zip entry.

[FlipperPA]

I'm actually not creating a zip within a zip. I'm only creating the xlsx file (itself a single ZIP); would that change things?

I initially attempted to only replace MZ_ZIP64_DISABLE with MZ_ZIP64_AUTO before adding the other three lines, including MZ_ZIP_FLAG_DATA_DESCRIPTOR. Let me try again.

[FlipperPA]

I've removed the flag, recompiled, and I'm having the same issue. Here's what unzip -l lists:

$ unzip -l n178gcwg5ao2mrm2.xlsx
Archive:  n178gcwg5ao2mrm2.xlsx
  Length      Date    Time    Name
---------  ---------- -----   ----
173528949  00-00-1980 00:00   xl/worksheets/sheet1.xml
     1054  00-00-1980 00:00   [Content_Types].xml
      587  00-00-1980 00:00   _rels/.rels
      807  00-00-1980 00:00   xl/workbook.xml
      565  00-00-1980 00:00   xl/_rels/workbook.xml.rels
     1365  00-00-1980 00:00   xl/styles.xml
      300  00-00-1980 00:00   docProps/app.xml
      638  00-00-1980 00:00   docProps/core.xml
---------                     -------
173534265                     8 files

And if I do a zip -FF, this may give a hint on what I'm doing wrong?

$ zip -FF n178gcwg5ao2mrm2.xlsx --out fixed.xlsx
Fix archive (-FF) - salvage what can
 Found end record (EOCDR) - says expect single disk archive
Scanning for entries...
 copying: xl/worksheets/sheet1.xml  (18828013 bytes)
 copying: [Content_Types].xml  (313 bytes)
 copying: _rels/.rels  (233 bytes)
 copying: xl/workbook.xml  (328 bytes)
 copying: xl/_rels/workbook.xml.rels  (218 bytes)
 copying: xl/styles.xml  (491 bytes)
 copying: docProps/app.xml  (181 bytes)
 copying: docProps/core.xml  (310 bytes)
Central Directory found...
EOCDR found ( 1 18831649)...

And this gives me an even-more-broken file:

$ unzip -l fixed.xlsx
Archive:  fixed.xlsx
  Length      Date    Time    Name
---------  ---------- -----   ----
        0  00-00-1980 00:00   xl/worksheets/sheet1.xml
        0  00-00-1980 00:00   [Content_Types].xml
        0  00-00-1980 00:00   _rels/.rels
        0  00-00-1980 00:00   xl/workbook.xml
        0  00-00-1980 00:00   xl/_rels/workbook.xml.rels
        0  00-00-1980 00:00   xl/styles.xml
        0  00-00-1980 00:00   docProps/app.xml
        0  00-00-1980 00:00   docProps/core.xml
---------                     -------
        0                     8 files

If you have any other ideas, I'm all ears. Thanks again for your time, I know it's valuable.

[pmqs]

Try running zipdetails against the broken xlsx file to see what exactly is being written to the zip file. If you also run it against a working xlsx file that you application creates, you can see what the differences are.

Also, found this writeup where someone was having trouble with z 84-bit xlsx file in a Java application. May, or may not, be relevant, but is an indication of how picky excel can be.

[nmoinvaz]

Try to fill out uncompressed_size in file_info when adding the entry to the zip and use MZ_ZIP64_AUTO. It looks like file descriptor might not be relevant in your case.

[Coeur]

For the record, I did solve the compatibility with MS Word in my project a few months ago by properly setting zipInfo->uncompressed_size. The issue report was ZipArchive/ZipArchive#693 where you can see that at the end of my analysis, I concluded that I had to set uncompressed_size to non-zero, which I did in ZipArchive/ZipArchive#726 where you can read my last paragraph titled "Second change" explaining the fix and where I strongly encourage to use MZ_ZIP64_AUTO as the only viable solution for large files compatibility (in case your MS Word doc is more than 2 GB). Then MS Word documents were properly supported. It should be the same for MS Excel documents.

[FlipperPA]

Thanks for the info and advice, it is much appreciated!

Part of the challenge here is that the major advantage for us using DuckDB is that we stream the results directly from a PostgreSQL database query into the xlsx ZIP. This avoids going to PostgreSQL -> CSV, then CSV -> XLSX, which requires column introspection to determine the column types for Excel.

This direct method allows us to know what columns types Excel needs from PostgreSQL directly by skipping the CSV step.

However, it does not allow us to know what the uncompressed size will be in advance. We might be able to guestimate with an EXPLAIN plan... but it may be a better overall strategy (in our case) to just let the edge cases that grow beyond 4 GB fail, and give the end user CSV. It amazes me that Microsoft Excel rejects ZIP64 xlsx files as invalid if they're not necessary. Argh!

I'm still new to all of this, so it is quite possible I'm missing something. I'm going to continue to noodle on this!

[nmoinvaz]

Why doesn't MZ_ZIP64_FORCE work for you?

[nmoinvaz]

Claude.AI generated this table from your two headers:

Byte-by-Byte Differences

Offset	Header 1	Header 2	Field	Difference
0x00-03	504b 0304	504b 0304	ZIP Signature	✅ Same
0x04-05	2d00	1400	Version needed	45 (4.5) vs 20 (2.0)
0x06-07	0800	0800	General purpose flags	✅ Same (data descriptor)
0x08-09	0800	0800	Compression method	✅ Same (deflate)
0x0A-0D	0000 0000	0000 0000	Last mod time/date	✅ Same (not set)
0x0E-11	0000 0000	0000 0000	CRC-32	✅ Same (in descriptor)
0x12-15	ffff ffff	0000 0000	Compressed size	Unknown (ZIP64) vs Unknown (standard)
0x16-19	ffff ffff	0000 0000	Uncompressed size	Unknown (ZIP64) vs Unknown (standard)
0x1A-1B	1800	1800	Filename length	✅ Same (24 bytes)
0x1C-1D	1400	0000	Extra field length	20 bytes vs 0 bytes
0x1E-31	786c 2f77...	786c 2f77...	Filename	✅ Same (xl/worksheets/sheet1.xml)
0x32-45	0100 1000...	Not present	Extra field (ZIP64)	ZIP64 extension vs None
0x46+	Not shown	bc9d 5f93...	Compressed data	Starts later vs Starts immediately

Don't know why Microsoft doesn't like ZIP64.

[nmoinvaz]

What does a XLSX file look like (specifically the zip header) when generated by Microsoft that has a 4GB file in it?

[FlipperPA]

@nmoinvaz Believe it or not, Microsoft Excel rejects as invalid a ZIP/xlsx that is 64-bit when it doesn't need to be. I have no idea why Microsoft designed it this way. I can open the generated xlsx file on Google Sheets, with Python polars, but it kicks this error when I try to open in O365 web Excel or the desktop version.

Generating a 4 GB Excel is a great idea. Let me try that tomorrow.

[nmoinvaz]

You could try:

• file_info->version_needed = 20;
• MZ_ZIP64_FORCE
• WITHOUT MZ_ZIP_FLAG_DATA_DESCRIPTOR.