Exporting Azure Table Into Excel and SQL Server using Enzo Cloud Backup

Do you have data stored in Azure Tables but can’t find a way to easily export into Excel or even SQL Server for analysis? With Enzo Cloud Backup (a free tool by the way) you can easily do that. Although Enzo Cloud Backup is a backup tool first and foremost, you can also export Azure Table data. Let me show you how to do this. Backing up an Azure Table allows you to restore data at a later time, while exporting an Azure Table allows you to inspect the content of an Azure Table and run reports if desired.

Install and Configure Enzo Cloud Backup

First and foremost, you will need to download Enzo Cloud Backup. Then, when you start Enzo Cloud Backup, you will need to login by providing a Storage Account and a Key; this is the Storage Account that the tool needs to save its internal configuration settings; it is usually best to create a separate Storage Account for Enzo Cloud Backup to use. Once the information is provided, click on Connect. To learn more on how to create a Storage Account in Microsoft Azure, see this post.

image

Once logged in, let’s register a Storage Account that we want to explore, and in which the Azure Table table you want to export resides. From the menu, click Connection –> Data Store –> Register –> Azure Storage. This will bring up a window that allows you to register a Storage Account you want to backup or explore. In this example, I am registering a storage account called bsctest1.

image

Once registered, the account will show up on the left menu of the main screen, under Azure Storage. I am showing you the bsctest1 Storage Account below. As you can see, there are a few Azure Tables showing up, and a few buttons on top: Backup, Open in Excel and Explore.

image

Let’s also register a database server to export to, so that we can quickly select it later (note: this step is optional). To do this, go to Connection –> Data Store –> Register –> Database Server, and enter the name of the database server along with credentials. Click OK to save the database connection information.

image

Exploring Azure Table Data

Let’s first explore the content of the enzodebug Azure Table. Click on the enzodebug table then click on Explore. This will open up a browser showing up to 1,000 entities at a time. You can enter a list of column names separated by a column, and a filter for the data which will come up once you click on Refresh. Click on Next allows you to browse the next set of records. The browser allows you to quickly inspect your data, but you cannot export from here. To learn more about filters, visit this page on MSDN and search for the Filter Expressions section.

image

Exporting to Excel

Another feature available is to export Azure Table data in Excel directly from Enzo Cloud Backup. Back on the main screen, click on the Open in Excel button. This will open a screen giving you a few options: you can provide a list of properties to return (all by default) and a filter for the data (none by default). You can also choose a Table Hint, which provides much faster data upload times when the PartitionKey contains a GUID, or random number. Select Optimize Search for GUID Values when your PartitionKey has random values.

image

When you are ready, click on Export. A progress indicator will tell you how far you are in the load process in Excel.

image

Once the download of the data is complete and Excel has all the data, you will see your data in Excel as expected. Depending on how many records you are exporting, and the PartitionKey strategy selected, this operation may take some time.

image

Exporting to SQL Server or Azure SQL Database

You can also export this data to SQL Server, or Azure SQL Database just as easily. Because of data type constraints and other considerations, a few more options are available to you.

From the main window, still having the table selected, click on Backup. Note that unlike the two previous options, this approach allows you to export multiple tables at once. The Backup screen will show up as follows:

image

If you would like to use the GUID strategy as discussed previously, you can do so under the Default Strategy tab:

image

From the General tab, click on Add Backup Device. A panel will be added to the list of destinations. Choose SQL Server or SQL Database from the destination Dropdown list, and provide the connection credentials. In this example, I am also creating a new database (TestExport) with a default size of 1GB (this is important; if your data needs more than 1GB of space, you need to change the database size accordingly or the export will fail). [note: if you did not register a database server previously, you can type the server name and the user id/pwd fields by hand here).

image

In the Data Import Options you can change a few settings that dictate the behavior of the export process depending on unexpected data conditions. I chose the create missing tables, and to add Error Columns if one or more columns cannot be loaded in SQL Server (this will allow you to load the data even if some columns fail to load). 

image

After you click the Start button, you can see the progress of the export by looking at the Tasks.

image

Once completed, we can view our data. Using SQL Server Management Studio (SSMS), let’s connect to the database where the export has occurred. Make sure to pre-select the database name on the Connection Properties tab if you are connecting to an Azure SQL Database.

image

image

Once logged in, simply select from the table:

image

Note that three fields were added automatically in my export (these fields are only created if there are data errors during the export, and if you have selected the Add Error Columns option earlier): __ENZORAWXML__, __ENZOERRORID__, and __ENZOERRORDESC__.  The error is telling me that one of the columns could not be exported because of a name mismatch: the TimeStamp column (date/time) already exists. That’s because in XML (the underlying storage type of Azure Tables), property names are case sensitive: in my case, each entity has both a Timestamp and TimeStamp property (note the case difference). However by default SQL Server column names are not case sensitive, and as a result it is not possible to create both fields in a table in SQL Server. While the extra TimeStamp column was not created, the __ENZORAWXML__ field contains the actual value of the field, in XML, so you can still inspect it here.

image

Conclusion

As shown in this blog post, Enzo Cloud Backup is a tool that allows you to not only backup Azure Storage content, but also easily browse and export Azure Tables for further analysis in Excel and SQL Server / Azure SQL Database. 

About Herve Roggero

Herve Roggero, Microsoft Azure MVP, @hroggero, is the founder of Blue Syntax Consulting (http://www.bluesyntaxconsulting.com). Herve's experience includes software development, architecture, database administration and senior management with both global corporations and startup companies. Herve holds multiple certifications, including an MCDBA, MCSE, MCSD. He also holds a Master's degree in Business Administration from Indiana University. Herve is the co-author of "PRO SQL Azure" and “PRO SQL Server 2012 Practices” from Apress, a PluralSight author, and runs the Azure Florida Association.

Print | posted @ Tuesday, January 19, 2016 2:36 PM

Comments on this entry:

Comments are closed.

Comments have been closed on this topic.