Namespaces

Variants
Actions

Please note that as of October 24, 2014, the Nokia Developer Wiki will no longer be accepting user contributions, including new entries, edits and comments, as we begin transitioning to our new home, in the Windows Phone Development Wiki. We plan to move over the majority of the existing entries over the next few weeks. Thanks for all your past and future contributions.

(Difference between revisions)

Azure Mobile Services - Importing data from Excel

From Wiki
Jump to: navigation, search
galazzo (Talk | contribs)
(Galazzo -)
galazzo (Talk | contribs)
m (Galazzo -)
 
(10 intermediate revisions by 2 users not shown)
Line 1: Line 1:
[[Category:Draft]][[Category:Windows Phone 8]][[Category:Windows Phone 7.5]][[Category:Web Services]]
+
[[Category:Web Services on Windows Phone]][[Category:Files/Data on Windows Phone]][[Category:Windows Phone 8]][[Category:Windows Phone 7.5]]
 +
== Introduction ==
 +
{{Abstract|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]].
 +
{{SeeAlso|
 +
* [[Azure Mobile Services on Windows Phone]]
 +
* [[Azure Mobile Services - Importing data from Excel]]
 +
* [[Azure Mobile Services - Send an email with SendGrid]]
 +
* [[Azure Blob Storage - Uploading images using REST API]]
 +
* [[Azure Mobile Services - Implementing Push Notifications]]
 +
}}
 
{{ArticleMetaData <!-- v1.3 -->
 
{{ArticleMetaData <!-- v1.3 -->
 
|sourcecode= <!-- Link to example source code e.g. [[Media:The Code Example ZIP.zip]] -->
 
|sourcecode= <!-- Link to example source code e.g. [[Media:The Code Example ZIP.zip]] -->
Line 6: Line 15:
 
|sdk= <!-- SDK(s) built and tested against (e.g. Windows Phone 8.0 SDK) -->
 
|sdk= <!-- SDK(s) built and tested against (e.g. Windows Phone 8.0 SDK) -->
 
|platform= Azure
 
|platform= Azure
|dependencies= <!-- Any other/external dependencies e.g.: Google Maps Api v1.0 -->  
+
|dependencies= <!-- Any other/external dependencies e.g.: Google Maps Api v1.0 -->
|signing=<!-- Special Signing requirements -->
+
|signing= <!-- Special Signing requirements -->
 
|capabilities= <!-- Required capabilities for code (e.g. ID_CAP_LOCATION, ID_CAP_NETWORKING) -->
 
|capabilities= <!-- Required capabilities for code (e.g. ID_CAP_LOCATION, ID_CAP_NETWORKING) -->
 
|language= <!-- Language category code for non-English topics - e.g. Lang-Chinese -->
 
|language= <!-- Language category code for non-English topics - e.g. Lang-Chinese -->
 
|translated-by= <!-- [[User:XXXX]] -->
 
|translated-by= <!-- [[User:XXXX]] -->
|translated-from-title= <!-- Title only - not link -->  
+
|translated-from-title= <!-- Title only - not link -->
 
|translated-from-id= <!-- Id of translated revision -->
 
|translated-from-id= <!-- Id of translated revision -->
|review-by=<!-- After re-review: [[User:username]] -->
+
|review-by= <!-- After re-review: [[User:username]] -->
 
|review-timestamp= <!-- After re-review: YYYYMMDD -->
 
|review-timestamp= <!-- After re-review: YYYYMMDD -->
 
|update-by= <!-- After significant update: [[User:username]]-->
 
|update-by= <!-- After significant update: [[User:username]]-->
 
|update-timestamp= <!-- After significant update: YYYYMMDD -->
 
|update-timestamp= <!-- After significant update: YYYYMMDD -->
 
|creationdate= 20130618
 
|creationdate= 20130618
|author=[[User:galazzo]]
+
|author= [[User:galazzo]]
 
}}
 
}}
 
== Introduction ==
 
In this article will be shown how to import data from an Excel file into Azure Mobile Services in order to be used by your App. It's assumed you have created your own service else follow this guide on [[Azure_-_Mobile_Services_on_Windows_Phone|how to manage Azure Mobile Services]].
 
  
 
== Tools ==
 
== Tools ==
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.<br />
+
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.
  
[[File:Sql-server-management-studio.jpg]]
+
[[File:Sql-server-management-studio.jpg|none]]
  
 
== Importing Data ==
 
