How to import/export data with Excel to/from Azure table storage

Introduction

This PowerShell script sample shows how to import/export data with Excel to/from Azure table storage.

Scenarios

Sometimes, users wants to import about 100,000 items to Azure table storage or to export the entities of table storage to do some analysis. But there is no good way to copy/paste the result, this script sample will help users to import/export data with Excel to/from Azure table storage.

Script

Step 1: From the Start Screen or the Start Menu, search for Windows Azure PowerShell. Right-click the Windows Azure PowerShell entry and select Run as Administrator.

Note: If Windows Azure PowerShell is not installed, see Getting Started with Windows Azure PowerShell Cmdlets for installation and configuration information.

Step2: To import modules into the Windows PowerShell session, type the command: Import-Module <Script Module Path> at the prompt.

For example, type Import-Module C:\Script\ConfigurePowerOptions

This is shown in the following figure.

Step2: When the script module finishes loading, you can type the Get-Module cmdlet to find the modules that have already been imported into your current session, this is shown in the following figure.

Step3: Then, you can use the Get-Command cmdlet to find all available commands. To find all available cmdlets in a module, type Get-Command -Module< module-name>, this is shown in the following figure.

Example

Example 1: This command shows how to export the entities of table storage and saves them to a csv file named 'ShemasTable'.

Type Export-AzureTableStorage -StorageAccountName "andersstorageaccount" -TableName "SchemasTable" -Path "C:\Tables\" command in the Windows PowerShell Console.

When the command finishes running, you will find a csv file under the C:\Tables\ directory. We can open the csv file and have a look at it, the following data is what we want.

Example 2: This command shows how to import the entities of the csv file into a table storage.

Type Import-AzureTableStorage -StorageAccountName "andersstorageaccount" -TableName "SchemasTable2" -Path "C:\Tables\SchemasTable.csv" command in the Windows PowerShell Console.

For this example, we use the Azure Storage Explorer to check if it is success. As you can see, we have successfully imported the entities of the csv file into a table storage named SchemasTable2.

Here are some code snippets for your references.

$Creds = New-Object Microsoft.WindowsAzure.Storage.Auth.StorageCredentials("$StorageAccountName","$StorageAccountKey")
$CloudStorageAccount = New-Object Microsoft.WindowsAzure.Storage.CloudStorageAccount($Creds, $true)
$CloudTableClient = $CloudStorageAccount.CreateCloudTableClient()
$Table = $CloudTableClient.GetTableReference($TableName)

$Query = New-Object "Microsoft.WindowsAzure.Storage.Table.TableQuery"
$Datas = $Table.ExecuteQuery($Query)
                        
$ExportObjs = @()
                        
Foreach($Data in $Datas)
{
  
    $Obj = New-Object PSObject

    $Obj | Add-Member -Name PartitionKey -Value $Data.PartitionKey -MemberType NoteProperty
    $Obj | Add-Member -Name RowKey -Value $Data.RowKey -MemberType NoteProperty 

    $Data.Properties.Keys | Foreach{$Value = $data.Properties[$_].PropertyAsObject;
    $Obj | Add-Member -Name $_ -Value $value -MemberType NoteProperty; }

    $ExportObjs += $Obj
} 

#Export the entities of table storage to csv file. 
$ExportObjs | Export-Csv "$Path\$TableName.csv" -NoTypeInformation
Write-Host "Successfully exported the table storage to csv file."

$Creds = New-Object Microsoft.WindowsAzure.Storage.Auth.StorageCredentials("$StorageAccountName","$StorageAccountKey") 
$CloudStorageAccount = New-Object Microsoft.WindowsAzure.Storage.CloudStorageAccount($Creds, $true) 
$CloudTableClient = $CloudStorageAccount.CreateCloudTableClient() 
$Table = $CloudTableClient.GetTableReference($TableName) 
 
$Query = New-Object "Microsoft.WindowsAzure.Storage.Table.TableQuery" 
$Datas = $Table.ExecuteQuery($Query) 
                         
$ExportObjs = @() 
                         
Foreach($Data in $Datas) 
{ 
   
    $Obj = New-Object PSObject 
 
    $Obj | Add-Member -Name PartitionKey -Value $Data.PartitionKey -MemberType NoteProperty 
    $Obj | Add-Member -Name RowKey -Value $Data.RowKey -MemberType NoteProperty  
 
    $Data.Properties.Keys | Foreach{$Value = $data.Properties[$_].PropertyAsObject; 
    $Obj | Add-Member -Name $_ -Value $value -MemberType NoteProperty; } 
 
    $ExportObjs += $Obj 
}  
 
#Export the entities of table storage to csv file.  
$ExportObjs | Export-Csv "$Path\$TableName.csv" -NoTypeInformation 
Write-Host "Successfully exported the table storage to csv file." 

Prerequisite

Windows PowerShell 3.0

Windows 8

Microsoft All-In-One Script Framework is an automation script sample library for IT Professionals. The key value that All-In-One Script Framework is trying to deliver is Scenario-Focused Script Samples driven by IT Pros' real-world pains and needs. The team is monitoring all TechNet forums, IT Pros' support calls to Microsoft, and script requests submitted to TechNet Script Repository. We collect frequently asked IT scenarios, and create script samples to automate the tasks and save some time for IT Pros. The team of All-In-One Script Framework sincerely hope that these customer-driven automation script samples can help our IT community in this script-centric move.