Retrieving Excel external data using Microsoft Query

You can use Microsoft Query to retrieve data from an external source. By using Microsoft Query, from an enterprise database database: A collection of data related to a particular topic or purpose. Within a database, information about a specific entity, such as an employee or order, is categorized into tables, records, and fields. and file to retrieve data, you can avoid typing the data you want to analyze in Excel. In addition, you can automatically refresh Excel reports and summaries from the original source database as soon as the new information is updated.

Learn more about Microsoft Query

With Microsoft Query, you can connect to an external data source, select data from those external data sources, import that data into a worksheet, and refresh the data as needed to keep the worksheet data synchronized with the data in the external source.

Accessible database types you can retrieve data from multiple types of databases, including Microsoft Office Access, Microsoft SQL Server, and Microsoft SQL Server OLAP Services. You can also retrieve data from Excel workbooks and text files.

Microsoft Office provides a set of "source" information that can be used from the following data sources data source: A group of storage used to connect to a database. The data source contains the name and location of the database server, the name of the database driver, and the information required to log on to the database. ) The driver that retrieves the data:

· Microsoft SQL Server Analysis Services (OLAP provider: A set of software that provides access to a particular type of OLAP database. The software includes data source drivers and other client software necessary to connect to the database. ))

· Microsoft Office Access

dbase

· Microsoft FoxPro

· Microsoft Office Excel

· Oracle

· Paradox

• Text File database

You can also use ODBC drivers from other manufacturers Open Database Connectivity (ODBC) drivers: Program files that are used to connect to a specific database. Each database program (such as Access or DBASE) or a database management system (such as SQL Server) requires a different driver. or data source driver data source driver: A program file that is used to connect to a specified database. Each database program or management system requires a different driver. , which never retrieves information from the data sources listed here, including other types of OLAP databases. For information about installing an ODBC driver or data source driver that is not listed here, check your database documentation or contact your database vendor.

Select data from a database you retrieve data from a database by creating a query, which is a question you raised about data stored in an external database. For example, if your data is stored in an Access database, you might want to know the sales figures for a particular product in each region. You can retrieve part of the data by selecting only the data for the products and regions that you want to analyze.

With Microsoft Query, you can select the data columns that you want and only import that data into Excel.

Update a worksheet with an action if your Excel workbook contains external data, you can refresh (refresh: Update the data from the external data source) whenever the database changes. Each time you refresh the data, you'll see the latest version of the information in the database, including any changes you made to the data. Data to update your analysis without having to re-create the summary report and the chart. For example, you can create a monthly sales summary and refresh it after each month's new sales figures come out.

When Microsoft query uses a data source to set up a data source for a particular database, you can use the data source whenever you want to create a query to select and retrieve data from that database without having to retype all the connection information. Microsoft Query uses the data source to connect to an external database and display the available data. After you create the query and return the data to Excel, Microsoft query provides query and data source information for the Excel workbook so that you can reconnect to the database when you need to refresh the data.

Import data using Microsoft Query to import external data into Excel using Microsoft Query, follow these basic steps, each of which is described in detail in the following sections.