Importing external data
Often, we use Excel to summarize detail that comes from some other program or accounting system. There are essentially two ways that I am aware of to get data from another program into a destination Excel worksheet, I’ll nickname them:
- Copy/Paste method
- Importing external data method
The Copy/Paste method is probably the most common. It is when you basically view or export the data to a new Excel, csv or text file, and then copy and paste the data into the destination Excel worksheet.. It is easy to use and works well. However, other methods may be more efficient for recurring use workbooks.
You can pull the data directly from the external data source using the External Data feature of Excel. This post focuses on this feature, and the details are found below.
External Data Overview
First, let’s start with what the feature does, and then we’ll move on to how to use it. External data is defined as data that exists outside of the Excel workbook, in some other place. That other place could be almost anywhere, and Excel supports pulling external data from a wide variety of sources. Examples include data stored on web pages, in text files, or in other programs.
Programs that store large amounts of data are often built upon a database engine or platform. You, as the user, see the program’s user interface which is made up of menus, icons, dialog boxes, and forms. Behind the scenes however, applications often store their data in a database. It is this underlying database that Excel can often tap into with the External Data feature.
This feature doesn’t pull a single value into a single cell. It retrieves a block of data, or a table, that will ultimately occupy a range of cells.
The External Data feature asks you to identify the location of the data you want. Once you do, Excel retrieves the data and places it into the selected worksheet. The best part of this feature lies in the fact that the External Data range is not static….it can be refreshed. When you click the refresh button, Excel heads out to the data source and pulls back the updated data. This improves the efficiency of recurring use workbooks because you don’t need to do the whole export, import, reformat routine that is needed with the standard copy/paste method. You simply click the refresh button, and bam, the updated data just appears in your worksheet.
How to Importing External Data
Excel supports pulling data from a wide variety of data sources. Examples include web pages, text files such as csv files, SQL, Access, ODBC compliant sources, and more. The basic process is that you first identify the type of data source and then the location. Depending on the type of source, you’ll have appropriate options. Next articles teaches you how to do this.