How do you import data from SQL Server 2012 to MS Excel?

Why do we need to import data into MS Excel? If we need to use the number crunching power of
Excel then we need to get the data from the SQL Server to MS Excel.

The following steps show you how to import.

Microsoft Excel [herein using Microsoft Office 2010 Professional Plus (x32bit) on a x64bit
Windows 7 Ultimate] has a Data tab in its ribbon as shown.

There are different ways you can get the data from SQL Server.

In this post importing data from
SQL Server 2012 using MS Query will be described.

Click on From Other Sources and in the drop-down click on From Microsoft Query.
This will require an ODBC Connection information.

How do you create an ODBC connection to SQL Server 2012?
Follow this link for a step-by-step procedure:
http://hodentekmsss.blogspot.com/2013/08/how-do-you-create-odbc-dsn-to-sql.html

When you click on From Microsoft Query the following window, Choose Data Sources window is displayed:

I have created several ODBC datasources over a period of time and the most recent one is to SQL Server 2012 with the name, sqlSrvr2012* at the very bottom.

Click on sqlSrvr2012 and click OK.

The Choose Columns of the Query Wizard will be displayed. You can see many columns even those including from System. Scroll down to the one you want to import (herein, Product Sales for 1997).

Click on that column and click on the symbol > between the two panels to bring over the columns to the right panel as shown.

Click Next to reveal Filter Data of the Wizard as shown:

Accept the default (no filtering) and click Next.
The wizard's Sort Order window is displayed as shown:

Accept the default (no sorting) and click Next.

Query Wizard's Finish screen is displayed as shown. Here you can get the data into MS Query window( open all the time we were working with the Wizard) or get it into Excel as shown.

Accept the first option and click Finish. You may also save the query if you like.
The program takes you to the Excel's sheet with the Import Data screen asking you how and where to display the imported data. The options are Table (into a table structure) beginning in the column $A$1. Both of them can be changed.

Click OK and after a brief processing the imported data will appear as shown:

Enjoy!

Mahalo

 

Report Builder Report using data from SQL Anywhere 16

Report Builder 3.0 is the present version shipped at the same time as SQL Server 2012. It is a one-stop report authoring tool which can even be launched from Report Manager or from a Reporting services Integrated SharePoint Site. Of course you can download and launch it after installing on your desktop.

The most viewed Report Builder tutorials are here:
Report Builder is described in great detail here:

http://jayaramkrishnaswamy.sys-con.com/node/982742
Creating reports using Report Builder 2.0 is described here

http://jayaramkrishnaswamy.sys-con.com/node/1227111

 

You can master Report Builder 3.0, just follow the step-by-step instructions

Report Builder 3.0 is described exhaustively in my latest book

Out of the box it can connect to a variety of vendor products and the inclusion of ODBC and OLE DB makes it extremely convenient to connect to many other (not out of the box supported) products

Case in point is SQL Anywhere Server 16 for which you can set up an ODBC connection

This post shows you some of the steps that you can follow to turn out a report from SQL Anywhere 16. The following assumes that you have already started the SQL Anywhere Personal server. The procedure has still some unanswered questions, please read the last section

The following screen shows how you connect to an ODBC source for a connection embedded with the report

The next slide shows an ODBC System DSN 'demo' created using the SQL Anywhere 16. In reality this ODBC DSN gets registered when you install SQL Anywhere 16

 The next slide shows that connectivity is OK with 'dba' as username and 'sql' as password

The final connectivity screen after testing the authentication is shown in the next image

 The Credentials for this connection are shown in the next image

In order to create a dataset for your report use of Query Designer is not possible as it is not supported. You need to have this information on your hand to insert into the Report Builder's data set page.

The next image shows the InteractiveSQL tool in which a SQL Select statement is used to choose a all the fields from the Contacts table.

The dataset will be created using this embedded connection as shown.

For this, the query contacts.sql created in Sybase's InteractiveSQL is used and persisted to the desktop. This is imported into Report Builder's data set interface as shown using the import button

.

The next image shows the report designer interface taking in a few columns from the data set shown on the left.

This last image shows the report being displayed in Report Builder 3.0

Caution

While the above procedure is correct you may find problems while repeating this procedure and this will be mainly due to the odbc32 and odbc64 problems as I understand it

However, take a look at my bug report to Microsoft connect here

https://connect.microsoft.com/SQLServer/feedback/details/799460/report-displays-correctly-in-ssdt-and-deploys-without-errors-the-same-report-is-not-displayed-in-report-manager-or-report-server