== Importing Data ==
First step, in order to avoid connection issues is to set Firewall Rules to allow your IP address to connect to DB.<br />
+
First step, in order to avoid connection issues is to set firewall rules to allow your IP address to connect to the database.
 +
 
 +
[[File:Azure-set-firewall.jpg|thumb|none|700px]]
 +
 
 +
[[File:Azure-set-firewall-2.jpg|thumb|none|700px]]
  
[[File:Azure-set-firewall.jpg]]<br />
 
[[File:Azure-set-firewall-2.jpg]]<br />
 
  
 
=== Microsoft SQL Server Management Studio ===
 
=== Microsoft SQL Server Management Studio ===
  
Opening Microsoft SQL Server Management Studio will be asked to connect to a server.
+
Open the ''Microsoft SQL Server Management Studio''. You will be asked to connect to a server.
  
 
[[File:Sql-server-management-studio-login.jpg]]
 
[[File:Sql-server-management-studio-login.jpg]]
  
Select the server name you choosed during creation of your Mobile Service and select SQL Server Authentication option.<br />
+
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.<br />
+
  
[[File:Sql-server-management-remote-db access.jpg]]<br />
+
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''. Will be shown the same window at startup and select your local address ( tipically '''localhost''' or network path )  and select  ''Windows authentication''.<br />
+
[[File:Sql-server-management-remote-db access.jpg|none]]
  
[[File:Sql-server-management-local-db access-2.jpg]]<br />
+
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''.
  
if don't have one create a new local database<br />
+
[[File:Sql-server-management-local-db access-2.jpg|none]]
  
[[File:Sql-server-management-studio-create-local-database.jpg]][[File:Sql-server-management-studio-create-local-database-2.jpg]]<br />
+
Create a new local database if you don't have one.
  
Right click on your local database -> task -> import data<br />
+
[[File:Sql-server-management-studio-create-local-database.jpg|none|frame|Right click to create new database]]
 +
[[File:Sql-server-management-studio-create-local-database-2.jpg|none|frame|Fill in details for new local database]]
  
[[File:Sql-server-management-studio-import-data-into-azure.jpg]]<br />
+
Right click on your '''local database -> task -> import data'''
  
Opened the import / export wizard, select Microsoft Excel as data origin and the right version of your Excel file<br />
+
[[File:Sql-server-management-studio-import-data-into-azure.jpg|none]]
  
[[File:Sql-server-management-studio-import-data-into-azure-2.jpg]]
+
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 and of course will be our remote Azure Db<br />
+
[[File:Sql-server-management-studio-import-data-into-azure-2.jpg|none]]
  
[[File:Sql-server-management-studio-import-data-into-azure-3.jpg]]<br />
+
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<br />
+
[[File:Sql-server-management-studio-import-data-into-azure-3.jpg|none]]
[[File:Sql-server-management-studio-import-data-into-azure-4.jpg]]<br />
+
  
Now you will be able to see the content of your Excel file. Note that each sheet will be seen as a table<br />
+
Select the first option: Copy data from one or more tables or views
 +
[[File:Sql-server-management-studio-import-data-into-azure-4.jpg|none]]
  
[[File:Sql-server-management-studio-import-data-into-azure-5.jpg]]<br />
+
Now you will be able to see the content of your Excel file. Note that each sheet will be seen as a table
  
Selected the sheet you want to import let's click on ''Modify Mapping'' and then on Modify ''SQL Code''<br />
+
[[File:Sql-server-management-studio-import-data-into-azure-5.jpg|none]]
  
[[File:Sql-server-management-studio-import-data-into-azure-6.jpg]][[File:Sql-server-management-studio-import-data-into-azure-7.jpg]]<br />
+
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. Remeber that each table must have a primary key named '''Id'''. The code should look like that:
+
[[File:Sql-server-management-studio-import-data-into-azure-6.jpg]][[File:Sql-server-management-studio-import-data-into-azure-7.jpg]]
 +
 
 +
Add the following line {{Icode|[id] int identity primary key,}} into the script. This will be our primary key. Remember that each table must have a primary key named {{Icode|Id}}. The code should look like the snippet below:
 
<code>
 
<code>
 
CREATE TABLE [dbo].['TABLE - NAME$'] (
 
CREATE TABLE [dbo].['TABLE - NAME$'] (
Line 86: Line 96:
 
)
 
)
 
</code>
 
</code>
<br />
 
Go forward <br />
 
