Import financial data into Excel from various sources

It’s the end of the financial period, and once again, you need data from other departments in your company — such as Sales, Operations, Human Resources, and Billing. But often, data from other departments is kept in separate programs or systems. This makes financial reporting a challenge, because before the reporting process can begin, all of the data must be in one place.

Do it the easy way

You can compile your company’s data into Microsoft Office Excel 2003 by retyping or by copying and pasting. But importing is most effective when you're working with large amounts of data that would be time-consuming to enter manually or too large to copy and paste.

In addition, if you import your data, Excel can automatically update your reports and summaries whenever the original source database is updated. Your data is always current.

Import or connect to data

ShowHow to

You can use these procedures to import data from most data sources, including OLAP (OLAP: A database technology that has been optimized for querying and reporting, instead of processing transactions. OLAP data is organized hierarchically and stored in cubes instead of tables.) databases.

For information about filtering or joining data before you import it into Excel, see Microsoft Query Help.

  1. On the Data menu, point to Import External Data, and then click Import Data.
  2. Do one of the following:

ShowOpen an existing data source

In the Select Data Source dialog box, select a data source from My Data Sources, and then click Open.

ShowCreate a new data source connection

  1. In the Select Data Source dialog box, click New Source.
  2. In the Data Connection Wizard, choose a data source to connect to, and then click Next.

Notes

  • If you can't find the data source you want, contact your system administrator.
  • If you choose Other/Advanced under What kind of data source do you want to connect to?, you are given a list of OLE DB providers. For more information about connecting to a provider, see your OLE DB provider documentation.
  • If you choose Microsoft Business Solutions or Data retrieval services under What kind of data source do you want to connect to?, a databound XML list that can be refreshed from the data source will be created in your worksheet. To use these data sources, you must have access to a server that is running Microsoft Windows® SharePoint™ Services. A default installation of Windows SharePoint Services provides a data retrieval service for connecting to data in SharePoint lists. A SharePoint site administrator can install the Microsoft Office Web Parts and Components to add additional data retrieval services for Microsoft SQL Server™ and Microsoft Business Solutions. The installation program for Microsoft Office Web Parts and Components is available from the Downloads on Microsoft Office Online site.
  1. Provide the information requested by the Data Connection Wizard dialog boxes.
  2. If you want, you can do the following in the Import Data dialog box:
    1. Click Properties to choose query definition, refresh, and formatting and layout options, and then click OK.
    2. Click Edit Query to edit the Connection, Command Type, and Command Text of your OLE DB query.
    3. For a query created with Microsoft Query or for a Web query, click Parameters to specify how a parameter value is obtained for the external data range.
  3. Do one of the following:
  • To return the external data to the selected worksheet, click Existing worksheet. On your worksheet, click the cell in which you want to place the upper-left corner of the external data range, and then click OK.
  • To return the external data to a new worksheet, click New worksheet, and then click OK. Excel adds a new worksheet to your workbook and automatically puts the external data range in the upper-left corner of the new worksheet.

ShowAbout importing data from databases and files

You can import data to Excel from most data sources by pointing to Import External Data on the Data menu, clicking Import Data, and then choosing the data you want to import in the Select Data Source dialog box.

The Data Connection Wizard

The Data Connection Wizard, available when you click New Source in the Select Data Source dialog box, makes it possible to import data from external data connections not available from the Select Data Source dialog box. These sources may include OLE DB data sources (including OLAP cubes and servers running Microsoft Exchange) and any data sources a system administrator supplies. You cannot filter or join data in the Data Connection Wizard.

The default connection method when you import data using the Data Connection Wizard is through OLE DB providers. The resulting office data connection (ODC) files can be opened for viewing in Microsoft Internet Explorer and edited in Excel, Notepad, and other Microsoft Office 2003 programs if the file doesn't point to an OLAP data source.

Data retrieval service

The Data Connection Wizard also provides access to a data source called a data retrieval service. A data retrieval service is a Web service installed on Microsoft Windows® SharePoint™ Services for connecting to and retrieving data. To use a data retrieval service, a client application, such as Excel, sends a query request over HTTP (HTTP: Internet protocol that delivers information on the World Wide Web. Makes it possible for a user with a client program to enter a URL (or click a hyperlink) and retrieve text, graphics, sound, and other digital information from a Web server.) to the data retrieval service on Windows SharePoint Services. The data retrieval service sends that request to the data source, and then passes the data that is returned to it back to the client application as XML. Importing data from a data retrieval service in Excel automatically creates a databound XML list in your worksheet. After adding a databound XML list to your worksheet, you can use the commands on the XML submenu of the Data menu or the XML and List toolbar to refresh data, edit the query, or set the properties of the XML map associated with the XML list.

