Unable to compress CSV file in Azure Synapse Analytics

Question

Hi All,

I am trying to compress one csv file to zip format using copy data activity. In sink I provided compression type as ZIP and provided extension for target file as .zip and able to get the compressed zip file.

When I extract it, it's actually giving a txt file with random name instead of the csv file. How to fix this issue ?

Answer 1

The issue you're facing with Azure Synapse Analytics (or Azure Data Factory) compressing a CSV file to ZIP format but extracting a text file with a random name is likely related to the way Synapse handles the file compression. By default, the service might not preserve the original file extension during the compression process.

To resolve this, try the following steps:

1. File Naming in Sink Configuration:
   • In your copy data activity, ensure that the sink file name is explicitly defined in the file name option, including the .csv extension.
   • For example, if your original file is mydata.csv, in the sink configuration where you're specifying the destination file, enter mydata.csv as the name.
2. Check the Compression Configuration:
   • Ensure that the compression type is correctly set to ZIP, and the file extension for the sink is set to .zip.
   • Double-check the file naming options under the sink settings to ensure that Azure Data Factory or Synapse doesn't rename the file randomly during compression.
3. Post-Copy Script (Optional):
   • If the issue persists, you can add a custom script step after the copy activity to rename the file inside the zip, so that the file retains the .csv extension.
4. Alternative Approach:
   • If Azure Synapse is consistently not handling the file extensions correctly within the ZIP file, you might also consider using a custom activity to compress the file. This could be done using an Azure function, Python, or other methods to have more control over the file compression process.

By making sure the original file name with the .csv extension is preserved throughout the process, you should be able to extract the compressed ZIP file correctly.