How to Connect to External Data in Excel?
In real world situations, you might have stored your students or employees database in external applications. If you wish to process the data, you might have to fetch it from Microsoft Access, SQL Server or even from plain .txt files. Excel offers different options to deal with your data.
Suppose, you have created a database in Access named Employees and it has a table Employees. Employees table has details including First Name, Last Name, Sex, Department and Salary as shown below.
You want all records from Employees database in your Excel spreadsheet for some processing. What will you do? Click on a cell on a worksheet where you want to get data from Access. Then, go to Data -> Get External Data -> From Access and you will get a window to browse your database. Select your database and click Open button as shown below.
Now, you will get a new window as shown below.
You can select how you want to view the data: as a Table, PivotTable report or PivotChart and PivotTable report. You can also select where you want to put the data. After choosing your options, click OK button. Now, you will get your excel worksheet as given below.
Similarly, you can fetch data from pure text files. Suppose, you have stored students details in .txt file named StudentsDB as given below.
If you look at this notepad file, you will see that different fields in every record are separated by tabs. Now, suppose you want to fetch this information from notepad to your Excel worksheet. Click on any cell where you want to put your data and then go to Data -> Get External Data -> From Text and you will get the window to browse your notepad file. Once you select the file and click Open, you will get a window as given below.
Click Next and you will get a screen as given below.
Here, you can choose the delimiter that separates your fields. You can also find the data preview down the window. After choosing the correct delimiter, click Next and you will be redirected to the final step of text import process. Click Finish and you will get your data on your worksheet as given below.
You can get data not only from Microsoft Access or Notepad, but even from other sources like SQL Server, Internet, XML, Oracle and so on. You can go to Data -> Get External Data -> From Other Sources and then choose the suitable option for you. You can get data from existing connections and even refresh the data as you need.
By connecting to external data from Excel, you don’t have to copy the data repeatedly even if you want to analyze the data periodically. After connecting, you just need to refresh your Excel worksheet from the actual data source when the actual data gets updated. Thus, an error-prone and time consuming process can be made simple and fast with using data connection facility of Excel.
Read More about Excel here