Azure Mobile Services - Importing data from Excel
This article demonstrates how to import data from an Excel file into Azure Mobile Services so that it can be used by your app. We assume you have an existing service, perhaps created by following this guide: Azure Mobile Services on Windows Phone.
In order to import data from your local database or Excel file you need to install last version of Microsoft SQL Server Management Studio Express (SSMSE), a free and easy graphical tool to use for managing SQL Server database with advanced services.
First step, in order to avoid connection issues is to set firewall rules to allow your IP address to connect to the database.
Microsoft SQL Server Management Studio
Open the Microsoft SQL Server Management Studio. You will be asked to connect to a server.
Select the server name you chose during creation of your Mobile Service and select SQL Server Authentication option.
Once logged you can browse your remote databases, tables and perform queries.
Now we need to access to a local resourse in order to upload data from local to Cloud. To do that click on connect button on top left of the explore object column and select database engine. You will be shown the same window at startup and select your local address (typically localhost or network path) and select Windows authentication.
Create a new local database if you don't have one.
Right click on your local database -> task -> import data
Open the import / export wizard, select Microsoft Excel as data origin and the right version of your Excel file
The next step is to select the destination server, in this case our remote Azure Db
Select the first option: Copy data from one or more tables or views
Now you will be able to see the content of your Excel file. Note that each sheet will be seen as a table
Select the sheet you want to import. Let's click on Modify Mapping and then on Modify SQL Code
Add the following line [id] int identity primary key, into the script. This will be our primary key. Remember that each table must have a primary key named Id. The code should look like the snippet below:
CREATE TABLE [dbo].['TABLE - NAME$'] (
[id] int identity primary key,
If no errors occurs you should see a result as in the image below:
Now the data has been imported into the database.
Your expectation is probably that at this point the tables will also be in your Mobile Service. However this is not the case - you need to perform another operation to link your Mobile Service with your database.
To do that, go into SQL Database section, select your database ( nokiadeveloepr_db for our tutorial ), open the dashboard section and click on Manage
The Database Management Portal will be opened
Select a New Query
Execute the following queries:
CREATE SCHEMA nokiadeveloper;
ALTER SCHEMA nokiadeveloper TRANSFER dbo.yourtablename;
We created a new schema with the same name of our Mobile Service and linked our table to it in order to access to this data from Mobile Service.
Go on your Mobile Service section and create a new table with the same name of the table created into database. At time of creation you will see just 1 record, but don't worry, click on it and all records will appear magically.