Using Export-CSV in Azure Automation


Whether you are just getting started with Azure Automation or you are pretty well in tune with it, there will likely come a point where you will want to capture the output of your scripts to a CSV. With Azure Automation, your scripts are run in a virtual environment and when you output your data to a file/csv it will only be saved in that temporary instance from where that script was executed. After the script is complete, that data that you 'thought' was going to persist is deleted. In order to capture that information and persist it you have to save it to an external environment. You do have a few options, but the most cost effective and intuitive approach is to save it to a blob storage account.


  1. Azure RM Module must be installed in Azure Automation. You can accomplish this by going to the PowerShell Gallery and having it "Deploy[ed] to Azure Automation"
  2. Azure RM Module installed on your local workstation. If your running PowerShell 3.0 or later, you can run Install-Package AzureRM as an administrator.
  3. Azure Subscription

How To

Creating a Storage Account

  1. First create a Storage Account. If you don't already have one, you'll to connect to Azure Resource Manager through your local PowerShell client:
  2. Once you have authenticated, ensure that you are connected to the correct subscription. You can view your subscriptions but using Get-AzureRmSubscription.
    Get-AzureRmSubscription -SubscriptionName "Office 365 Test and Validations" | Select-AzureRmSubscription
        Environment           		: AzureCloud
        Account               		:
        TenantId             		: 72f988bf-86f1-41af-91ab-212cd011db47
        SubscriptionId        		: 1edb6f86-8931-40e4-a70e-d2128ce0ee32
        SubscriptionName      		: Microsoft Azure Internal Consumption
        CurrentStorageAccount 		:
  3. Next you'll want to create a Storage Account:
    New-AzureRmStorageAccount -SkuName “<"Standard_LRS,Standard_ZRS,Standard_GRS,Standard_RAGRS,Premium_LRS"> -ResourceGroupName  -Name  -Location 
    Sample: New-AzureRmStorageAccount -SkuName "Standard_LRS” -ResourceGroupName CloudLogicRG -Name clstorageacct -Location “Central US”
  4. And finally, create a Storage Account Container:
    New-AzureStorageContainer -Name "savedfiles"

Setting up Automation to Export to CSV

  1. Now that the foundation is established, you'll want to create a new Runbook of the type "PowerShell".

  2. Connect to AzureRM with a Service Principal Account
    $connectionName = "AzureRunAsConnection"
        # Get the connection "AzureRunAsConnection "
        $servicePrincipalConnection=Get-AutomationConnection -Name $connectionName         
        "Logging in to Azure..."
        Add-AzureRmAccount `
            -ServicePrincipal `
            -TenantId $servicePrincipalConnection.TenantId `
            -ApplicationId $servicePrincipalConnection.ApplicationId `
            -CertificateThumbprint $servicePrincipalConnection.CertificateThumbprint 
    catch {
        if (!$servicePrincipalConnection)
            $ErrorMessage = "Connection $connectionName not found."
            throw $ErrorMessage
        } else{
            Write-Error -Message $_.Exception
            throw $_.Exception
  3. Select the subscription you are going to work with
    Select-AzureRmSubscription -SubscriptionId '<SubscriptionID>'
  4. Set the Current Storage Account to the approperiate location
    Set-AzureRmCurrentStorageAccount -StorageAccountName  -ResourceGroupName 
  5. Store O365 Credentials – For sake of clarity, we are storing the credentials in a variable. For security reasons, it is best to securely store the credentials and call them when required.
    $MSOUser = ""
    $MSOPwd = ConvertTo-SecureString -String "" -AsPlainText -Force
    $MSOCred = New-Object –TypeName System.Management.Automation.PSCredential –ArgumentList $MSOUser, $MSOPwd
  6. Connect to Office 365
    Connect-MsolService –Credential $MSOCred
  7. Capture All MSOLUsers in a variable
    $users=get-msoluser -All
  8. Export CSV to Local Automation Storage
    foreach ($user in $users){
        write $user
        $user | export.csv $FileName -NoTypeInformation -Append
  9. Capture the file that is local to automation and save to Storage Blob
    Set-AzureStorageBlobContent -Container savedfiles -File AutomationFile.csv -Blob SavedFile.csv


  • The real magic is done with the very last cmdlet "Set-AzureStorageBlobContent -Container savedfiles -File AutomationFile.csv -Blob SavedFile.csv". The container should be the name of the container that you are saving the file to; in association to the Storage Account your connected to. -File is the name of the local (to the automation) file you wish to make a copy of and the -Blob will be the (new) name of the file that is created in the container.
  • You can use Microsoft Storage Explorer to view the content of the storage account and download the blobs
  • To download the full Automation Script please see: AzureAutomationExportCSV
Comments (2)
  1. Craig Franzen says:

    Paul, in your article, there is a variable, $FileName, in line 8 of the second set of instructions. I do not see this variable defined anywhere within your previous steps. Where would you define this, what should that definition look like?

    1. PaulKotylo says:

      My apologies for the oversight. I do believe that I may also have typo in the export process itself. Instead of export.csv it should be export-csv. $filename can really be any value that you wish, you just have to set it before you export to it.

      Azure Automation basically saves the .csv to a local, temporary instance. You then have to move it from the temporary instance to storage blob it order to persist the data after the script is executed.

      # Capture All MSOLUsers
      $filename = “DesiredFilename.csv”
      $users=get-msoluser -All

      # Export CSV to Local Automation Storage
      foreach ($user in $users){
      write $user
      $user | export-csv $FileName -NoTypeInformation -Append

Comments are closed.

Skip to main content