Azure Data Transfer: Tutorial

Microsoft Codename “Data Transfer” (https://web.datatransfer.azure.com) is a cloud service that lets you transfer data from your computer to a SQL Azure database or Windows Azure Blob storage account that you own. You can upload any data format to Windows Azure Blob storage, and data that is stored in comma-separated value (CSV) or Microsoft Excel format (.xlsx) to a SQL Azure database. When you upload data to a SQL Azure database, it is transformed into database tables.

Prerequisites

To use the Data Transfer service, you must have the following:

• A Windows Azure account. For more information, see Windows Azure Account.
• A SQL Azure database to upload data to SQL Azure. For more information, see How to Create a SQL Azure Database.
• A Windows Azure storage account to upload data to Windows Azure Blob storage. For more information, see How to Create a Storage Account for a Windows Azure Subscription.

Create a Data Transfer Account

Microsoft Codename “Data Transfer” is located at: https://web.datatransfer.azure.com/ The home page looks like this:

On the top of the home page, there are tabs designated for importing data, managing datasets and managing data stores.

To create an Azure account

1. Click either the Microsoft SQL Azure or Windows Azure Blob button.
2. If you are not already signed in to a Windows Live account, enter a valid Windows Live ID and password on the Windows Live site. This account is associated with your Data Transfer account.
3. Register a name and email account on the Registration Page to identify the owner of the account.
4. Read and agree to the Terms of Service, and then click Submit.

Import Your Data

On the Import tab, you have the options to import data into SQL Azure or Windows Azure Blob services. You can import comma-separated value (csv or txt) or Excel (.xlsx) data to a SQL Azure database. When you upload to a database, the Data Transfer service transforms your tabular data into database tables.

Import data to a SQL Azure database

The SQL Azure import process allows you to create or select a SQL Azure database connection.

To create a SQL Azure connection

1. Click the Import tab to navigate to the Data Transfer home page.
2. Click the Microsoft SQL Azure button.
3. If required, log into your Azure account using your Windows Live id.
4. On the Choose a SQL Azure database and table page, click the Add link.
5. Enter your Server, Database, User name and Password.
6. Click the Add Server button.

Note The User format is [username]@[server], where [server] is the name of the SQL Azure database server.

To select an existing SQL Azure connection

1. Click the Import tab on the Data Transfer service site.

2. On the Choose a SQL Azure database and table page, select an existing database connection from the list.

   

After you create or select a SQL Azure database connection, you start the file transfer process.

To import a tabular file into a new SQL Azure table

1. Select your SQL Azure connection. For more information, see Select an existing SQL Azure connection.

2. Select the New Table option, and then click Next
   **
   **

3. From the Choose a file to transfer page, enter the path to a file to upload or click Browse and select a .csv, .txt, or .xlsx file on your computer. You can specify whether the first data row contains the column names by selecting the Column names in the first data row checkbox. When this box is checked, the first data row of your .csv, .txt, or .xlsx file is used to create the column names of your database table. Click the Show Advanced Options link to specify more details about your .csv or.txt data.

   Note When you upload an .xlsx file, only the first sheet in your workbook is uploaded.

   

4. Click Analyze to transform your tabular data to database tables.

5. On the Update the table settings page, modify the table name or column data as necessary.

   

6. Click Save to upload your data to the SQL Azure database.

7. The My Data page is displayed with information about your upload. For more information, see Manage Your Datasets.

To import a tabular file into an existing SQL Azure table

When importing a tabular file into an existing SQL Azure table, the file transfer process performs an Update or Replace operation. Update is an upsert operation that updates an existing row if the primary key can be found; otherwise, the row is inserted. Replace is a truncate and insert operation.

1. Select your SQL Azure connection. For more information, see  Select an existing SQL Azure connection.

2. Select the Existing Table option and select a file from the list, and then click Next.   
        

3. From the Choose a file to transfer page, click Browse and select a .csv, .txt, or .xlsx file on your computer. You can specify whether the first data row contains the column names by selecting the Column names in the first data row checkbox. When this box is checked, the first data row of your .csv, .txt, or .xlsx file is used to create the column names of your database table. Click the Show Advanced Options link to specify more details about your .csv or.txt data.

   Note When you upload an .xlsx file, only the first sheet in your spreadsheet is uploaded.
        

4. From the Choose a file to transfer page, select the Update Table or Replace Table option.

5. Click Analyze to transform your data to database tables.

6. On the Drag source file columns to match target table page, create a Source file to Target table mapping. For more information, see Source file to Target table Mapping.

7. Click Save to upload your data to the SQL Azure database.

8. The My Data page is displayed with information about your upload. For more information, see Manage Your Datasets.

Source file to Target table Mapping

A source file is mapped into a target table using the Drag source file columns to match target table page. The page initially contains four columns: an Unmapped Source file column containing data column tiles, an empty Source file Mapped column, an empty Target table Mapped column, and an Unmapped Target table column containing data column tiles. Drag Unmapped data column tiles into Mapped columns to create a Source file to Target table Mapping.

Initial Source file to Target table mapping page

Mapped Source file to Target table mapping page

A Source file to Target table mapping can include a partial list of data column tiles as illustrated below.

Import data to Windows Azure Blob Storage

The Windows Azure Blob import process allows you to create or select a Windows Azure Blob connection.

To create a Windows Azure blob connection

1. Click the Import tab to navigate to the Data Transfer home page.
2. Click the Windows Azure Blob button.
3. If required, log into your Azure account using your Windows Live id.
4. On the Enter your Windows Azure Blob credentials page, enter your Account, Container and Key, and click Next.

Note The Container name must be 3 – 63 characters long containing only lowercase alphanumeric characters or dashes.

To select an existing Windows Azure blob connection

1. Click the Import tab to navigate to the Data Transfer home page.
2. Click the Windows Azure Blob button.
3. If required, log into your Azure account using your Windows Live id.
4. Select a Windows Azure Blob connection from the available connections list.
5. Click Next.

After you create or select a Windows Azure blob connection, you start the file transfer process. The file transfer process supports Numerics Binary Format which is used in Cloud Numerics. Cloud Numerics is a SQL Azure Lab that lets you model and analyze data at scale. When uploading a file to Windows Azure Blob storage for use in Cloud Numerics, you can choose to have the file converted to the Numerics Binary Format. This only applies to .csv and Excel files that contain numerical data ready for analysis with Cloud Numerics.

To import data into a Windows Azure blob

1. From the Choose a file to transfer page, enter the path to a file to upload or click Browse to find it on your computer.

2. For Cloud Numerics, select Numerics Binary Format from the Output Data Format dropdown list then select the additional options as appropriate for the import file.

3. Click Import to transfer your data without applying a tabular data transformation.

4. The My Data page is displayed with information about your upload. For more information, see Manage Your Datasets.

   

Manage Your Datasets

The My Data page contains tabs that list all datasets and blobs you have uploaded, along with the most recent job upload status.

To manage SQL Azure datasets

1. Click the My Data tab.
2. Click the datasets tab to view the list of datasets. The My Data tab for datasets looks like this:
        
3. Click Manage next to a dataset to open a new browser tab for the Management Portal for SQL Azure. This portal lets you view and change the data in your database. Click Remove to remove a dataset from the list. Removing a dataset only removes it from the Data Transfer service. It does not affect the data in your store.

Some errors that can occur during an upload to your database contain additional information. If an error of this type occurs, your dataset will show an Error link in the last job status column. Click the Error link for more information. An error log is also uploaded to the database as a table named ErrorLog_. The table contains a list of detailed error information.

To manage Windows Azure blob files

1. Click the My Data tab.
2. Click the blobs tab to view the list of blobs.
        
3. Click the name of the blob or the View link to view the contents of the blob in a new browser tab.
4. Click Rename to specify a new name for the blob. Click Remove to remove the blob from the storage account. Removing a blob permanently deletes the file from your Windows Azure Blob container.

Manage Your Data Stores

The My Stores page lists databases and storage accounts.

From this page, click Update to update the connection information for an account or Remove to remove it from the service. Removing a store from the service only removes it from the service and does not affect the store or the data it contains. Any datasets associated with the removed store are also removed from the service; however, they are not removed from your database.

Update and Remove perform the same actions for blob stores that they do for databases.

You can also click Rename on the blobs tab to specify a new name for a blob container

We Want Your Feedback

At the top of the Data Transfer Home page, you will see two additional buttons that let you give us feedback:

Click I Have an Idea to tell us about how we can improve this service.

Click I Found a Bug to send us email about what doesn’t work so we can fix it.

Conclusion

Microsoft Codename “Data Transfer” is a Windows Azure service that lets you easily upload data to a SQL Azure database or a Windows Azure Blob storage account. When you upload data to a SQL Azure database, your data is transformed into database tables. By uploading your data to Windows Azure, you can make it quickly and easily available to your users and applications around the world.