If  you want to bring external data into microsoft excel data files, you have to 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 required connection information.

Internet Explorer or Excel, Notepad and other Microsoft office applications can not write files that points to an OLAP (On-line retrieval and analysis of data to reveal business trends and statistics not directly visible in the data directly retrieved from a data warehouse. Also known as multidimensional analysis or Online Analytical Processing) data source.

Importing data

    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, 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 exchange servers) 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 odc (office data connection) files can be opened for viewing in Internet Explorer and edited in Excel, Notepad, and other Microsoft Office applications if the file doesn?t point to an OLAP data source.

Note  To bring external data 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.
 
    Importing data with Microsoft Query

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

·    Filter rows or columns of data before they are brought into Excel
·    Create a parameter query
·    Sort data before it is brought into Excel
·    Join multiple tables.

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

You can use Query to set up ODBC data sources to retrieve data.  In Query, you can use the Query Wizard to create a simple query, or you can use advanced criteria in Query 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) with Query.  For more information about DDE, see Query Help.

To import data using Query, you must first:

·    Install Query   Query, including the Query Wizard, is an optional feature for Excel.  Under most circumstances, you are prompted to install Query when you point to Import External Data on the Data menu and click New Database Query.
·    Install ODBC drivers  An ODBC driver is required to retrieve data in relational databases, text files, or Excel using  Query.  When you install Query, you automatically install a set of ODBC drivers.  If you use a driver other than one installed with Query, you must install the driver separately.
·    Install data source drivers  A data source driver is required to retrieve OLAP source data.  Query supports connecting to databases that are created by using SQL Server OLAP Services; when you installed Query, you automatically installed support for this type of OLAP database.  To connect to other OLAP databases, you must install a data source driver and client software.