Excel Help: Extract live data from system in Excel by connecting your Excel file to the System database

Suppose your organization records all the transactions in an SQL server database (or any other database) using an ERP system and you need data from this system on a regular basis for your routing activities.

For e.g. you work in HR department needing information about employees like current status, department, Designation, date of joining etc very frequently and this data is stored in SQL database.

You can use Import External data feature of Excel to connect your Excel file to this SQL server database and get updated data whenever you need it just by refreshing this data with few clicks.

Followings are the steps to import data from SQL server to Excel file.

Note: Before you start, please ensure that you have read access to this database and you have Server Name, Login name & Password

There are broadly two steps involved to achieve this…

1. Create Data Source Name (DSN)

2. Importing data into Excel File using this DSN

1. Creating Data Source Name (DSN)

Go to Control Panel --> Administrative Tools --> Data Sources (ODBC)
Click Add, Select SQL Server & Click Finish
Enter any Name for DSN and select server name and click next
Select the authentication mode and click next (check with the database administration team for the type of access provided to you)

Select “With Windows NT authentication using the Network login ID” if your windows login name has been granted the access
Select “With SQL Server Authentication using a login ID and password entered by the user” if you have the login name and password.

Select default database and click next
Click Finish

2. Importing data into Excel file using the DSN which we have just created

Open new Excel file and select Data --> From Other Sources (under “Get External Data”) --> From Microsoft Query

Select the DSN which we have just created (Note: You can use this same DSN for making n number of files)

Enter Password if it asks for and click OK

In next screen, Expand the Table name from which the data is required. Select the Columns which are required. Click Next.

If you want to filter any data then apply filters on this screen. Select the field on which filter need to be applies and select the filter type. Like Date of Joining after 1-1-2011 or Designation equal to “Grade 1” etc.

Select the sorting order if required. You can specify 3 sorting fields and select the type of sorting i.e. Ascending or Descending, click Next

Select “Return data to Excel” to finish the data. Use edit “Edit data using Microsoft Query” option for advanced query editing. (Basic MS SQL knowledge is required for advanced Query editing)
Click Finish