Read multiple sheets excel workbook present in blob storage through azure ml jupyter notebook using datastore

Question

I want to read excel file having multiple sheets in azure ml notebook using datastore.

Answer 1

Thanks for the details, Is your data in csv format?. Here is the sample notebook to explore data.

1. Import necessary libraries: azureml.core and pandas.
2. Get the workspace.
3. Get the datastore.
4. Download the file from the datastore to the compute instance.
5. Use pandas to read the Excel file with multiple sheets. Replace 'your_datastore_name' and 'your_file_name.xlsx' with your actual datastore name and file name. If you want to read a specific sheet, replace 'Sheet1' with your actual sheet name. If you want to read all sheets, use sheet_name=None to read all sheets to a map.

from azureml.core import Workspace, Datastore
import pandas as pd
# Get the workspace
ws = Workspace.from_config()
# Get the datastore
ds = Datastore.get(ws, 'your_datastore_name')
# Download the file from the datastore to the compute instance
ds.download(target_path='.', prefix='your_file_name.xlsx')
# Use pandas to read the Excel file with multiple sheets
xls = pd.ExcelFile('your_file_name.xlsx')
# To read a specific sheet to DataFrame
df1 = pd.read_excel(xls, 'Sheet1')
# To read all sheets to a map
dict_df = pd.read_excel(xls, sheet_name=None)