Skip Navigation LinksHome > View Post

Using excel to access your Mobile Services data

If you have Microsoft Excel it’s easy to connect it to your Mobile Service SQL database to take a look at your data. This is great if you use Mobile Services for error reporting etc and want to perform some analysis on those numbers.

First, you need to get the server name for your Mobile Service. Login to the Windows Azure portal and locate your Mobile Service. Go the CONFIGURE tab and you’ll see the name of your SQL Database and it’s Server.

image

Server names are autogenerated random strings so you may want to make a note.

Now that you have the name of your server, we need to open up the server to accept connections from your machine. Navigate to the SQL Databases tab in the portal:

image

Once in the SQL Databases part of the portal, click on the SERVERS tab to list all your servers. Now locate the name of your server from earlier, and click on the first column (it’s name) to select the item.

image

Next, click on the CONFIGURE tab to configure your server. This will show you a list of allowed IP addresses. The SQL guys have made it easy for you, and provided a quick option to add your own IP to the list.

You should see your current IP (CURRENT CLIENT IP ADDRESS) and they have even provided a button that you can click to enable this IP.

image

Click the ADD TO ALLOWED IP ADDRESSES button and then press SAVE at the bottom of the screen.

image

We’re almost done with the portal. The last thing we need to do is get the full server name. Click the DASHBOARD tab

image

On the right hand side of the DASHBOARD you should see your administrator login name, the option to reset your password (in case you can’t remember it) and the MANAGE URL. This

image

If you already know you admin login and password (you do, right?) then all you need from here is the manage URL. Note that we don’t want the ‘https://’ scheme at the start of the name. Now, in excel open a new workbook. Click the Data tab and the From Other Sources dropdown and choose From SQL Server.

image

You’ll now be prompted for the server name.  Enter the name from the MANAGE URL field above but without the ‘https://’ scheme at the start.

image

Click Next; this will connect to your SQL database server. Now you need to choose the database (this is the first part from the SQL Databse field in the first screen shot above)

SNAGHTML2ffad4e

And now you can choose which tables you want to view.

SNAGHTML2ff21e1

When you’re happy with your choice click Finish (or you can click Next to configure this connection if you’d like to reuse it again in the future, I’ll save that as an exercise for the reader).

Finally, decide how you’d like the data to be imported, I’m going to go for a good old fashioned Table:

SNAGHTML3019af6

And finally you’ll be prompted for those credentials once more…

SNAGHTML302e5db

et Voila!

SNAGHTML303f584

And don’t worry, any changes you make aren’t synced back to the server so you won’t break your live service.

Tags:

 
Josh Post By Josh Twist
11:53 PM
28 Nov 2012

» Next Post: The twelve days of ZUMO
« Previous Post: Got kids? Have an iPhone or iPad? Read this.

Comments are closed for this post.

© 2005 - 2014 Josh Twist - All Rights Reserved.