Reading .csv stored in Azure Blob Storage from Excel

[Federico Fontana]

I've been able to create a storage account, then a container, than a blob storing a .csv file. I've successfully been able to download the .csv file from Azure itself and using Python as well via the azure-storage package. This is to confirm that external sources (i.e. Python) are able to import/export blobs. The .csv stores a numeric table with header in the first row.

The second step is to import the same data in Excel 2016. The steps that I'm following from Excel are:

New Query --> From Azure --> From Microsoft Azure Blob Storage --> provide <Account_name> and <Key> --> Navigator. From here, I can see that dataset.csv file in the container.

 However, clicking the button "load" in the Navigator only allows to download the table in the previous picture in excel, and not the data stored in those files.

 I've explored the add ons "Design" and "Query" to look for a button to download the data stored in those blobs but unsuccessfully.

Question: how to import the numberic data stored in one of the .csv in Azure Blobs listed in the Wizard?

[#2 · Accepted solution]

Hi,  don't load the connection, edit the connection

then in the Power Query screen, filter the 'Name' column to get the files you want, then click on the column 'Content' to actual get the data.

Sorry my example sheets have only one column, but you can use the Power Query features to transform the files.

[#3]

Hi,
I've had the same issue with excel 365. The only difference is that when i attempt to do as above, and use the power query feature it comes up with an error as follows

Unable to connect

We encountered an error while trying to connect.
Details: "We found extra characters at the end of the JSON input."

Can you help me with this ?
Des

[#4]

@DesOwens 

Perhaps that Workaround to connect to Power BI dataflows from Power Query in Excel – (thebiccountant.com) could help

[#5]

Hi Sergei,
Unfortunately that appears way too difficult for my abilities, I don't think it will help me
Thanks

Des

[#6]

@DesOwens 

Sorry, I personally have no practical experience with that.

[#7]

Thanks Sergei,
I have managed to download what I need as a JSON file and then use an online converter to change to a csv file which I can work with !!
So I'm happy enough, pity the Query would not work for me.
Thanks for your input

Des

[#8]

@DesOwens 

Great to know you sorted this out, thank you for the update

[#9]

To read a CSV file stored in Azure Blob Storage from Excel in Python, you can use the azure-storage-blob library and the pandas library.

Here's an example of how you can do it:

# Import the necessary libraries
from azure.storage.blob import BlobServiceClient, BlobClient, ContainerClient
import pandas as pd

# Get the connection string for your storage account
# Replace `<your_storage_account_name>` and `<your_storage_account_key>` with your storage account name and key
connection_string = "DefaultEndpointsProtocol=https;AccountName=<your_storage_account_name>;AccountKey=<your_storage_account_key>;EndpointSuffix=core.windows.net"

# Create a BlobServiceClient using the connection string
blob_service_client = BlobServiceClient.from_connection_string(connection_string)

# Get the container client for the container where your CSV file is stored
# Replace `<your_container_name>` with the name of your container
container_client = ContainerClient.from_connection_string(connection_string, container_name="<your_container_name>")

# Get the blob client for the CSV file
# Replace `<your_csv_file_name>` with the name of your CSV file
blob_client = BlobClient.from_connection_string(connection_string, container_name="<your_container_name>", blob_name="<your_csv_file_name>.csv")

# Download the CSV file from Azure Blob Storage
csv_stream = blob_client.download_blob()

# Read the CSV file into a pandas DataFrame
df = pd.read_csv(csv_stream)

# Now you can work with the data in the DataFrame
print(df)

This code will connect to your storage account, download the CSV file from the specified container and blob, and read the contents of the CSV file into a pandas DataFrame. You can then work with the data in the DataFrame as needed.

I hope this helps!