[[File:Sql-server-management-studio-import-data-into-azure-8.jpg]][[File:Sql-server-management-studio-import-data-into-azure-9.jpg]]<br />
 
  
If no errors occurs you should see a result like that<br />
+
Go forward
[[File:Sql-server-management-studio-import-data-into-azure-10.jpg]]<br />
+
[[File:Sql-server-management-studio-import-data-into-azure-8.jpg|none]]
 +
[[File:Sql-server-management-studio-import-data-into-azure-9.jpg|none]]
  
 +
If no errors occurs you should see a result as in the image below:
 +
[[File:Sql-server-management-studio-import-data-into-azure-10.jpg|none|frame|Data imported into database]]
  
Now data are imported into Db and your expectation is to find tables also into your Mobile Service, but checking you still will not find them, You need to perform other 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'' <br />
 
  
[[File:Azure-manage-database.jpg]]<br />
+
Now the data has been imported into the database.  
  
The Database Management Portal will be opened<br />
+
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.
  
[[File:Azure-manage-database-2.jpg]]<br />
+
To do that, go into SQL Database section, select your database ( '''nokiadeveloepr_db''' for our tutorial ), open the ''dashboard'' section and click on '''Manage'''
  
Select a ''New Query''<br />
+
[[File:Azure-manage-database.jpg|none]]
  
[[File:Azure-manage-database-3.jpg]]<br />
+
The ''Database Management Portal'' will be opened
  
execute the following queries:<br />
+
[[File:Azure-manage-database-2.jpg]]
<code>
+
 
 +
Select a '''New Query'''
 +
 
 +
[[File:Azure-manage-database-3.jpg]]
 +
 
 +
Execute the following queries:
 +
<code text>
 
CREATE SCHEMA nokiadeveloper;
 
CREATE SCHEMA nokiadeveloper;
 
ALTER SCHEMA nokiadeveloper TRANSFER dbo.yourtablename;
 
ALTER SCHEMA nokiadeveloper TRANSFER dbo.yourtablename;
Line 115: Line 129:
 
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.
 
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.
  
== Summary ==
+
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.

Latest revision as of 19:49, 12 August 2013

Contents

[edit] Introduction

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.

WP Metro Icon File.png
WP Metro Icon Web.png
WP Metro Icon WP8.png
SignpostIcon WP7 70px.png
Article Metadata
CompatibilityArticle
Created: galazzo (18 Jun 2013)
Last edited: galazzo (12 Aug 2013)

[edit] Tools

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.

Sql-server-management-studio.jpg

[edit] Importing Data

First step, in order to avoid connection issues is to set firewall rules to allow your IP address to connect to the database.

Azure-set-firewall.jpg
Azure-set-firewall-2.jpg


[edit] Microsoft SQL Server Management Studio

Open the Microsoft SQL Server Management Studio. You will be asked to connect to a server.

Sql-server-management-studio-login.jpg

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.

Sql-server-management-remote-db access.jpg

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.

Sql-server-management-local-db access-2.jpg

Create a new local database if you don't have one.

Right click to create new database
Fill in details for new local database

Right click on your local database -> task -> import data

Sql-server-management-studio-import-data-into-azure.jpg

Open the import / export wizard, select Microsoft Excel as data origin and the right version of your Excel file

Sql-server-management-studio-import-data-into-azure-2.jpg

The next step is to select the destination server, in this case our remote Azure Db

Sql-server-management-studio-import-data-into-azure-3.jpg

Select the first option: Copy data from one or more tables or views

Sql-server-management-studio-import-data-into-azure-4.jpg

Now you will be able to see the content of your Excel file. Note that each sheet will be seen as a table

Sql-server-management-studio-import-data-into-azure-5.jpg

Select the sheet you want to import. Let's click on Modify Mapping and then on Modify SQL Code

Sql-server-management-studio-import-data-into-azure-6.jpgSql-server-management-studio-import-data-into-azure-7.jpg

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,
[Tipo] nvarchar(255),
[Descrizione] nvarchar(255),
[Immagine] nvarchar(255)
)

Go forward

Sql-server-management-studio-import-data-into-azure-8.jpg
Sql-server-management-studio-import-data-into-azure-9.jpg

If no errors occurs you should see a result as in the image below:

Data imported into database


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

Azure-manage-database.jpg

The Database Management Portal will be opened

Azure-manage-database-2.jpg

Select a New Query

Azure-manage-database-3.jpg

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.

This page was last modified on 12 August 2013, at 19:49.
320 page views in the last 30 days.

Was this page helpful?

Your feedback about this content is important. Let us know what you think.

 

Thank you!

We appreciate your feedback.

×