How to import data into Azure Mobile Services

CAUTION: Back up your Azure Mobile Service tables and data before playing with this.

When using Azure Mobile Services as your cross platform back end there may be times where you need to import preexisting data. In my case, it was metadata for our weekly podcast. The data was comprised of episode titles, medial urls, episode descriptions, etc.

To gather this data, I converted our RSS feed to CSV and opened it up in Excel. From Excel I made some minor data transformations and performed some quick data cleanup. From there, I wanted to import the Excel data into the SQL tables being utilized by Azure Mobile Services. Sadly, I was unable to simply open SQL Management Studio, right click on the Azure Mobile Service database and click ‘Tasks >> Import Data.’ Here are the steps I used to import the data into Azure Mobile Services.

  • Create a local replica of the Azure Mobile Service table
  • Import Excel data into the local replica using ‘SQL Import and Export Wizard’
  • Export data from local replica table to export.dat using bcp
  • Import data from export.dat to Azure Mobile Service table

Create a local replica of the Azure Mobile Service table

Assuming you have already created your Azure Mobile Service, table and columns, log into your Azure SQL Database server via SQL Management Studio, right click on the table and click ”Script Table As >> CREATE To >> Clipboard”

Script to create local replica of the Azure Mobile Service Table

Script to create local replica of the Azure Mobile Service Table

 

On your locally installed instance of SQL Server create a database and open a new query. Paste the CREATE TABLE script and run it. This should result in a complete replica of your Azure Mobile Service table, locally.

CREATE Script to create local replica of Azure Mobile Services Table

CREATE Script to create local replica of Azure Mobile Services Table

Import Excel data into the local replica table using ‘SQL Import and Export Wizard’

From your local SQL instance, right click on the local database, where we created the new table, and click “Tasks >> Import Data.” This will open the ‘SQL Server Import and Export Wizard.’

Now, in my case, I’m using an Excel file as my data source. However, as you can see from the drop down, the ‘SQL Server Import and Export Wizard’ supports several Data Source types. Enjoy! PRO TIP: You might want to save your Excel file as a 97-2003 version.

For the ‘Source’ I choose my Excel file and for ‘Destination’ I choose ‘SQL Server Native Client 11.0′ and then chose my local SQL instance. Go ahead and choose  ‘Copy data from one or more tables or views’ and click ‘Next.’ From the ‘Source’ column check the box and in the ‘Destination’ column choose the replica table. Finally, click ‘Edit Mappings’ to map the source to the destination columns. Click ‘Finish’,’Finish’,’Finish’ to start the import.

Note: You may need to transform and cleanup the data in your source file. This is not uncommon.

Map your SOURCE to the DESTINATION columns of the SQL local replica of the Azure Mobile Services

Map your SOURCE to the DESTINATION columns of the SQL local replica of the Azure Mobile Services

Export data from local replica table to export.dat using bcp

Once the data has been imported into the Azure Mobile Services replica table we can export it using bcp. Open a command prompt and run the following command. This will export the data from the local replica table into a single file, export.dat.

bcp windevshow_import.dbo.Episodes out c:\export.dat -S minint-a1skhu0\sqlexpress -T -n -q

Import data from export.dat to Azure Mobile Service table

Finally, we can import the data to our Azure Mobile Service table using bcp. Open a command prompt and run the following command.

bcp windevshow_js.Episodes in c:\export.dat -n -d windevshow_db -U windevshow -P <your password here> -S tcp:e9t5n75o08.database.windows.net

Use Windows 10 Virtual Desktops for Presentations and Coding Demos

Windows 10 Virtual Desktops for Presentations and Coding Demos

Windows 10 offers a slew of new features, one of which is Virtual Desktops. To get started, try the following:WinKey + CTRL + D - Creates a new Virtual Desktop WinKey + CTRL + Left or Right arrows - Moves between Virtual DesktopsMany of us … [Continue reading]

A simple, clean, minimalist watchface for Pebble

A simple, clean, minimalistic watchface for Pebble

I spent some designing and coding my own watchface for my new Pebble watch. The SDK and tooling is surprisingly intuitive and fun. The SDK supports C and JS. Both languages are utilized in this watchface, which is available on GitHub. … [Continue reading]