How to load a table with multiple zip files in Snowflake?

Question

I'm trying to upload data to a Snowflake table using a zip file containg multiple CSV files but I keep getting the following message:

Unable to copy files into table. Found character '\u0098' instead of field delimiter ',' File 'tes.zip', line 118, character 42 Row 110, column "TEST"["CLIENT_USERNAME":1] If you would like to continue loading when an error is encountered, use other values such as 'SKIP_FILE' or 'CONTINUE' for the ON_ERROR option. For more information on loading options, please run 'info loading_data' in a SQL client.

If I skip the errors some data load but it is like snowflake is not properly opening the zip file and I just get some random characters like if the zip file was only opened with notepad.

I tried changing the File Format Compression Method to all the available ones: Auto, Gzip, Deflate, Raw Deflate, Bz2m Brotli, Zstd and None. Getting different error messages.

I know my Zip file is compressed using the standard Deflate compression method but when I select this type I'm getting the following error:

Invalid data encountered during decompression for file: 'test.zip',compression type used: 'DEFLATE', cause: 'data error' The "Auto" method sends the same error message as None

I also tried with zip files containing only one file and I get the same errors. The files that worked correctly were an uncompressed one (CSV) and one compressed using GZ but I need this to work using a zip file containing multiple CSVs

Answer 1

A zip file is not a DEFLATE file, even though zip uses deflate. All the compression methods supported are single file compression methods. Where-as zip is a file archive, thus why it has many files, which would be similar to are tar.gz which is also not supported.

Thus you will ether need to uncompress your files yourself, in your S3 bucket, or alter your data export tool to conform.

CREATE FILE FORMAT help