Q. How to import data from SQL to Excel?
A. By using Excel Data Connection feature we can import data from SQL server easily and efficiently.
This step-by-step guide describes how to import data from a database that is included with Microsoft SQL Server into Microsoft Excel in the form of table or pivot table. We can import whole data or filtered data as required from single table, multiple table of SQL server to Microsoft Excel spreadsheet and use it further processing.
Why to import data to excel from SQL?
As we know excel is familiar, user friendly and most used data analytical tool so it would be easy for further data analysis using excel.
Can worked with data as required by the user and easily can convert into graphs and diagrams as required.
Following is the procedure to extract data into excel sheet form SQL table (how to import data form SQL to Excel – step by step tutorial)
STEP 1.
Go to Data tab then select From SQL Server by selecting the From Other Source button.
Data>From Other Source>From SQL Server
STEP 2.
Connect to the SQL Server
Enter the information required to connect the database server.
Initially input the server name in first box. (Make sure that the “\” you typed is correct and also SQL services of related server is started).
Then either use window authentication or SQL authentication method for login credential.
To login as windows user select first radio button.
To login as Database user select second radio button and then input user name and password of database user.
STEP 3.
Select the Database or Table
Select the database or table/cue that contains the data that you want by using the drop-down box then click next.
STEP 4.
You can select to connect with specific table or enable multiple table selection from the check box below. Then roll down and make sure you are selecting form type TABLE then select the required table which has similar name as in SQL database. Then select Next.
STEP 5.
Save Data Connection file and finish
Enter a name and description for your new data connection file. The wizard automatically create file name, generate friendly name, and select preferred location and also highlight where we need to write description. You can leave as it is and press finish to save the file.
STEP 6.
How do you want to import data
Select how you want to view data in your worksheet there four option available
i. Table
ii. PivotTable Report
iii. Pivot Chart
iv. Only Create connection
I preferred to select table because you can use same data many times in different ways.
IF YOU NEED TO SELECT SPECIFIC DATA FROM TABLE THEN USE FILTER by pressing on Properties button.
Then connection properties window open on the definition tab select command type as SQL and write SQL query as you required.
In this tutorial suppose I want to select data from table G_L Entry of company CRONOUS International Limited of Demo Database NAV(8-0) where posting date of data is between January 1st 2016 to December 31st 2016 then I should have to write query like following:
QUERY FOR FILTER IN POSTING DATE BETWEEN SPECIFIC DATE
select * from "Demo Database NAV (8-0)"."dbo"."CRONUS International Ltd_$G_L Entry"
WHERE "Posting Date" between '01/01/2016' and '12/31/2016'
Leave other part as it is and then click ok then on other window press yes. Then in import Data window press ok.
Depending on volume of data time required varies wait for some time the data will be imported in excel in the form of excel. If there is huge amount of data like more than 1,000,000 rows then excel could not handle the data.
Final Output
How to cancel?
During importing of data processing if you want to cancel the process then either press ESC button or click near the sheet name.
How to extract data again from same table?
Either starts form the beginning of select Existing Connection which is in Data tab left to From Other source.
How to import data from SQL into excel using existing connection?
Press the Existing Connection near From Other Source button in Data tab then select the file which we saved previously the Import data window appears. After that select the best way to view data and click ok the data will be imported.
Reference:
https://support.microsoft.com How to import data from SQL to MS Excel
SQL server export to excel
Some other method using SQL
As discussed in Stack overflow
