Azure: Migrate Excel worksheets to Table Storage

This article discusses migration of Microsoft Excel worksheets to Azure Table Storage and the need for such. Microsoft Azure is becoming a must have for enterprises. At the same time most enterprises and corporations still maintain their old data with Microsoft office software such as Microsoft Access and Microsoft Excel. Microsoft Access is truly a DBMS, that supports relational database and now Microsoft Azure has connectors for easily migrate Microsoft Access databases to SQL server and Azure SQL DB, you can read more on this here.

On the other hand many corporations have a lot of their data stored in Excel spreadsheets. We can easily upload them as .xlsx files but there we can not ensure the scalability of the data and we can not programatically retrieve necessary information from those Excel files from the VMs. This is not the real cloud infrastructure model we can achieve with Azure.  In order to take full advantage of Azure it is best to migrate the data to Azure Table Storage.

What is Azure Table Storage

In simple terms, Azure Table Storage is a non relational database that stores a huge amounts of structured data. This is ideal for storing structured and non-relational data. This has the ability to store TBs of structured data. This does not require joins, foreign keys and stored procedures to store data thus data stored here can be denormalized. So because of the above abilities the data stored in Azure Table Storage is arguably quicker to query. We can access the stored data from LINQ queries and .NET libraries of WCF data services. Price is very lower when comparing to SQL Azure.

Excel datasheets and Azure Tables

First of all Excel is a data sheet program not a database management system, still Excel has the abilities to run powerful queries to analyze the data and perform sophisticated calculations. So many enterprises and firms used Excel as their main data processing tool earlier. Now cloud has overtaken them and we need to feed the data that is in Excel to the cloud. The data in Excel is a kind of structured and non-relational data. So the best possible target to migrate Excel data in Azure Tables.

How to Migrate

The latest versions of Microsoft Excel has the ability to Migrate data from Azure Table to Excel but not from Excel to Azure Table. So we have to manually feed the data and its really a nightmare. Because we have to create Entity classes for the data we have and manually create entities for each row of the data and feed it. To ease this pain, there is a tool available for free called "Excel to Azure Table Storage." you can get it from here.

Using this tool, you can generate Entity class on C# for the Excel worksheet you need to migrate and the codes to insert the data for the Azure Table. Here you can learn how to execute it.

Lets take an example, consider the Excel sheet like below:

	A	B	C	D
1	Last Name	First Name	Email	Phone Number
2	John	Doe	john@doe.com	+9957785
3	Anne	Doe	anne@doe.com	+9955875

This sheet represents an entity with five fields that are 'Last Name', 'First Name', 'Email' and 'Phone Number' so the entity class for this will come like below as per the documentation available here:

public class FarmersEntity : TableEntity
{
    public FarmersEntity(string LastName, string FirstName)
    {
        this.PartitionKey = LastName;
        this.RowKey = FirstName;
    }
    public FarmersEntity() { }
    public string Email {get; set;}
 public string PhoneNumber {get; set;}
}

So this above tool generates the same class and saves it in the same directory as FarmersEntity.cs

And this will save another file for inserting data, that is more like:

 

//--new entity
temp_LastName = "John";
temp_FirstName = "Doe";
FarmersEntity Farmers0 = new FarmersEntity(temp_LastName, temp_FirstName);
Farmers0.Email = "john@doe.lk";
Farmers0.PhoneNumber = "9957785";
batchOperation.Insert(Farmers0);
//--new entity
temp_LastName = "Anne";
temp_FirstName = "Doe";
FarmersEntity Farmers1 = new FarmersEntity(temp_LastName, temp_FirstName);
Farmers1.Email = "anne@doe.lk";
Farmers1.PhoneNumber = "9955875";
batchOperation.Insert(Farmers1);

This will be saved as DataInsert.cs in the same directory, and we have to just copy this and paste it where we are invoking the data insertion method. So the full insertion method will come something like the below.

// Define the temp variables (we have to manually do it)
string temp_LastName, temp_FirstName;

// Retrieve the storage account from the connection string.
CloudStorageAccount storageAccount = CloudStorageAccount.Parse(
 CloudConfigurationManager.GetSetting("StorageConnectionString"));

// Create the table client.
CloudTableClient tableClient = storageAccount.CreateCloudTableClient();

// Create the CloudTable object that represents the "farmers" table.
CloudTable table = tableClient.GetTableReference("farmers");

// Create the batch operation.
TableBatchOperation batchOperation = new TableBatchOperation();

//--new entity
temp_LastName = "John";
temp_FirstName = "Doe";
FarmersEntity Farmers0 = new FarmersEntity(temp_LastName, temp_FirstName);
Farmers0.Email = "john@doe.lk";
Farmers0.PhoneNumber = "9957785";
batchOperation.Insert(Farmers0);
//--new entity
temp_LastName = "Anne";
temp_FirstName = "Doe";
FarmersEntity Farmers1 = new FarmersEntity(temp_LastName, temp_FirstName);
Farmers1.Email = "anne@doe.lk";
Farmers1.PhoneNumber = "9955875";
batchOperation.Insert(Farmers1);

// Execute the batch operation.
table.ExecuteBatch(batchOperation);

That's all. Hope you all enjoy this tool and we value your feedback.