[UPDATE 9/12/2012: The URL’s that point to the service does not seem to work. The service may be out of “lab phase” and hopefully we would see this integrated in Portal or as an Independent service.]

Microsoft recently announced a tool (which is in CTP) called “Data Transfer” on SQL Azure Labs. It let’s you transfer CSV and excel data to SQL Azure or Windows Azure blob. I think this service would make our task easier than before. Earlier, We had the option to use SQL server integration services (SSIS) or BCP to transfer excel files or CSV files to SQL Azure. It was straightforward too but what if we had the tool in which you just point it to your excel/csv file and it would do try to do the rest. wouldn’t that be easier? Yes, so Data Transfer aims to achieve exactly that. To test drive Data Transfer tool go to https://web.datatransfer.azure.com/

In this blog post, I will show you how to upload an excel file to SQL Azure via this tool:

1. After you have registered yourselves (via a Live ID) on https://web.datatransfer.azure.com/ – you will see

2. In my case, since I wish to transfer an excel file to SQL Azure, I am going to go with first option i.e. Microsoft SQL Azure

3. In the next step, provide the credentials of a SQL Azure DB (that exists already)

You have the option of saving a connection. And the drop down that you see in the above image will let you see all your saved connection.

4. In the next step, point to your file and select appropriate options via the Advanced options which has the File Encoding, Column Delimiter, Row delimiter and Text Qualifier options. So this are useful if you had a flat file with {tab} instead of {,} to separate column values. In my case, I am going to leave the advanced options to their default values. Point to the excel file and since my first row has column names – I am going to check the box that says “column names in the first data row” and then click on analyze.

5. if you had not checked the “Edit table defaults” box, then Done! you can view the status of the process from the My Data tab. It’s this easy.

If you checked the Edit table defaults, the next page would allow you to edit the data-type. The power of this tool resides in the fact that it guesses the data-type and assigns it to the data that we had specified. And if you want to over-ride the data-type chosen by the tool, you can do so here at this page:

BTW: I find this amazing!

6. Click on save and the data is ready to be uploaded. it’s that simple. And after the upload, you can view the details here on the “my data” page:

7. You can log into the SQL Azure manage portal and browse the tables. And after upload, the link to “manage” these tables will also be available in the “my data” page.

Conclusion:

As you imagine, the process is simple and “Data Transfer” tool makes it easier to upload CSV/Excel file to SQL Azure. And it also let’s you upload a file to Azure blob storage account. Give it a spin! URL:  https://web.datatransfer.azure.com/

And learn more about it here: http://www.microsoft.com/en-us/sqlazurelabs/labs/datatransfer.aspx