OakLeaf Systems: Test-Drive SQL Azure Labs’ New Codename “Data Transfer” Web UI for Copying *.csv Files to SQL Azure Tables or Azure Blobs

Wednesday, November 30, 2011

Test-Drive SQL Azure Labs’ New Codename “Data Transfer” Web UI for Copying *.csv Files to SQL Azure Tables or Azure Blobs

Updated 11/30/2011 3:00 PM PST: Added blob storage steps to the “Saving *.csv files to Windows Azure Blobs” section near the end of the post and a link to Shoshanna Budzianowski’s (@shoshe) official announcement: Announcing Microsoft Codename “Data Transfer” Lab of 11/30/2011.

Liam Cavanagh, the “father” of SQL Azure Data Sync, announced the availability of a Codename “Data Transfer” preview from SQL Azure Labs in his How to upload your Excel and CSV data to the Cloud blog post of 11/28/2011:

As a SQL Azure Program Manager at Microsoft, one of the most common questions we get from customers is how to get their data into the cloud. Just like SQL Server, SQL Azure has import tools like BCP that allow you to build scripts to load your data. I have never been completely happy with these solutions because for many people, they just want a quick and easy way to load their data and don’t want to mess around with complex command line tools or deal with firewall issues associated with connecting on-premises systems to the cloud. That was a primary reason why our group chose to create a SQL Azure Lab called: Microsoft Codename “Data Transfer”.

The purpose of this data transfer service is really simple. To give you an easy way to load your Excel or CSV (Comma Separated) files into SQL Azure. All you need to do is tell us where you want us to load your data to, point us at the file and we will do the rest.

Watch as WMV

A big piece of the work that we do for you is in what we call “Data Type Guessing”. This is where we look at your file and makes some guesses as to the best data-type to use when creating the SQL Azure table. Much of this is based on the years of experience we have gained from the SQL Server Integration Services (SSIS) technology. Although these guesses can be over-ridden by more advanced users, it is our belief that most people will just want to get it up there so that they can continue with their work.

The other capability Microsoft Codename “Data Transfer” provides is the ability to upload files to Windows Azure Blob store. Once again, this is pretty simple. You choose the Windows Azure blob storage account you want us to upload the file to, choose the file and you are done. From here, we upload the file and provide you with a private link that allows you to view, share or use that file.

I hope you will give this Data Transfer service a try and let me know what you think. Do you like the idea? Are there other sources, destinations or new features you would like us to add?

I can be contacted by email at: Liam <dot> Cavanagh AT Microsoft <dot> com.

Note: Microsoft Access has had since v1.0 a Wizard that analyzes and converts Excel and *.csv files to and from Jet or ACE tables.

To give Codename “Data Transfer” a test-drive, do the following:

1. Navigate to https://web.datatransfer.azure.com/ and register with your Windows Live ID to open the landing page:

Note: A What file designations are we missing? link isn’t visible in the preceding screen capture. Click the link to open a Tell Us Where Else You Would Like Us to Send Your Data text to add an additional Windows Azure data type. I requested Windows Azure Tables, which I understand is a feature currently under consideration.

Saving *.csv Files to SQL Azure Tables

2. Click the SQL Azure button to open a page to specify the target server and database, as well as your data access credentials:

Note: After you’ve added a target server/database and marked the Save Settings check box, you can fill out the form by selecting an item from the list above the Server text box.

3. Complete the form:

Note: Missing or invalid entries are emphasized in red when you click the Next button. Retyping the entry doesn’t remove the red tinge.

4. Click Next to open the Choose a File to Transfer page, click browse to open a Choose File to Upload dialog, navigate to and select a favorite *.csv file, and click Open to specify the source file. (My Microsoft Codename “Social Analytics” Windows Form Client creates ContentItems.csv files.) Click the Show Advance Options link to expand the list:

Note: The Edit Table Defaults check box is marked for demo purposes only (see screen capture below). ContentItems.csv files contain only date and numeric values, so don’t use a text qualifier.

5. Click Analyze to invoke “Data Type Guessing” and open the Update the Table Settings page:

Note: The process adds an autoincrementing integer (identity) primary key named Pid.

6. Click the Save button to start the upload process and open the My Data page, which includes previous transfers in its Datasets tab:

Caution: If you delete a table that you create with saved settings outside of the preceding page, such as with SQL Server Management Studio 2010+, you might encounter an error when attempting to create it. This might be a bug, so I’ve reported the problem.

7. Click Remove to drop unwanted tables or Manage to open the Database Manager’s login page. Type your credentials:

8. Click Log On to open the Database Manager’s main form. Click the Tables button in the Database Schema and Data pane:

9. Click the Data icon to open a grid to display the new ContentItems table’s rows:

Saving *.csv files to Windows Azure Blobs

2. Click the Windows Azure Blob button to open the Enter Your Windows Azure Blob Credentials Page. Complete the required entries and mark the Save Settings check box:

Caution: Be sure to type the Container name in lower case. If you include upper-case letters, you’re likely to encounter an HTTP 500 error when uploading the file. This issue is likely to be resolved shortly.

3. Click Next to open the Choose a File to Upload page and click Browse to open a Choose File to Upload dialog, which displays All Files (*.*) as the filespec. Choose any file (200 MB or smaller at present) to upload: