Upload CSV and excel files to SQL Azure via “Data Transfer” tool

[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

image

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)

image 2

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.

image

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!

Edit table defaults

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:

image

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.

image

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

6 thoughts on “Upload CSV and excel files to SQL Azure via “Data Transfer” tool

  1. Pingback: Upload file to windows azure blob via “data transfer” tool « Paras Doshi's Blog

  2. Pingback: Upload di CSV e excel su SQL Azure via “Data Transfer” « SQLServerITA

Thank this author by sharing the article on social media. If you have any questions or comments, please leave a reply below:

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s