How to get data from an Excel Document/ CSV stored in a blob storage with an automation Account/Runbook

Question

I have a blob storage for storing online files. After I uploaded the excel document I need to extract data from the excel sheet. Is it possible to get access to the blob storage with an automation account from a runbook? I need the data then to create SharePoint Sites and attributes. I haven't figured out how I can access the blob storage.

Thanks for the help!

Best regards

Matthias

Answer 1

I tried the below PowerShell script in the Automation runbook to extract the Excel/CSV file data from the Azure Storage.

Powershell script :

$Appid = "<app_ID>"
$PSWord = ConvertTo-SecureString -String "<app_secret>" -AsPlainText -Force
$tenantID = "<tenant_ID>"
$SubscriptionID = "<subscription_ID>"
$RG = "Vyshu"  
$storageAccountName = "<storage_name>"
$storageAccountKey = "<storage_key>"
$containerName = "<container_name>"
$blobname = "<xlsx_blobName>"

$Credential = New-Object -TypeName "System.Management.Automation.PSCredential" -ArgumentList $Appid, $PSWord

Connect-AzAccount -Credential $Credential -Tenant $tenantID -ServicePrincipal -Subscription $SubscriptionID

Install-Module -Name ImportExcel -Force -AllowClobber
Import-Module ImportExcel

Set-AzCurrentStorageAccount -ResourceGroupName $RG -Name $storageAccountName
$blob = Get-AzStorageBlob -Container kamcontainer -Blob $blobname | Get-AzStorageBlobContent

$localPath = "C:\app\test_file.xlsx"
$data = Import-Excel -Path $localPath
$data | Format-Table 

To read the blob data from the Storage, you need to assign the Storage Blob Data Reader role in the Storage account.

Output :

Below is the output for extracting the Excel file data from the storage.

Note :- To extract the CSV file data, you should replace it with the below code.

$localPath = "C:\app\blob.csv"
$data = Import-Csv -Path $localPath
$data | Format-Table 

Below is the output for extracting the CSV file data from the storage.