A default installation of Windows SharePoint Services provides a data retrieval service for connecting to data in SharePoint lists. A SharePoint site administrator can install the Microsoft Office Web Parts and Components to add additional data retrieval services for Microsoft SQL Server™ and Microsoft Business Solutions. The installation program for Microsoft Office Web Parts and Components is available on the Downloads on Microsoft Office Online site.

 Note   To bring external data (external data: Data that is stored outside of Excel. Examples include databases created in Access, dBASE, SQL Server, or on a Web server.) into Microsoft Excel, you must have access to the data. If the external data source you want to access is not on your local computer, you might need to contact the administrator of the database for a password, user permission, or other connection information.

ShowAbout importing data with Microsoft Query

In most cases, you can import data by using the Import Data command as described in the preceding section. Use Query or another program only if you need to perform specialized query tasks such as the following:

Microsoft Query provides a simple interface, easily accessible from within Excel, to perform these specialized query tasks.

You can use Query to set up open database connectivity (ODBC) data sources to retrieve data. In Query, you can use the Query Wizard to create a simple query (query: In Query or Access, a means of finding the records that answer a particular question you ask about the data stored in a database.), or you can use advanced criteria to create a more complex query. You can access Query from Excel, or you can create a query from within the PivotTable® and PivotChart® Wizard.

You can also use Dynamic Data Exchange (DDE) (Dynamic Data Exchange (DDE): An established protocol for exchanging data between Microsoft Windows-based programs.) with Query. For more information about DDE, see Query Help.

To import data using Query, you must first:

For more information, see Query Help.

ShowAbout importing data from the Web

You can import data originating from a Web page by pointing to Import External Data on the Data menu and clicking New Web Query. You must have access to the World Wide Web (WWW) (World Wide Web (WWW): The multimedia branch of the Internet that presents not only text, but also graphics, sound, and video. On the Web, users can easily jump from item to item, page to page, or site to site by using hyperlinks.) through your company's intranet or through a modem on your computer or network, or you can make a query against local HTML or XML sources.

ShowAbout importing data with Microsoft Visual Basic® for Applications (VBA)

You can use a Visual Basic for Applications (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.)  macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) to gain access to an external data source.

Depending on the data source, you will use either ActiveX® Data Objects (ActiveX Data Objects (ADO): A data access interface that communicates with OLE DB-compliant data sources to connect to, retrieve, manipulate, and update data.) or Data Access Objects (Data Access Objects (DAO): A data access interface that communicates with Microsoft Jet and ODBC-compliant data sources to connect to, retrieve, manipulate, and update data and the database structure.) to retrieve data using VBA.

If you want to use a macro that you created in Excel version 5.0 or earlier, click Add-Ins on the Tools menu, and then make sure the ODBC Add-In check box is selected.

For information about creating Visual Basic for Applications macros, see Visual Basic Help (Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.).

 Note   While you are recording a macro that includes a query, Excel can't run the query in the background, even if you chose to run it that way. To change the recorded macro so that it runs in the background, edit the macro in the Visual Basic Editor and change the refresh method for the QueryTable object from "BackgroundQuery := False" to "BackgroundQuery := True".

Refreshing data and naming ranges

ShowRefreshing data

Excel provides many options for refreshing imported data, including refreshing the data whenever you open the workbook and automatically refreshing data at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status of the refresh while it's being refreshed.

If your external data source (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.) requires a password (password: A word, phrase, or string of characters used to prevent unauthorized access to a database. To gain access to the database, you must enter the password correctly.) to gain access to the data, you can require that the password be entered each time the external data range (external data range: A range of data that is brought into a worksheet but that originates outside of Excel, such as in a database or text file. In Excel, you can format the data or use it in calculations as you would any other data.) is refreshed.

When an external data range expands and additional records are returned, Excel can fill formulas in adjacent columns or within the data range so that they remain next to the appropriate data.

You can also choose how to add new data to your worksheet.

ShowNaming external data ranges

Excel automatically names an external data range as follows:

If your worksheet has more than one external data range from the same source, the ranges are numbered. For example, MyText, MyText_1, MyText_2, and so on.

You can also change the name of an external data range in the Data Range Properties dialog box.

Data sources you can access

Microsoft Office 2003 provides drivers that you can use to retrieve data from the following data sources (data source: A stored set of "source" information used to connect to a database. A data source can include the name and location of the database server, the name of the database driver, and information that the database needs when you log on.):

You can use ODBC drivers (Open Database Connectivity (ODBC) driver: A program file used to connect to a particular database. Each database program, such as Access or dBASE, or database management system, such as SQL Server, requires a different driver.) or data source drivers (data source driver: A program file used to connect to a specific database. Each database program or management system requires a different driver.) from other manufacturers to get information from data sources that are not listed here, including other types of OLAP databases. For information about other drivers, see the xlreadme.htm file. For information about installing an ODBC driver or data source driver that is not listed here or in the xlreadme.htm file, check the documentation for the database, or contact your database vendor.

The Data Connection Wizard also provides access to a data source called a data retrieval service. A data retrieval service is a Web service installed on Microsoft Windows® SharePoint™ Services for connecting to and retrieving data. A data retrieval service can provide access to the following data sources:

  • List and document libraries on Windows SharePoint Services
  • Microsoft SQL Server
  • Microsoft Business Solutions
 
 
Applies to:
Excel 2003