How to read zip file in Azure Synapse Notebook with extension of .zip.

SaiSekhar, MahasivaRavi (Philadelphia) 60

Hi Team,

Could you please advise the pyspark code, how to read zip files in synapse notebook.
Example: File_Name - samplefile.zip

Note: In the samplefile.zip we have multiple csv files, please let us know. The file Extension is only with .zip.

Could you please advice how to extract zip files in synapse notebook, we have multiple CSV files in it.
please provide your inputs.

phemanth 11,295 Reputation points Microsoft Vendor

2024-11-08T18:28:51.7833333+00:00
@SaiSekhar, MahasivaRavi (Philadelphia)

Thanks for reaching out to Microsoft Q&A.

To read and extract CSV files from a ZIP file in an Azure Synapse Notebook using PySpark, you can follow these steps:

Upload the ZIP file to your Synapse workspace or ensure it’s accessible via a path (like Azure Blob Storage).

Use the zipfile module to extract the contents of the ZIP file. Here’s a sample code snippet to help you get started:

from pyspark.sql import SparkSession import zipfile import os # Initialize Spark session spark = SparkSession.builder.appName("ZipFileReader").getOrCreate() # Path to the ZIP file zip_file_path = "/path/to/samplefile.zip" # Update with your ZIP file path extract_path = "/path/to/extracted_files" # Directory to extract files # Create a directory to extract files os.makedirs(extract_path, exist_ok=True) # Extract the ZIP file with zipfile.ZipFile(zip_file_path, 'r') as zip_ref: zip_ref.extractall(extract_path) # List all CSV files in the extracted directory csv_files = [f for f in os.listdir(extract_path) if f.endswith('.csv')] # Read each CSV file into a DataFrame and show the content for csv_file in csv_files: csv_file_path = os.path.join(extract_path, csv_file) df = spark.read.csv(csv_file_path, header=True, inferSchema=True) print(f"Contents of {csv_file}:") df.show()

Explanation:

Import Libraries: You need zipfile for handling ZIP files and os for file operations. Spark Session: Initialize a Spark session to work with DataFrames. Extract ZIP: The ZIP file is extracted to a temporary directory. Read CSV Files: After extraction, each CSV file is read into a Spark DataFrame, which you can then manipulate or analyze as needed.

Notes:

Make sure to replace "/path/to/samplefile.zip" with the actual path to your ZIP file.

The extracted files will be stored in the /tmp/extracted_files directory, which is temporary and will be cleared after the session ends

Hope this helps. Do let us know if you any further queries.
SaiSekhar, MahasivaRavi (Philadelphia) 60 Reputation points

2024-11-11T07:49:42.7066667+00:00
Hi @phemanth ,
Thanks for the update,
Could you please let us know how to provide the zip_file_path and extract_path.
we are giving as below in synapse but if is failing with below error.

FileNotFoundError: [Errno 2] No such file or directory:

zip_file_path = "abfss://XXX@XXXX.dfs.core.windows.net/bronze/XX/XX_Zip.zip" # Update with your ZIP file path extract_path = "abfss://XX@XXX.dfs.core.windows.net/bronze/XX"

Note: Could you please how the file path will work in synapse notebook.
phemanth 11,295 Reputation points Microsoft Vendor

2024-11-11T18:05:58.14+00:00
@SaiSekhar, MahasivaRavi (Philadelphia)

To specify the zip_file_path and extract_path in Azure Synapse Notebooks, you need to ensure that you’re using the correct format and that the paths are accessible. Here’s how you can do it:

Using Azure Blob Storage Paths

When using Azure Data Lake Storage (ADLS) or Blob Storage, the paths should be in the format:

zip_file_path = "abfss://<filesystem>@<account>.dfs.core.windows.net/<path_to_your_zip_file>/samplefile.zip" extract_path = "abfss://<filesystem>@<account>.dfs.core.windows.net/<path_to_extract_directory>"

Ensure Proper Permissions

Make sure that your Synapse workspace has the necessary permissions to access the specified paths. You may need to check the access policies for your storage account.

Using the dbutils Library

In Synapse, you can use the dbutils library to work with files in Azure Blob Storage. Here’s how you can modify your code:

# Set the paths zip_file_path = "abfss://<filesystem>@<account>.dfs.core.windows.net/bronze/XX/XX_Zip.zip" extract_path = "/tmp/extracted_files" # Local path for extraction # Create a directory to extract files locally dbutils.fs.mkdirs(extract_path) # Download the ZIP file to the local path dbutils.fs.cp(zip_file_path, "file:" + zip_file_path) # Extract the ZIP file import zipfile import os with zipfile.ZipFile(zip_file_path, 'r') as zip_ref: zip_ref.extractall(extract_path) # List all CSV files in the extracted directory csv_files = [f for f in os.listdir(extract_path) if f.endswith('.csv')] # Read each CSV file into a DataFrame and show the content for csv_file in csv_files: csv_file_path = os.path.join(extract_path, csv_file) df = spark.read.csv("file:" + csv_file_path, header=True, inferSchema=True) print(f"Contents of {csv_file}:") df.show()

Key Points:

Local Extraction: The extraction is done to a local path (like /tmp/extracted_files), which is temporary and will be cleared after the session ends.

File Access: Use dbutils.fs.cp to copy the file from Blob Storage to the local file system before extraction.

File Prefix: When reading the CSV files, prefix the path with "file:" to indicate that it’s a local file
phemanth 11,295 Reputation points Microsoft Vendor

2024-11-12T18:07:24.2666667+00:00

@SaiSekhar, MahasivaRavi (Philadelphia) We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Share via

How to read zip file in Azure Synapse Notebook with extension of .zip.

Your answer