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”
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.
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.
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