LikeOffice.com

excel utility

Keep In Touch:
 contact us  facebook
 
Get HELP with your Excel Project:
 Excel Formula
 List of Excel Formula examples
 
Home >> excel 2007 >> Excel 2007 Importing data

This page presents how to work with Microsoft Excel 2007 features and its redesigned interface. You may also visit our Excel Question page.

 

   Overview of connecting (importing) data

   Connect to (Import) external data

   Refresh connected (imported) data

   Create, edit, and manage connections to external data

   Remove the data connection from an external data range

   Connection properties

   Manage external data ranges and their properties

   Connect to (import) SQL Server data

   Get external data from a Web page

   Exchange (copy, import, export) data between Excel and Access

   Customize a parameter query

   Import or export text files

   Text Import Wizard

   Use Microsoft Query to retrieve external data

   Import data from a SharePoint list

 


Overview of connecting (importing) data

There is a world of data outside of your Microsoft Office Excel workbook that you can use inside of your workbook. But how do you find it, import it, update it, secure it, and manage it? It is all about making the right connections.

In this article

Working with external data connections

Understanding the basics of data connections

Finding connections

Editing connection properties

Managing connections

Sharing connections

Using external data ranges and properties

Understanding data source support in Excel and Excel Services

Understanding Microsoft Data Access Components

Using ODBC to connect to data sources

Using OLE DB to connect to data sources

Refreshing data

Importing data from data sources

Importing data from an Access database

Importing data from the Web

Importing text files

Importing data from Microsoft SQL Server

Importing data from Microsoft SQL Server Analysis Services

Importing XML data

Importing data by using the Data Connection Wizard

Importing data by using Microsoft Query

Importing data programmatically and by using functions

Making data access more secure

Storing data connections in a trusted location

Using credentials in a safe manner

Publishing to Excel Services securely

Issues about connecting to data

Issue: I run out of disk space when I try to import data.

Issue: Where is the OLAP Cube Wizard?

Issue: What happened to data retrieval services?

Working with external data connections

The following sections discuss how external data connections work, and how to find edit, manage, and share the connection information with other applications and users.

 Top of Page

Understanding the basics of data connections

Data in an Excel workbook can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as a text file, a database, or an Online Analytical Processing (OLAP) cube. This external data source is connected to the workbook through a data connection, which is a set of information that describes how to locate, log in, and access the external data source.

The main benefit of connecting to external data is that you can periodically analyze this data without repeatedly copying the data, which is an operation that can be time-consuming and error-prone. After connecting to external data, you can also automatically refresh (or update) your Excel workbooks from the original data source whenever the data source is updated with new information.

Connection information is stored in the workbook and can also be stored in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Data Source Name file (.dsn).

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 Excel, you need access to the data. If the 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.) that you want to access is not on your local computer, you may need to contact the administrator of the database for a password, user permission, or other connection information. If the data source is a database, make sure that the database is not opened in exclusive mode. If the data source is a text file or a spreadsheet, make sure that another user does not have it open for exclusive access.

Many data sources also require an ODBC driver or OLE DB provider to coordinate the flow of data between Excel, the connection file, and the data source.

The following diagram summarizes the key points about data connections.

There are a variety of data sources that you can connect to: Analysis Services, SQL Server, Microsoft Office Access, other OLAP and relational databases, spreadsheets, and text files.

Many data sources have an associated ODBC driver or OLE DB provider.

A connection file defines all of the information that is needed to access and retrieve data from a data source.

Connection information is copied from a connection file into a workbook and can be edited.

The data is copied into a workbook so that you can use it just as you use data stored directly in the workbook.

 Top of Page

Finding connections

To find connection files, use the Existing Connections dialog box. (On the Data tab, in the Get External Data group, click Existing Connections.) Using this dialog box, you can see the following types of connections:

  • Connections in the workbook  

This list displays all of the current connections in the workbook. The list is created from connections that you already defined, that you created by using the Select Data Source dialog box of the Data Connection Wizard, or from connections that you previously selected as a connection from this dialog box.

  • Connection files on your computer  

This list is created from the My Data Sources folder that is usually stored in the My Documents (Windows XP) or Documents (Windows Vista) folder.

  • Connection files on the network  

This list can be created from the following:

    • A set of folders on your local network, the location of which can be deployed across the network as part of the deployment of Microsoft Office group policies.
    • An Excel Services Data Connection Library (DCL) on a Microsoft Office SharePoint Server 2007 site. For more information about DCLs, see Publish to Excel Services.

 Top of Page

Editing connection properties

You can also use Excel as a connection file editor to create and edit connections to external data sources that are stored in a workbook or in a connection file. If you don't find the connection that you want, you can create a connection by clicking Browse for More to display the Select Data Source dialog box, and then clicking New Source to start the Data Connection Wizard.

Once you create the connection, you can use the Connection Properties dialog box (On the Data tab, in the Get External Data group, click Properties.) to control various settings for connections to external data sources, and to use, reuse, or switch connection files.

If you use a connection file to connect to a data source, Excel copies the connection information from the connection file into the Excel workbook. When you make changes by using the Connection Properties dialog box, you are editing the data connection information that is stored in the current Excel workbook and not the original data connection file that may have been used to create the connection, which is indicated by the file name that is displayed in the Connection File property on the Definition tab. Once you edit the connection information (with the exception of the Connection Name and Connection Description properties), the link to the connection file is removed and the Connection File property is cleared.

To ensure that the connection file is always used when a data source is refreshed, click Always attempt to use this file to refresh this data on the Definition tab. Selecting this check box ensures that updates to the connection file will always be used by all workbooks that use that connection file, which must also have this property set.

 Top of Page

Managing connections

By using the Workbook Connections dialog box, you can easily manage these connections, including creating, editing, and deleting them. (On the Data tab, in the Get External Data group, click Connections.) You can use this dialog box to do the following:

  • Create, edit, refresh, and delete connections that are in use in the workbook.
  • Verify where external data is coming from, because, for example, the connection was defined by another user.
  • Show where each connection is used in the current workbook.
  • Diagnose an error message about connections to external data.
  • Redirect a connection to a different server or data source, or replace the connection file for an existing connection.
  • Make it easy to create and share connection files with users.

 Top of Page

Sharing connections

Connection files are particularly useful for sharing connections on a consistent basis, making them more discoverable, helping to improve their security, and facilitating data source administration. The best way to share connection files is to put them in a secure and trusted location, such as a network folder or SharePoint library, where users can read the file but only designated users can modify the file.

Using ODC files

You can create Office Data Connection (ODC) files (.odc) by connecting to external data through the Select Data Source dialog box or by using the Data Connection Wizard to connect to new data sources. An ODC file uses custom HTML and XML tags to store the connection information. You can easily view or edit the contents of the file in Office Excel.

You can share connection files with other people to give them the same access that you have to an external data source. Other users don't need to set up a data source to open the connection file, but they may need to install the ODBC driver or OLE DB provider required to access the external data on their computer.

ODC files are the recommended method for connecting to data and sharing data. You can easily convert other traditional connection files (DSN, UDL, and query files) to an ODC file by opening the connection file and then clicking the Export Connection File button on the Definition tab of the Connection Properties dialog box.

Using query files

Query files are text files that contain data source information, including the name of the server where the data is located and the connection information that you provide when you create a data source. Query files are a traditional way for sharing queries with other Excel users.

Using .dqy query files   You can use Microsoft Query to save .dqy files that contain queries for data from relational databases or text files. When you open these files in Microsoft Query, you can view the data in the query and select different data for the query to retrieve. You can save a .dqy file for any query that you create, either by using the Query Wizard or directly in Microsoft Query.

Using .oqy query files   You can save .oqy files to connect to data in an OLAP database, either on a server or in an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) (.cub). When you use the Multi-Dimensional Connection Wizard in Microsoft Query to create a data source for an OLAP database or cube, an .oqy file is created automatically. Because OLAP databases aren't organized in records or tables, you can't create queries or .dqy files to access these databases.

Using .rqy query files   Excel can open query files in .rqy format to support OLE DB data source drivers that use this format. For more information, see the documentation for your driver.

Using .qry query files   Microsoft Query can open and save query files in .qry format for use with earlier versions of Microsoft Query that cannot open .dqy files. If you have a query file in .qry format that you want to use in Excel, open the file in Microsoft Query, and then save it as a .dqy file. For information about saving .dqy files, see Microsoft Query Help.

Using .iqy Web query files   Excel can open .iqy Web query files to retrieve data from the Web.

 Top of Page

Using external data ranges and properties

An external data range (also called a query table) is a defined name or table name that defines the location of the data brought into a worksheet. When you connect to external data, Excel automatically creates an external data range. The only exception to this is a PivotTable report connected to a data source, which does not create an external data range. In Excel, you can format and lay out an external data range or use it in calculations, as with any other data.

Excel automatically names an external data range as follows:

  • External data ranges from Office Data Connection (ODC) files are given the same name as the file name.
  • External data ranges from databases are named with the name of the query. By default Query_from_source is the name of the data source that you used to create the query.
  • External data ranges from text files are named with the text file name.
  • External data ranges from Web queries (Web query: A query that retrieves data stored on your intranet or the Internet.) are named with the name of the Web page from which the data was retrieved.

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.

An external data range has additional properties (not to be confused with connection properties) that you can use to control the data, such as the preservation of cell formatting and column width. You can change these external data range properties by clicking Properties in the Connections group on the Data tab, and then making your changes in the External Data Range Properties or External Data Properties dialog boxes.

 Note    If you want to share a summary or report that is based on external data, you can give other people a workbook that contains an external data range, or you can create a report template. A report template (report template: An Excel template (.xlt file) that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel saves the query definition but doesn't store the queried data in the template.) lets you save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template.

 Top of Page

Understanding data source support in Excel and Excel Services

There are several data objects (such as an external data range and PivotTable report) that you can use to connect to different data sources. However, the type of data source that you can connect to is different between each data object. You can also use and refresh connected data in Excel Services, but there are additional limitations and workarounds that you should be aware of.

Excel data object and data source support

The following table summarizes which data sources are supported for each data object in Excel.

   
   
 

 

 

Supported data source

 

 

 

 

 

 

 

 

 

 

Excel
data
object

Creates
External
data
range?

 

OLE
DB

ODBC

Text
file

HTML
file

XML
file

Share-
Point
list

Import Text Wizard

Yes

 

No

No

Yes

No

No

No

PivotTable report
(non-OLAP)

No

 

Yes

Yes

Yes

No

No

Yes

PivotTable report
(OLAP)

No

 

Yes

No

No

No

No

No

Excel Table

Yes

 

Yes

Yes

No

No

Yes

Yes

XML Map

Yes

 

No

No

No

No

Yes

No

Web Query

Yes

 

No

No

No

Yes

Yes

No

Data Connection Wizard

Yes

 

Yes

Yes

Yes

Yes

Yes

Yes

Microsoft Query

Yes

 

No

Yes

Yes

No

No

No

 

 

 

 

 

 

 

 

 

 Note    These files, a text file imported by using the Import Text Wizard, an XML file imported by using an XML Map, and an HTML or XML file imported by using a Web Query do not use an ODBC driver or OLE DB provider to make the connection to the data source.

Excel Services and data source support

If you want to display an Excel workbook in Excel Services, you can connect to and refresh data, but you must use a PivotTable report. Excel Services does not support external data ranges, which means that Excel Services does not support an Excel Table connected to a data source, a Web query, an XML map, or Microsoft Query.

However, you can work around this limitation by using a PivotTable to connect to the data source, and then design and layout the PivotTable as a two-dimensional table without levels, groups, or subtotals so that all desired row and column values are displayed. For more information, see Use a PivotTable report to make external table data available in Excel Services.

 Top of Page

Understanding Microsoft Data Access Components

Microsoft Data Access Components (MDAC) 2.8 is included with Microsoft Windows Server 2003, Windows Vista, and Windows XP SP2. With MDAC, you can connect to and use data from a wide variety of relational and nonrelational data sources. You can connect to many different data sources by using Open Database Connectivity (ODBC) drivers or OLE DB (OLE DB: A component database architecture that implements efficient network and internet access to many types of data sources, including relational data, mail files, flat files, and spreadsheets.) providers, which are either built and shipped by Microsoft or developed by various third parties. When you install Microsoft Office, additional ODBC drivers and OLE DB providers are added to your computer.

To see a complete list of OLE DB providers installed on your computer, display the Data Link Properties dialog box from a Data Link file, and then click the Provider tab.

To see a complete list of ODBC providers installed on your computer, display the ODBC Database Administrator dialog box, and then click the Drivers tab.

You can also use ODBC drivers and OLE DB providers from other manufacturers to get information from sources other than Microsoft data sources, including other types of ODBC and OLE DB databases. For information about installing these ODBC drivers or OLE DB providers, check the documentation for the database, or contact your database vendor.

 Top of Page

Using ODBC to connect to data sources

The following sections describe Open Database Connectivity (ODBC) in more detail.

The ODBC architecture

In the ODBC architecture, an application (such as Excel) connects to the ODBC Driver Manager, which in turn uses a specific ODBC driver (such as the Microsoft SQL ODBC driver) to connect to a data source (such as a Microsoft SQL Server database).

Defining connection information

To connect to ODBC data sources, do the following:

  1. Ensure that the appropriate ODBC driver is installed on the computer that contains the data source.
  2. Define a data source name (DSN) by using either the ODBC Data Source Administrator to store the connection information in the registry or a DSN file, or a connect string in Microsoft Visual Basic code to pass the connection information directly to the ODBC Driver Manager.

To define a data source, in Windows Vista, click the Start button and then click Control Panel. Click System and Maintenance, and then click Administrative Tools. In Windows XP and Windows Server, click Start, and then click Control Panel. Click Performance and Maintenance, click Administrative Tools. and then click Data Sources (ODBC). For more information about the different options, click the Help button in each dialog box.

Machine data sources

Machine data sources store connection information in the registry, on a specific computer, with a user-defined name. You can use machine data sources on only the computer they are defined on. There are two types of machine data sources  user and system. User data sources can be used only by the current user and are visible only to that user. System data sources can be used by all users on a computer and are visible to all users on the computer.

A machine data source is especially useful when you want to provide added security, because it helps ensure that only users who are logged on can view a machine data source, and a machine data source cannot be copied by a remote user to another computer.

File data sources

File data sources (also called DSN files) store connection information in a text file, not the registry, and are generally more flexible to use than machine data sources. For example, you can copy a file data source to any computer with the correct ODBC driver, so that your application can rely on consistent and accurate connection information to all the computers it uses. Or you can place the file data source on a single server, share it between many computers on the network, and easily maintain the connection information in one location.

A file data source can also be unshareable. An unshareable file data source resides on a single computer and points to a machine data source. You can use unshareable file data sources to access existing machine data sources from file data sources.

 Top of Page

Using OLE DB to connect to data sources

The following sections describe Object Linking and Embedding Database (OLE DB) in more detail.

The OLE DB architecture

In the OLE DB architecture, the application that accesses the data is called a data consumer (such as Excel), and the program that allows native access to the data is called a database provider (such as Microsoft OLE DB Provider for SQL Server).

Defining connection information

A Universal Data Link file (.udl) contains the connection information that a data consumer uses to access a data source through the OLE DB provider of that data source. You can create the connection information by doing one of the following:

  • In the Data Connection Wizard, use the Data Link Properties dialog box to define a data link for an OLE DB provider. For more information, see Importing data by using the Data Connection Wizard.
  • Create a blank text file with a .udl file type, and then edit the file, which displays the Data Link Properties dialog box.

 Top of Page

Refreshing data

When you are connected to an external data source, you can also perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

The following illustration explains the basic process of what happens when you refresh data connected to an external data source.

A refresh operation gets up-to-date data.

The connection file defines all of the information that is needed to access and retrieve data from an external data source.

There are a variety of data sources that you can refresh: OLAP, SQL Server, Access, OLE DB, ODBC, spreadsheets, and text files.

Up-to-date data is added to the current workbook.

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 the data is being refreshed.

If your external data source 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 is 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.

 Top of Page

Importing data from data sources

Windows XP, Windows Vista, and Microsoft Office supply the ODBC drivers and OLE DB providers that you can use to retrieve data from the following common data sources: Microsoft Office Access, HTML files on the World Wide Web, text files, Microsoft SQL Server, Microsoft SQL Server Analysis Services, and XML files. By using the Data Connection Wizard and Microsoft Query, you can also access many other data sources that have the appropriate OLE DB providers and ODBC drivers, including other Excel worksheets, Microsoft FoxPro, dBASE, Paradox, Oracle, and DB2.

 Top of Page

Importing data from an Access database

There are several ways to exchange data between Microsoft Office Access and Microsoft Office Excel.

  • To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.
  • To bring data into Access from Excel, you can copy data from an Excel worksheet and paste it into an Access datasheet, import an Excel worksheet into an Access table, or link to an Excel worksheet from an Access table.

 Note    The word import has two different meanings between Excel and Access. In Excel, the word import means to make a permanent connection to data that can be refreshed. In Access, the word import means to bring data into Access once, but without a data connection.

Working with Access data in Excel

You may want to work with Access data in an Excel workbook in order to take advantage of the data analysis and charting features, the flexibility in data arrangement and layout, or functions that are not available in Access.

Connecting to Access data from Excel

To bring refreshable Access data into Excel, you can create a connection to the Access database and retrieve all of the data from a table or query. For example, you may want to update an Excel summary budget report that you distribute every month so that it contains the current month's data.

Exporting Access data to Excel

By using the Export Wizard in Access, you can export an Access database object, such as a table, query, form, or selected records in a view into an Excel worksheet. When you perform an export operation, you can save the details for future use and even schedule the export operation to run automatically at specified intervals.

The following are common scenarios for exporting data from Access to Excel:

  • Your department or workgroup uses both Access and Excel to work with data. You store the data in Access databases, but you use Excel to analyze the data and to distribute the results of your analysis. Your team currently exports data to Excel when they need to, but you would like to make this process more efficient.
  • You are a long-time user of Access, but your manager prefers to view reports in Excel. At regular intervals, you do the work of copying the data into Excel, but you would like to automate this process to save yourself time.

For more information about exporting data from Access to Excel, see the Access Help system.

Working with Excel data in Access

You may want to work with Excel data in an Access database to take advantage of Access data management, security, or multiuser features. Although there are many useful features in Access, there are two features that users find particularly useful for their Excel data:

  • Reports   If you are familiar with designing Access reports and you want to summarize and organize your Excel data in this type of report, you can create an Access report. For example, you can create more flexible reports, such as group and summary reports, printed labels, and graphical reports.
  • Forms   If you want to use a form to find or to display data in Excel, you can create an Access form. For example, you can create an Access form to display fields in a different order from the order of columns in your worksheet, or you can view a lengthy row of data more easily on one screen.

For more information about working with Access forms and reports, see the Access Help system.

Linking to Excel data from Access

You can link an Excel range into an Access database as a table. Use this approach when you plan to continue maintaining the range in Excel but also want the range to be available from within Access. You create this type of link from within the Access database, not from Excel.

When you link to an Excel worksheet or a named range, Access creates a new table that is linked to the source cells. Any changes that you make to the source cells in Excel are reflected in the linked table. However, you cannot edit the contents of the corresponding table in Access. If you want to add, edit, or delete data, you must make the changes in the source file.

The following are common scenarios for linking to an Excel worksheet from within Access:

  • You want to continue to keep your data in Excel worksheets, but be able to use the powerful querying and reporting features of Access.
  • Your department or workgroup uses Access, but data from external sources that you work with is in Excel worksheets. You don't want to maintain copies of external data, but you want to be able to work with it in Access.

For more information about linking data from Access to Excel, see the Access Help system.

Importing Excel data into Access

To store data from Excel in an Access database, and then use and maintain the data in Access from then on, you can import the data. When you import data, Access stores the data in a new or existing table without altering the data in Excel. You can import only one worksheet at a time during an import operation. To import data from multiple worksheets, repeat the import operation for each worksheet.

The following are common scenarios for importing Excel data into Access:

  • You are a long-time user of Excel but, going forward, you want to use Access to work with this data. You want to move the data in your Excel worksheets into one or more new Access databases.
  • Your department or workgroup uses Access, but you occasionally receive data in Excel format that must be merged with your Access databases. You want to import these Excel worksheets into your database as you receive them.
  • You use Access to manage your data, but the weekly reports that you receive from the rest of your team are Excel workbooks. You would like to streamline the import process to ensure that data is imported every week at a specific time into your database.

For more information about importing data from Excel to Access, see the Access Help system.

 Top of Page

Importing data from the Web

Web pages often contain information that is perfect for analysis in Excel. For example, you can analyze stock quotes in Excel by using information that comes directly from a Web page. Depending on your needs, you can retrieve data that is refreshable (that is, you can update the data in Excel with the latest data on the Web page), or you can get data from a Web page and keep it static on the worksheet.

You can use a Web query to retrieve data stored on your intranet or the Internet, such as a single table, multiple tables, or all of the text on a Web page, and analyze the data by using the tools and features in Excel. With the click of a button, you can easily refresh the data with the latest information from the Web page. For example, you can retrieve and update stock quotes from a public Web page, or retrieve and update a table of sales information from a company Web page.

You can import data that originates from a Web page by using the New Web Query dialog box. (On the Data tab, in the Get External Data group, click From Web.) You need access to the World Wide Web (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 files.

 Top of Page

Importing text files

You can use Excel to import data from a text file into a worksheet. (On the Data tab, in the Get External Data group, click From Text.) The Text Import Wizard examines the text file that you are importing and helps you ensure that the data is imported in the way that you want.

There are two ways to import data from a text file by using Excel: You can open the text file in Excel (which does not make a connection to the text file), or you can import the text file as an external data range (which does make a connection to the text file).

There are two commonly used text file formats:

  • Delimited text files (.txt), in which the TAB character (ASCII character code 009) usually separates each field of text.
  • Comma-separated values (CSV) text files (.csv), in which the comma character (,) usually separates each field of text.

You can also change the separator character that is used in both delimited and .csv text files. This may be necessary to make sure that the import or export operation works the way that you want it to.

 Top of Page

Importing data from Microsoft SQL Server

Microsoft SQL Server is a full-featured, relational database designed for enterprise-wide data solutions that require optimum performance, availability, scalability, and security. In Excel, you can easily connect to a Microsoft SQL Server database. (On the Data tab, in the Get External Data group, click From Other Sources, and then click From SQL Server.)

When you connect to a Microsoft SQL Server database, the Data Connection Wizard displays three pages:

  • Page 1: Connect to Database Server   Use this page to specify the server and the way that you log on to the database server.
  • Page 2: Select Database and Table  Use this page to specify the database, and table or query.
  • Page 3: Save Data File and Connection  Use this page to specify and describe the connection file and search phrases for locating the file.

 Top of Page

Importing data from Microsoft SQL Server Analysis Services

Analysis Services (a component of Microsoft SQL Server) that supports business intelligence and is a foundation for Online Analytical Processing (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.) analysis systems, Key Performance Indicator (KPI) scorecards, data mining, and dashboard reporting systems. In Excel, you can easily connect to an an Analysis Services OLAP database by using an OLAP provider. (On the Data tab, in the Get External Data group, click From Other Sources, and then click From Analysis Services.) An OLAP provider is a set of software that provides access to a particular type of OLAP database. This software can include a data source driver and other client software that is necessary to connect to a database. You must use a PivotTable (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources, including ones that are external to Excel.) report to connect to an OLAP Provider.

You can also access OLAP data when you are disconnected from the OLAP data source. An offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.) is a file with a .cub extension that stores a portion of the source data (source data: The list or table that's used to create a PivotTable or PivotChart report. Source data can be taken from an Excel list or range, an external database or cube, or another PivotTable report.) from an OLAP server database. Use an offline cube file to continue to make changes to PivotTable and PivotChart reports (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) when the server is unavailable or when you are disconnected from the network.

When you connect to a Microsoft SQL Server Analysis Services, the Data Connection Wizard displays three pages:

  • Page 1: Connect to Database Server   Use this page to specify the server and the way that you log on to the database server.
  • Page 2: Select Database and Table  Use this page to specify the database and cube.
  • Page 3: Save Data File and Connection  Use this page to specify and describe the connection file and search phrases for locating the file.

 Top of Page

Importing XML data

Office Excel makes it easy to import Extensible Markup Language (XML) (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) data that is created from other databases and applications, map XML elements from an XML schema (XML Schema: A formal specification, written in XML, that defines the structure of an XML document, including element names and rich data types, which elements can appear in combination, and which attributes are available for each element.) to worksheet cells, and export revised XML data for interaction with other databases and applications. Think of these new XML features as turning Excel into an XML data file generator with a familiar user interface.

By using XML maps, you can easily add, identify, and extract specific pieces of business data from Excel documents. For example, an invoice that contains the name and address of a customer, or a report that contains last quarter's financial results are no longer just static reports. You can easily import this information from databases and applications, revise it and export it to the same or other databases and applications.

Key XML scenarios

The following are key scenarios that the XML features are designed to address:

  • Extend the functionality of existing Excel templates by mapping XML elements onto existing cells. This makes it easier to get XML data into and out of your templates without having to redesign them.
  • Use XML data as input to your existing calculation models by mapping XML elements onto existing spreadsheets.
  • Import XML data files into a new workbook.
  • Import XML data from a Web service into your Excel worksheet.
  • Export data in mapped cells to XML data files independent from other data in the workbook.

The basic process of using XML data in Excel

The following diagram shows how the different files and operations work together when you use XML with Excel. Essentially, there are five phases to the process.

Adding an XML schema file (.xsd) to a workbook.

Mapping XML schema elements to individual cells or XML lists.

Importing an XML data file (.xml) and binding the XML elements to mapped cells.

Entering data, moving mapped cells, and leveraging Excel functionality, while preserving XML structure and definitions.

Exporting revised data from mapped cells to an XML data file.

Using XML data

When you import the contents of an XML data file into an existing XML map in your workbook, you bind the data from the file to an XML map stored in your workbook. This means that each data element in the XML data file has a corresponding element in the XML schema that you mapped from an XML Schema file or inferred schema. Each XML map can only have one XML data binding, and an XML data binding is bound to all of the mappings that were created from a single XML map.

You can display the XML Map Properties dialog box (On the Developer tab, in the XML group, click Map Properties.), where there are three options, all on by default, that you can set or clear to control the behavior of an XML data binding:

  • Validate data against schema for import and export  Specifies if Excel validates data against the XML map when importing data. Set this option when you want to ensure that the XML data you import conforms to the XML schema.
  • Overwrite existing data with new data  Specifies if data is overwritten when you import data. Set this option when you want to replace the current data with new data, for example, when up-to-date data is contained in the new XML data file.
  • Append new data to existing XML lists  Specifies if the contents of the data source are appended to the existing data on the worksheet. Set this option, for example, when you are consolidating data from several similar XML data files into an XML list, or you do not want to overwrite the contents of a cell that contains a function.

 Top of Page

Importing data by using the Data Connection Wizard

You can use the Data Connection Wizard to connect to an OLE DB and ODBC external data source that has already been defined. To open the Data Connection Wizard, on the Data tab, in the Get External Data group, click From Other Sources, and then click From Data Connection Wizard.

If you choose the Other/advanced data source option in the Data Connection Wizard, you can view a list of available OLE DB providers in the Data Link Properties dialog box. In addition, the Microsoft OLE DB Provider for ODBC Drivers also allows access to ODBC data sources. For more information on using each tab in this dialog box, click Help in the Data Link Properties dialog box.

In general, to define connection information in the Data Link Properties dialog box, do the following :

  • Click the Provider tab, select the OLE DB provider, and then click Next, to display the Connection tab, to enter specific connection information for that OLE DB provider.

Each OLE DB provider defines specific connection information. For example, Microsoft OLE DB Provider for SQL Server requires a server name, server location, and a user name, and you may want to define additional information, such as a password or whether you want to use Microsoft Windows integrated security.

  • Click the Advanced tab to provide additional information, such as network settings and access permissions.
  • Click the All tab to define initialization properties for that OLE DB provider.

 Note    You cannot filter or join data in the Data Connection Wizard.

 Top of Page

Importing data by using Microsoft Query

You can also use Microsoft Query to import data. (On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.) You use Microsoft Query to set up ODBC data sources to retrieve data. In Microsoft 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 in Query to create a more complex query and do the following:

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

 Top of Page

Importing data programmatically and by using functions

If you are a developer, there are several approaches within Excel that you can take to import data:

For more information about creating Visual Basic for Applications, see Visual Basic Help.

 Top of Page

Making data access more secure

When you connect to an external data source or refresh the data, it is important to be aware of potential security issues and to know what you can do about these security issues. Use the following guidelines and best practices to help secure your data.

 Top of Page

Storing data connections in a trusted location

A data connection file often contains one or more queries that are used to refresh external data. By replacing this file, a user who has malicious intent can design a query to access confidential information and distribute it to other users or perform other harmful actions. Therefore, it is important to ensure the following:

  • The connection file was written by a reliable individual.
  • The connection file is secure and comes from a trusted location.

To help improve security, connections to external data may be disabled on your computer. To connect to data when you open a workbook, you must activate data connections by using the Trust Center bar, or by putting the workbook in a trusted location. For more information, see Create, remove, or change a trusted location for your files, Add, remove, or view a trusted publisher, and View my security settings in the Trust Center.

 Top of Page

Using credentials in a safe manner

Accessing an external data source usually requires credentials (such as a user name and a password) that are used to authenticate the user. Make sure that these credentials are provided to you in a safe and secure manner and that you do not inadvertently reveal these credentials to others.

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

Avoid saving logon information when connecting to data sources. This information can be stored as plain text in the workbook and the connection file, and a malicious user can access the information to compromise the security of the data source.

When possible, use Windows Authentication (also referred to as a trusted connection), which uses a Windows user account to connect to SQL Server. When a user connects through a Windows user account, SQL Server uses information in the Windows operating system to validate the account name and password. Before you can use Windows Authentication, a server administrator must configure SQL Server to use this mode of authentication. If Windows Authentication is not available, avoid saving users' logon information. It is more secure for users to enter their logon information each time that they log on.

 Top of Page

Publishing to Excel Services securely

When you connect to a data source, you can use the Excel Services Authentication Settings dialog box to choose a method of authentication when you access the data source in Excel Services. You can select one of the following options to log on to the data source:

  • Windows Authentication  Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can impact performance when there are many users.
  • SSO  Select this option to use Single Sign On, and then enter the appropriate identification string in the SSO ID text box. A site administrator can configure a SharePoint site to use a Single Sign On database where a user name and password can be stored. This method can be the most efficient when there are many users.
  • None  Select this option to save the user name and password in the connection file.

 Note    The authentication setting is only used by Excel Services, and not by Microsoft Office Excel. If you want to ensure that the same data is accessed whether you open the workbook in Excel or Excel Services, make sure that the authentication setting in Excel is the same.

To improve the security of your connections, use a Data Connection Library (DCL). A DCL is a special SharePoint document library that can be defined as a trusted location library, and that makes it easy to store, secure, share, and manage ODC files. For example, an administrator may need to move a database from a test server to a production server, or update a query that accesses the data. By using one ODC file saved in a DCL, administration of this connection information is much easier and the user's access to data is more convenient, because all workbooks use the same connection file, and a refresh operation (whether on the client or server computer) gets up-to-date changes to that connection file. You can even set up Office SharePoint Server and a user's client computer to automatically detect changes to the connection file and use the most up-to-date version of that connection file. For more information, see Office SharePoint Server 2007 Central Administration Help.

 Top of Page

Issues about connecting to data

The following sections discuss several issues that you may encounter when you connect to external data

 Top of Page

Issue: I run out of disk space when I try to import data.

If you run out of disk space when you connect to external data, consider doing the following:

Check and increase available disk space   To free some space on your hard disk, try emptying the Recycle Bin, backing up unneeded files and then removing them from your hard disk, or removing Windows components that you don't use. For more information about freeing hard disk space, see Windows Help.

Strategies that you can try when disk space is limited   If you have only a limited amount of space available on your hard disk, try the following:

Microsoft Query  Do one or more of the following:

Data Connection Wizard   Although you cannot limit the data through the Data Connection Wizard, you may be able to limit the data from the data source by defining a query in the data source. If you don't have the ability or permission to define a query at the data source, contact your data administrator.

 Top of Page

Issue: Where is the OLAP Cube Wizard?

The OLAP Cube Wizard in Microsoft Query, which created an OLAP Cube from a Jet database, is no longer available in Office Excel.

 Top of Page

Issue: What happened to data retrieval services?

Introduced in Microsoft Office 2003, data retrieval services use a Universal Data Connection (UDC) file (.udcx) to access Web Services and query different data sources.

In the Data Connection Wizard on the Welcome to the Data Connection Wizard page, you can select the Microsoft Business Solutions or Data retrieval services data source options. The Data retrieval services option allows you to choose two data retrieval services: Windows SharePoint Services lists, which retrieves data from lists and document libraries on a server running Microsoft Windows SharePoint Services, and Microsoft SQL Server, which retrieves data from Microsoft SQL Server 2000 or later.

Although the Data Connection Wizard no longer supports editing UDC connection files, Excel continues to support existing data retrieval services connections, and you can still refresh those connections.

 Top of Page

 

See Also

 

 

Excel > Importing data

Connect to (Import) external data

The main benefit of connecting to external data is that you can periodically analyze this data in Microsoft Office Excel without repeatedly copying the data, which is an operation that can be time-consuming and error-prone. After connecting to external data, you can also automatically refresh (or update) your Excel workbooks from the original data source whenever the data source is updated with new information.

Important  Connections to external data may be disabled on your computer. To connect to data when you open a workbook, you must enable data connections by using the Trust Center bar, or by putting the workbook in a trusted location. For more information, see Create, remove, or change a trusted location for your files, Add, remove, or view a trusted publisher, and View my security settings in the Trust Center.

  1. On the Data tab, in the Get External Data group, click Existing Connections.

  1. In the Existing Connections dialog box, in the Show drop-down list, do one of the following:
    • To display all connections, click All Connections. This is selected by default.
    • To display only the recently used list of connections, click Connections in this Workbook.

This list is created from connections that you have already defined, that you have created by using the Select Data Source dialog box of the Data Connection Wizard, or that you have previously selected as a connection from this dialog box.

    • To display only the connections that are available on your computer, click Connection files on this computer.

This list is created from the My Data Sources folder that is usually stored in the My Documents folder.

    • To display only the connections that are available from a connection file that is accessible from the network, click Connection files on the Network.

This list is created from an Excel Services Data Connection Library (DCL) on a Microsoft Office SharePoint Server 2007 site. A DCL is a document library in a Microsoft Office SharePoint Services 2007 site that contains a collection of Office Data Connection (ODC) files (.odc). Typically, a DCL is set up by a site administrator, who can also configure the SharePoint site to display ODC files from this DCL in the External Connections dialog box. For more information, see Office SharePoint Server 2007 Central Administration Help.

  1. Tip  If you do not see the connection that you want, you can create a connection. Click Browse for More, and then in the Select Data Source dialog box, click New Source to start the Data Connection Wizard so that you can select the data source that you want to connect to.
  2.  Note    If you choose a connection from the Connection files on the network or Connection files on this computer categories, the connection file is copied into the workbook as a new workbook connection, and then it is used as the new connection information.
  3. Select the connection that you want, and then click Open.
  4. In the Import Data dialog box, under Select how you want to view this data in your workbook do one of the following:

Important  The Select how you want to view this data in your workbook section and its options, as shown in the following list, are not available for text, Web Query, and XML data connections. If you are connecting to such data, continue to step 5.

    • To create a table for simple sorting and filtering, click Table.
    • To create a PivotTable report for summarizing large amounts of data by aggregating and subtotaling the data, click PivotTable Report.
    • To create a PivotTable report, and PivotChart report for visually summarizing data, click PivotChart and PivotTable Report.
    • To store the selected connection in the workbook for later use, click Only Create Connection.

Use the Only Create Connection option to store the selected connection in the workbook for later use. For example, if you are connecting to an Online Analytical Processing (OLAP) cube data source and you intend to convert PivotTable cells to worksheet formulas by using the Convert to Formulas command (On the Options tab, in the Tools group, click OLAP tools), you can use this option because you don't need to save the PivotTable report.

  1. Under Where do you want to put the data?, do one of the following:
    • To place the PivotTable or PivotChart report in an existing worksheet, select Existing worksheet, and then type the first cell in the range of cells where you want to locate the PivotTable report.

Alternatively, click Collapse Dialog to temporarily hide the dialog box, select the beginning cell on the worksheet, and then press Expand Dialog .

  1. To place the PivotTable report in a new worksheet starting at cell A1, click New worksheet.
  2. Optionally, you can change connection properties by clicking Properties, by making your changes in the Connection Properties , External Data Range , or XML Map Properties dialog boxes, and then by clicking OK.

 

See Also

 

 

Excel > Importing data

Refresh connected (imported) data

Excel 2007

A cell range, Excel table, PivotTable report, PivotChart report, text file, and Web query can all be connected to an 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.). You can refresh the data in order to update data from this external data source. Each time you refresh the data, you see the most recent version of the information in the data source, including any changes that were made to the data.

Security  Connections to external data may be currently disabled on your computer. To refresh the data when you open a workbook, you must enable the data connections by using the Trust Center bar, or you must place the workbook in a trusted location.

For more information, see Create, remove, or change a trusted location for your files, Add, remove, or view a trusted publisher, and View my security settings in the Trust Center.

What do you want to do?

Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query

Refresh data from an imported text file

Refresh an offline cube file

Refresh data in an imported XML file

Automatically refresh data when a workbook is opened

Automatically refresh data at regular intervals

Require a password to refresh an external data range

Run a query in the background or while you wait

Check the status of a refresh operation or cancel a refresh

Refresh data from a Microsoft Query, the Data Connection Wizard, or Web query

Do one of the following:

  • To refresh a specific data connection in the workbook, click a cell in the external data range. On the Data tab, in the Connections group, click the arrow next to Refresh All, and then click Refresh.
  • To refresh all data connections in the workbook, click Refresh All on the Data tab, in the Connections group.

 Note    If you have more than one workbook open, you must repeat the operation in each workbook.

 Top of Page

Refresh data from an imported text file

  1. Select the worksheet that contains the imported text file.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.
  3. In the Import Text File dialog box, select your text file, and then click Import.

 Top of Page

Refresh an offline cube file

Refreshing an offline cube file (offline cube file: A file you create on your hard disk or a network share to store OLAP source data for a PivotTable or PivotChart report. Offline cube files allow you to keep working when you are not connected to the OLAP server.), which re-creates the file by using the most recent data from the server cube, can be time consuming and require a lot of temporary disk space. Start the process at a time when you do not need immediate access to other files, and make sure that you have adequate disk space to save the file again.

  1. Click the PivotTable report that is based on the offline cube file.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Refresh.

 Top of Page

Refresh data in an imported XML file

  1. On the worksheet, click a mapped cell to select the XML map that you want to refresh.
  2. If the Developer tab is not available, do the following to display it:
    1. Click the Microsoft Office Button , and then click Excel Options.
    2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

 Note    The Ribbon is a component of the Microsoft Office Fluent user interface.

  1. On the Developer tab, in the XML group, click Refresh Data.

 Top of Page

Automatically refresh data when a workbook is opened

You can refresh an external data range automatically when you open the workbook. Optionally, you can save the workbook without saving the external data so that the file size of the workbook is reduced.

  1. Click a cell in the external data range.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

  1. Click the Usage tab.
  2. Under Refresh control, select the Refresh data when opening the file check box.
  3. If you want to save the workbook with the query definition but without the external data, select the Remove data from the external data range before saving the workbook check box.

 Note    To refresh the data when the workbook is opened for a PivotTable report, you can also select the Refresh data when opening the file check box. This check box is under the PivotTable Data section on the Data tab of the PivotTable Options dialog box.

For more information, see PivotTable options.

 Top of Page

Automatically refresh data at regular intervals

  1. Click a cell in the external data range.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

  1. Click the Usage tab.
  2. Select the Refresh every check box, and then enter the number of minutes between each refresh operation.

 Top of Page

Require a password to refresh an external data range

Stored passwords are not encrypted and are not recommended. If your data source requires a password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) to connect to it, you can require that users enter the password before they can refresh the external data range. The following procedure does not apply to data that is retrieved from a text file (.txt) or a Web query (.iqy).

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

  1. Click a cell in the external data range.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

  1. Click the Definition tab.
  2. Clear the Save password check box.

 Note    Excel prompts you for the password only the first time that the external data range is refreshed in each Excel session. The next time that you start Excel, you will be prompted for the password again if you open the workbook that contains the query and then attempt a refresh operation.

 Top of Page

Run a query in the background or while you wait

Running a query in the background enables you to use Excel while the query runs.

 Note    You cannot run an OLAP query in the background.

  1. Click a cell in the external data range.
  2. On the Data tab, in the Connections group, click the arrow next to Refresh, and then click Connection Properties.

  1. Click the Usage tab.
  2. Select the Enable background refresh check box to run the query in the background. Clear this check box to run the query while you wait.

 Note    While you are recording a 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.) that includes a query, Excel does not run the query in the background. To change the recorded macro so that the query runs in the background, edit the macro in the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.). Change the refresh method for the QueryTable object from BackgroundQuery := False to BackgroundQuery := True. For information about editing macros, see Visual Basic for Applications Help.

 Top of Page

Check the status of a refresh operation or cancel a refresh

Do one or more of the following:

 Top of Page

 

See Also

 

 

Excel > Importing data

Create, edit, and manage connections to external data

You can use Microsoft Office Excel to create and edit connections to external data sources that are stored in a workbook or in a connection file. By using the Workbook Connections dialog box, you can easily manage these connections, including creating, editing, and deleting them.

Security  Connections to external data may be currently disabled on your computer. To connect to data when you open a workbook, you must enable data connections by using the Trust Center bar, or by putting the workbook in a trusted location. For more information, see Create, remove, or change a trusted location for your files, Add, remove, or view a trusted publisher, and View my security settings in the Trust Center.

What do you want to do?

Learn about data connections

Manage connections by using the Workbook Connections dialog box

Create an Office Data Connection (ODC) file

Learn about data connections

Data in an Excel workbook can come from two different locations. The data may be stored directly in the workbook, or it may be stored in an external data source, such as a text file, a database, or an Online Analytical Processing (OLAP) cube. The external data source is connected to the workbook through a data connection, which is a set of information that describes how to locate, log in, query, and access the external data source.

When you are connected to an external data source, you can also perform a refresh operation to retrieve the updated data. Each time that you refresh data, you see the most recent version of the data, including any changes that were made to the data since it was last refreshed.

Connection information can either be stored in the workbook or in a connection file, such as an Office Data Connection (ODC) file (.odc) or a Universal Data Connection (UDC) file (.udcx). Connection files are particularly useful for sharing connections on a consistent basis and for facilitating data source administration.

If you use a connection file to connect to a data source, Excel copies the connection information from the connection file into the Excel workbook. When you make changes by using the Connection Properties dialog box, you are editing the data connection information that is stored in the current Excel workbook, and not the original data connection file that may have been used to create the connection, indicated by the file name that is displayed in the Connection File property. Once you edit the connection information (with the exception of the Connection Name and Connection Description properties), the link to the connection file is removed and the Connection File property is cleared.

 Top of Page

Manage connections by using the Workbook Connections dialog box

The Workbook Connections dialog box helps you manage one or more connections to external data sources in your workbook. You can use this dialog box to do the following:

  • Create, edit, refresh, and delete connections that are in use in the workbook.
  • Verify where external data is coming from, because, for example, the connection was defined by another user.
  • Show where each connection is used in the current workbook.
  • Diagnose an error message about connections to external data.
  • Redirect a connection to a different server or data source, or replace the connection file for an existing connection.
  • Display the Existing Connections dialog box to create new connections. For more information, see Connect to (Import) external data.
  • Display the Connection Properties dialog box to modify data connection properties, edit queries, and change parameters. For more information, see Connection properties.
  • Make it easy to create and share connection files with users.

To manage the connections in the current workbook, do one or more of the following:

Identify a connection

In the top portion of the dialog box, all connections in the workbook are displayed automatically with the following information:

Column

Comment

Name

The name of the connection, defined in the Connection Properties dialog box.

Description

An optional description of the connection, defined in the Connection Properties dialog box.

Last refreshed

The date and time that the connection was last successfully refreshed. If blank, then the connection has never been refreshed.

Add a connection

Display connection information

  • Select a connection, and then click Properties to display the Connection Properties dialog box. For more information, see Connection properties.

Refresh the external data

  • Click the arrow next to Refresh, and then do one of the following:
    • To refresh specific connections, select one or more connections, and then click Refresh.
    • To refresh all connections in the workbook, clear all connections, and then click Refresh All.
    • To get status information about a refresh operation, select one or more connections, and then click Refresh Status.
    • To stop the current refresh operation, click Cancel Refresh.

For more information, see Refresh connected (imported) data.

Remove one or more connections

  • Select one or more connections to be removed from the workbook, and then click Remove.

 Notes 

  • This button is disabled when the workbook is protected or an object, such as a PivotTable report, that uses the connection is protected.
  • Removing a connection only removes the connection and does not remove any object or data from the workbook.

Important  Removing a connection breaks the connection to the data source and may cause unintended consequences, such as different formula results and possible problems with other Excel features.

Display the locations of one or more connections in the workbook

  • Select one or more connections, and then under Locations where connections are used in this workbook, click the link Click here to see where the selected connections are used.

The following information is displayed.

Column

Comment

Sheet

The worksheet where the connection is used.

Name

The Excel query name.

Location

The reference to a cell, range, or object.

Value

The value of a cell, or blank for a range of cells.

Formula

The formula of a cell, or for a range of cells.

Selecting another connection at the top of the dialog box clears the display of the current information.

 Top of Page

Create an Office Data Connection (ODC) file

By using the Connection Properties dialog box or the Data Connection Wizard, you can use Excel to create an Office Data Connection (ODC) file (.odc).

  1. Do one of the following:
  2. Save the connection information to a connection file by clicking Export Connection File on the Definition tab of the Connection Properties dialog box to display the File Save dialog box, and then save the current connection information to an ODC file. For more information, see Connection properties.

 Top of Page

 

See Also

 

 

Excel > Importing data

Remove the data connection from an external data range

Excel 2007

  1. Click the worksheet that contains 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.) from which you want to remove the data connection.
  2. On the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), click the arrow next to the Name box, and then click the name of the external data range from which you want to remove the data connection. The data range is then selected.
  3. On the Tools tab, in the External Table Data group, click Unlink. The data range remains, and still bears the same name, but the connection is deleted.

 

See Also

 

 

Excel > Importing data

Connection properties

Excel 2007

Use the Connection Properties dialog box to control various settings for connections to external data sources, and to use, reuse, or switch connection files.

Important  Connections to external data may be currently disabled on your computer. To connect to data when you open a workbook, you must enable data connections by using the Trust Center bar, or by putting the workbook in a trusted location. For more information, see Create, remove, or change a trusted location for your files, Add, remove, or view a trusted publisher, and View my security settings in the Trust Center

Connection name and Description boxes
These display the connection name and an optional description. To change the name and description, click the text in the box, and then edit the text. The connection name and description are displayed as columns in the Workbook Connections dialog box. (On the Data tab, in the Connections group, click Connections.)

In this article

Usage tab options

Definition tab options

Usage tab options

The settings on the Usage tab control the way that the connection information is used in the workbook.

Refresh control

Enable background refresh  Select this check box to run the query in the background. Clear this check box to run the query while you wait. Running a query in the background enables you to use Excel while the query runs.

Refresh every n minutes  Select this check box to enable automatic external data refresh at regular time intervals, and then enter the number of minutes between each refresh operation. Clear this check box to disable automatic external data refresh.

Refresh data when opening the file  Select this check box to automatically refresh external data when you open the workbook. Clear this check box to immediately open the workbook without refreshing external data.

Remove data from the external data range before saving the workbook  Select this check box if you want to save the workbook with the query definition but without the external data. Clear this check box to save the query definition and data with the workbook. This check box becomes available only after you select the Refresh data when opening the file check box.

OLAP Server Formatting

Controls whether the following OLAP server formats are retrieved from the server and are displayed with the data.

Number Format  Select or clear this check box to enable or disable number formatting, such as currency, date, or time.

Font Style  Select or clear this check box to enable or disable font styles, such as bold, italic, underline, or strike-through.

Fill Color  Select or clear this check box to enable or disable fill colors.

Text Color  Select or clear this check box to enable or disable text colors.

OLAP Drill Through

Maximum number of records to retrieve  Enter a number from 1 to 10,000 to specify the maximum number of records to retrieve when you expand a level of data in a hierarchy.

Language

Retrieve data and errors in the Office display language when available  Select or clear this check box to enable or disable the retrieval of translated data and errors, if any, from the OLAP server.

 Top of Page

Definition tab options

The settings on the Definition tab control how the connection information is defined and the source of the connection information, either the workbook or a connection file.

Connection type  Displays the type of connection that is used, such as Office Data Connection or Microsoft Office Access Database.

Connection file  Displays the current connection file that is used to store the connection information and enables switching to a revised or new connection file. If this field is blank, a connection file was never used, or it was used and then modified so that the link to the connection file was broken.

To re-establish the link to the connection file, for example because it was updated and you want to use the new version, or to change the current connection and use a different connection file, click Browse, which displays the Select Data Source dialog box of the Data Connection Wizard. You can then select the revised connection file, a different connection file, or create a new connection file by clicking New Source.

 Note    Make sure that the new connection file is consistent with the object that has the data connection. You can change the connection, but you cannot switch connections between the following objects:

  • An OLAP PivotTable or PivotChart report
  • A non-OLAP PivotTable or PivotChart report
  • An Excel table
  • A text file
  • An XML table
  • A Web query to a Web page

Always use connection file  Select this check box to ensure that the most up-to-date version of the connection file is always used whenever the data is displayed or refreshed. Clear this check box to use the connection information in the Excel workbook.

Important  If the connection file is not available, Excel resorts to the connection information that is saved in the workbook. If you want to ensure that the most up-to-date version of the connection file is always used, make sure that the connection file is accessible and available.

Connection string  Displays the current connection information in the form of a connection string. Use a connection string to verify all of the connection information and to edit specific connection information that you cannot change through the Connection Properties dialog box.

Save password  Select this check box to save the username and password (password: A way to restrict access to a workbook, worksheet, or part of a worksheet. Excel passwords can be up to 255 letters, numbers, spaces, and symbols. You must type uppercase and lowercase letters correctly when you set and enter passwords.) in the connection file. The saved password is not encrypted. Clear this check box to log on to the data source, if a user name and password are required, the first time that you access it. This check box does not apply to data retrieved from a text file or a Web query.

Security  Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

Command type  Select one of the following command types:

  • SQL
  • Table
  • Default

If the connection is to an OLAP data source, Cube is displayed, but you cannot change it.

Command text   Specifies the data returned based on the command type. For example, if the command type is Table, the table name is displayed. If the command type is SQL, the SQL query used to specify the data returned is displayed. To change the command text, click the text in the box and then edit the text.

Excel Services  Click the Authentication Settings button to display the Excel Services Authentication Settings dialog box and to choose a method of authentication when you access the data source that is connected to a workbook and that is displayed in Excel Services. Select one of the following options to log on to the data source:

  • Windows Authentication  Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can impact performance when there are many users.
  • SSO  Select this option to use Single Sign On, and then enter the appropriate identification string in the SSO ID text box. A site administrator can configure a SharePoint site to use a Single Sign On database where a user user name and password can be stored. This method can be the most efficient when there are many users.
  • None  Select this option to use information saved in the connection string for authentication, for example, when you select the Save Password check box.

Security  Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

 Note    The authentication setting is only used by Excel Services, and not by Microsoft Office Excel. If you want to ensure that the same data is accessed whether you open the workbook in Excel or Excel Services, make sure that the authentication setting in Excel is the same.

Edit Query  Click this button to change the query that is used to retrieve data from the data source. Depending on the type of data source, displays one of the following:

  • The Data Connection Wizard for an Office Data Connection (ODC) file (.odc) OLEDB connection.
  • Microsoft Query for an ODBC connection.
  • The Edit Web Query dialog box for a Web page.
  • The Text Import Wizard for a text file.

 Note    You cannot edit the query if the connection information is currently linked to a connection file.

Parameters   Click this button to display the Parameters dialog box and to edit Parameter information for a Microsoft Query or Web Query connection.

Export Connection File  Click this button to display the File Save dialog box and to save the current connection information to a connection file.

 Top of Page

 

See Also

 

 

Excel > Importing data

Manage external data ranges and their properties

Excel 2007

You can change the properties of an external data range to control data that has been imported into a worksheet through a connection to an external data source.

What do you want to do?

Learn more about external data ranges and properties

Find an external data range

Change the name of an external data range

Change the underlying query for an external data range

Copy an external data range and its underlying query

Specify how new data is added to an external data range

Specify data formatting and layout

Copy formulas when an external data range expands

Freeze data in an external data range

Customize field names in an external data range

Delete an external data range

Learn more about external data ranges and properties

An external data range (also called a query table) is a defined name or table name that defines the location of the data that is brought into a worksheet. When you connect to external data, Excel automatically creates an external data range. The only exception to this is a PivotTable report that is connected to a data source a PivotTable report does not use an external data range. In Excel, you can format and lay out an external data range or use it in calculations, as with any other data.

External data range properties

An external data range has additional properties (not to be confused with connection properties) that you can use to control the data, such as the preservation of cell formatting and column width. You can change these external data range properties by clicking Properties in the Connections group on the Data tab. Then make your changes by doing the following:

  • For external data ranges that are created from Microsoft Query and the Data Connection Wizard, use the External Data Properties dialog box.
  • For external data ranges that are created from an imported text file or Web query that retrieves HTML data, use the External Data Range Properties dialog box.
  • For external data ranges that are created from a Web query that retrieves XML data, use the XML Map Properties dialog box.

Using templates with external data ranges

If you want to share a summary or a report that is based on external data, you can give other users a workbook that contains an external data range, or you can create a report template. A report template (report template: An Excel template (.xlt file) that includes one or more queries or PivotTable reports that are based on external data. When you save a report template, Excel saves the query definition but doesn't store the queried data in the template.) lets you save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template.

 Top of Page

Find an external data range

  1. Select the worksheet in which you want to search for an 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.).
  2. On the formula bar (formula bar: A bar at the top of the Excel window that you use to enter or edit values or formulas in cells or charts. Displays the constant value or formula stored in the active cell.), click the arrow next to the Name Box, and then click the name of the external data range that you want.

 Top of Page

Change the name of an external data range

  1. On the Formulas tab, in the Defined Names group, click Name Manager.

  1. In the Name Manager dialog box, click the name of the external data range, and then click Edit. You can also double-click the name.
  2. In the Edit Name dialog box, type the new name for the reference in the Name box.

 Note    The Close button closes only the Name Manager dialog box. You don't have to click Close in order to commit changes that you already made.

 Top of Page

Change the underlying query for an external data range

You can change the underlying query for an external data range that was created from Microsoft Query, an imported text file, a Web query, or the Data Connection Wizard.

Change the underlying query for an external data range created from Microsoft Query

  1. Click a cell in the external data range for which you want to change the underlying query.
  2. On the Data tab, in the Connections group, click Properties.

  1. In the External Data Properties dialog box, click Connection Properties .
  2. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.

 Note    For more information about constructing and editing queries in Microsoft Query, see Microsoft Query Help.

Change the underlying query for an external data range created from an imported text file

  1. Click a cell in the external data range for which you want to change the underlying query.
  2. On the Data tab, in the Connections group, click Connections.

  1. In the Workbook Connections dialog box, click Properties.
  2. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.
  3. In the Import Text File dialog box, click Import.
  4. Make changes to the imported text file in the Text Import Wizard, and then click Finish.

For more information about importing text files, see Import or export text files.

Change the underlying query for an external data range created from a Web query

  1. Click a cell in the external data range for which you want to change the underlying query.
  2. On the Data tab, in the Connections group, click Properties.

  1. In the Workbook Connections dialog box, click Properties.
  2. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.
  3. Make changes to the Web query in the Edit Web Query dialog box, and then click Finish.

 Note    For more information about creating and editing Web queries, see Get external data from a Web page.

Change the underlying query for an external data range created by using the Data Connection Wizard

  1. Click a cell in the external data range for which you want to change the underlying query.
  2. On the Data tab, in the Connections group, click Properties.

  1. In the External Data Properties dialog box, click Connection Properties .
  2. In the Connection Properties dialog box, click the Definition tab.
  3. Do one of the following:
    • In the Command type box, click Table and then, in the Command text box, change the value to the name of an appropriate table, view, or query.
    • In the Command type box, click SQL or Default and then, in the Command text box, edit the SQL statement.

 Note    Depending on how the connection was defined, the Command type box may be unavailable (it appears dimmed).

 Top of Page

Copy an external data range and its underlying query

  1. On the formula bar, click the arrow next to the Name Box, and select the name of the external data range that you want to copy.

For an Excel table, select the name of the range, and then press CTRL+A to select the table headers.

If you want to include column labels or formulas that are not part of the external data range, select the cells that contain the column labels or formulas that you want to copy. Click the arrow next to the Name Box on the formula bar, and click the name of the external data range that you want to copy.

  1. On the Home tab, in the Clipboard group, click Copy.

  1. Switch to the workbook in which you want to paste the external data range.
  2. Click the upper-left cell of the paste area (paste area: The target destination for data that's been cut or copied by using the Office Clipboard.).

To ensure that the 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.) does not replace existing data, make sure that the worksheet has no data under or to the right of the cell that you click.

  1. On the Home tab, in the Clipboard group, click Paste.

 Note    If you copy only part of an external data range, the underlying 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.) is not copied, and the copied data can't be refreshed (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.).

 Top of Page

Specify how new data is added to an external data range

You can control how to handle a smaller or larger data set that is returned to Excel when data is refreshed.

  1. On the Data tab, in the Connections group, click Properties.

  1. In the External Data Range Properties dialog box, under If the number of rows in the data range changes upon refresh, click one of the following:
    • Insert cells for new data, delete unused cells

 Notes 

      • When one or more rows are added in the data source, cells directly under the external data range move down, but cells to the right of the external data range do not move.
      • When one or more rows are deleted in the data source, cells directly under the external data range move up, but cells to the right of the external data range do not move.
    • Insert entire rows for new data, clear unused cells

 Notes 

      • When one or more rows are added in the data source, cells directly under and to the right of the external data range move down.
      • When one or more rows are deleted in the data source, cells directly under and to the right of the external data range do not move.
    • Overwrite existing cells with new data, clear unused cells

 Notes 

      • When one or more rows are added in the data source, cells directly under the external data range are overwritten, but cells to the right of the external data range do not move.
      • When one or more rows are deleted in the data source, cells directly under and to the right of the external data range do not move.

 Top of Page

Specify data formatting and layout

  1. On the Data tab, in the Connections group, click Properties.

  1. Under Data formatting and layout, do one or more of the following:
    • To include field names as the first row, select the Include field names check box.

 Note    This check box is only available for an external data range that is converted from a Microsoft Office 2003 program, or to an external data range that is created programmatically.

    • To add a column of row numbers, select the Include row numbers check box.

 Note    This check box is not available for an imported text file, XML file, or Web query.

    • To preserve the cell formatting that you apply, select the Preserve cell formatting check box.

 Note    For a Web query, this option is automatically cleared when you select Full HTML Formatting in the Options dialog box. You access the Options dialog box from the Edit Query dialog box.

    • To preserve the column widths that you set, select the Adjust column width check box.
    • To preserve the column sorting, filtering, and layout that you apply, select the Preserve column sort/filter/layout check box.

 Note    This check box is not available for a Web query.

    • To preserve the column filtering that you apply, select the Preserve column filter check box.

 Note    This check box is available only for a Web query that is based on XML data.

 Top of Page

Copy formulas when an external data range expands

 Note    The following procedure applies only to an external data range that is converted from a Microsoft Office 2003 program, or to an external data range that is created programmatically. In Office Excel 2007, all external data ranges that are created by using the user interface are created as Excel tables. These tables automatically expand when new rows are added and fill down calculated columns.

  1. Enter a formula in a cell that is adjacent to the first row of data in the external data range.

 Note    The first row of data may be the first or second row in the external data range, depending on whether the first row contains headers.

  1. Select the cell and double-click the fill handle (fill handle: The small black square in the lower-right corner of the selection. When you point to the fill handle, the pointer changes to a black cross.) to copy the formula to all rows in the external data range.
  2. Click a cell in the external data range.
  3. On the Data tab, in the Connections group, click Properties.

  1. In the External Data Range Properties dialog box, select the Fill down formulas in columns adjacent to data check box.

 Note    If the external data range expands when you refresh (refresh: To update the contents of a PivotTable or PivotChart report to reflect changes to the underlying source data. If the report is based on external data, refreshing runs the underlying query to retrieve new or changed data.), Excel copies only those formulas that are immediately adjacent to or within the external data range.

 Top of Page

Freeze data in an external data range

Freezing an external data range retains the data but not its underlying query, so a frozen external data range cannot be refreshed.

Freeze data in an Excel table

  1. Click the worksheet that contains the Excel table from which you want to remove the data connection.
  2. On the formula bar, click the arrow next to the Name Box, and then click the name of the external data range from which you want to remove the data connection. The data range is then selected.
  3. On the Tools tab, in the External Table Data group, click Unlink. The data range remains and still bears the same name, but the connection is deleted.

Freeze data in a range

 Note    The following procedure applies only to an external data range that is converted from a Microsoft Office 2003 program, or to an external data range that is created programmatically. In Office Excel 2007, all external data ranges that are created by using the user interface are created as Excel tables. These tables automatically expand when new rows are added and fill down calculated columns.

  1. On the Data tab, in the Connections group, click Properties.

  1. Under Query definition, clear the Save query definition check box.

 Note     When you save your workbook, the underlying query is deleted from the workbook. However, the saved database query files (.dqy or .odc files) are not deleted. If you saved the query when you created it in the Query Wizard or in Microsoft Query, the query file is saved on your computer, and you can use the query again to retrieve external data. For more information, see Microsoft Query Help.

 Top of Page

Customize field names in an external data range

 Note    The following procedure applies only to an external data range that is created by using the Data Connection Wizard or Microsoft Query (and not to an imported text file or Web query), an external data range converted from a Microsoft Office 2003 program, or an external data range created programmatically. In Office Excel 2007, all external data ranges that are created by using the user interface are created as Excel tables.

  1. Insert a blank row above the external data range.

How to insert a blank row

    1. Do one of the following:
      • Insert new blank cells  Select a range (range: Two or more cells on a sheet. The cells in a range can be adjacent or nonadjacent.) of cells where you want to insert the new blank cells. Select the same number of cells as you want to insert.
      • Insert a single row  Click a cell in the row immediately under where you want the new row. For example, to insert a new row above row 5, click a cell in row 5.
      • Insert multiple rows  Select rows immediately under where you want the new rows. Select the same number of rows as you want to insert.
      • Insert a single column  Click a cell in the column immediately to the right of where you want to insert the new column. For example, to insert a new column to the left of column B, click a cell in column B.
      • Insert multiple columns  Select columns immediately to the right of where you want to insert the new columns. Select the same number of columns as you want to insert.
    2. On the Home tab, in the Cells group, click the arrow next to Insert, and then click one of the following: Insert Cells, Insert Sheet Rows, or Insert Sheet Columns.

If you are inserting blank cells, click Shift cells right or Shift cells down to define where you want the existing cells to move.

  1. Type the labels that you want in the cells in the blank row.
  2. Click a cell in the external data range.
  3. On the Data tab, in the Connections group, click Properties.

  1. In the External Data Range Properties dialog box, under Data formatting and layout, clear the Include field names check box, and then click OK.
  2. To remove the existing field names and refresh the external data range, click a cell in the external data range, and then click Refresh Data .

 Note    When you retrieve data from a database, any changes to column names in Microsoft Query are retained in the external data range. For information about changing column names in the query, see Microsoft Query Help.

 Top of Page

Delete an external data range

  1. Click the worksheet that contains the external data range that you want to delete.
  2. On the formula bar, click the arrow next to the Name Box, and then click the name of the external data range that you want to delete.

If the external data range is an Excel table, press CTRL+A to select the entire table.

  1. To delete the external data range, press DELETE.
  2. To delete the underlying query, click Yes when Excel prompts you.

 Top of Page

 

See Also

 

 

Excel > Importing data

Connect to (import) SQL Server data

You can use an Office Data Connection (.odc) file to connect to a Microsoft SQL Server database from a Microsoft Office Excel 2007 file. SQL Server is a full-featured, relational database program that is designed for enterprise-wide data solutions that require optimum performance, availability, scalability, and security.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From SQL Server.

The Data Connection Wizard starts. This wizard has three pages.

Page 1: Connect to Database Server  

  1. In step 1, type the name of the SQL Server computer in the Server name box.
  2. In step 2, under Log on credentials, do one of the following:
    • To use your current Microsoft Windows user name and password, click Use Windows Authentication.
    • To enter a database user name and password, click Use the following User Name and Password, and then type your user name and password in the corresponding User Name and Password boxes.

Security  

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

Page 2: Select Database and Table  

  1. Under Select the database that contains the data you want, select a database. Under Connect to a specific table, select a specific table or view.

Alternatively, you can clear the Connect to a specific table check box, so that other users who use this connection file will be prompted for the list of tables and views.

Page 3: Save Data Connection File and Finish  

  1. Optionally, in the File Name box, revise the suggested file name. Click Browse to change the default file location (My Data Sources).
  2. Optionally, type a description of the file, a friendly name, and common search words in the Description, Friendly Name, and Search Keywords boxes.
  3. To ensure that the connection file is always used when the data is updated, click the Always attempt to use this file to refresh this data check box. This check box ensures that updates to the connection file will always be used by all workbooks that use that connection file.
  4. To specify how the external data source of a PivotTable report is accessed if the workbook is saved to Excel Services and is opened by using Excel Services, click Authentication Settings, and then select one of the following options to log on to the data source:
    • Windows Authentication  Select this option to use the Windows user name and password of the current user. This is the most secure method, but it can affect performance when many users are connected to the server.
    • SSO  Select this option to use Single Sign On (SSO), and then enter the appropriate identification string in the SSO ID box. A site administrator can configure a site to use a Single Sign On database in which a user name and password can be stored. This method can be the most efficient when many users are connected to the server.
    • None  Select this option to save the user name and password in the connection file.

Security  Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

  1.  Note    The authentication setting is used only by Excel Services, and not by Excel.
  2. Click OK.
  3. Click Finish to close the Data Connection Wizard.

The Import Data dialog box is displayed.

  1. Under Select how you want to view this data in your workbook, do one of the following:
    • To create an Excel table, click Table (this is the default).
    • To create a PivotTable report, click PivotTable Report.
    • To create a PivotChart and PivotTable report, click PivotChart and PivotTable Report.

 Note    The Only Create Connection option is available only for an OLAP database.

  1. Under Where do you want to put the data?, do one of the following:
    • To place the data in an existing worksheet, select Existing worksheet, and then type the name of the first cell in the range of cells where you want to locate the data.

Alternatively, click Collapse Dialog to temporarily collapse the dialog box, select the beginning cell on the worksheet, and then click Expand Dialog .

    • To place the data in a new worksheet starting at cell A1, click New worksheet.
  1. Optionally, you can change the connection properties (and also change the connection file) by clicking Properties, making your changes in the Connection Properties dialog box, and then clicking OK.

For more information, see Connection properties.

 

See Also

 

 

Excel > Importing data

Get external data from a Web page

Excel 2007

You can create or run a Web query (Web query: A query that retrieves data stored on your intranet or the Internet.) to retrieve text or data from a Web page. Web pages often contain information that is perfect for analysis in Excel. Depending on your needs, you can retrieve data that is refreshable. That is, you can update the data in Excel with the latest data on the Web page. Or you can retrieve data from a Web page and keep it static on the worksheet.

What do you want to do?

Learn more about getting external data from a Web page

Create and edit a Web query

Copy data from a Web page

Run a saved Web query file

Make a Web page redirect a Web query to another data source

Issue: I am having problems sorting, filtering, or searching external data from a Web query

Learn more about getting external data from a Web page

You can use a Web query to retrieve refreshable data that is stored on your intranet or the Internet, such as a single table, multiple tables, or all of the text on a Web page. Then you can analyze the data by using the tools and features in Excel. For example, you can retrieve and update stock quotes from a public Web page or retrieve and update a table of sales information from a company Web page.

Web queries are especially useful for retrieving data that is in tables or preformatted areas. (Tables are defined with the HTML (HTML: The standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.)

tag. Preformatted areas are often defined with the HTML
 tag.) The retrieved data does not include pictures, such as .gif images, and does not include the contents of scripts.

 

To create a Web query, you need 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 files.

Sample Web queries

Several sample Web queries are installed with Microsoft Office Excel 2007. By default, these queries are located in the \Program Files\Microsoft Office\Office12\QUERIES folder on your hard disk:

  • MSN MoneyCentral Investor Major Indicies.iqy
  • MSN MoneyCentral Investor Currency Rates.iqy
  • MSN MoneyCentral Investor Stock Quotes.iqy (a Web query parameter query)

Ways to get external data

If you are using Excel, you can import data that originates from a Web page by using the New Web Query dialog box. (On the Data tab, in the Get External Data group, click From Web.)

If you are using Internet Explorer, there are two ways to get external data into Office Excel:

  • Use familiar copy and paste commands to bring the data from a Web page onto an Excel worksheet. When you paste Web page data into Excel, you can keep the data static or make it refreshable by clicking Paste Options and then clicking Create Refreshable Web Query.
  • Right-click the Web page, and then click Export to Microsoft Excel on the shortcut menu. This displays the New Web Query dialog box.

 Note    You can also open any HTML (HTML: The standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.) file, MHTML (Single File Web Page (MHTML): An HTML document saved in MHTML format, which integrates inline graphics, applets, linked documents, and other supporting items referenced in the document.) file, or well-formed XML (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) file in Excel. When you open an HTML or MHTML file, you get the entire Web page, but you might lose some formatting, scripts, image files (HTML only), or lists of data in a single cell. When you open an XML file, you have the option of applying one or more referenced stylesheets (XML stylesheet: Contains formatting rules which are applied to an XML file that references the stylesheet. The standard set of rules for XML stylesheets is the Extensible Style Language (XSL).). In either case, the data is not refreshable.

Redirecting Web queries to other data sources

If you are a Web page author, you can increase the reliability of the data that users retrieve from your pages by having Web queries (Web query: A query that retrieves data stored on your intranet or the Internet.) from Excel redirected to a data source such as an XML (Extensible Markup Language (XML): A condensed form of Standard Generalized Markup Language (SGML) that enables developers to create customized tags that offer flexibility in organizing and presenting information.) or text file.

For example, if an HTML page contains a table of stock quotes, that table data can be used in a Web query, even if the data is being pulled from another data source. Or, if you are pointing to a table on a Microsoft Windows SharePoint Services site that summarizes the project status, you can create a Web query to pull data from the master status list.

This redirection has two primary advantages. First, the HTML Web page that you are importing can be formatted for viewing, even though the data is optimized for analysis (for example, in XML format). Second, the HTML view of the data can be broken up into pages of data (for example, 20 results at a time), but the Web query can be redirected to the full set of data.

You can use an HTML attribute with the TABLE, PRE, XMP, LISTING, or PLAINTEXT tag that identifies where the underlying data is stored. Web page authors can use this redirection attribute, o:WebQuerySourceHRef, to list the data source URL (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.), so that Web queries can link to the underlying source data. This redirection attribute is defined in the Microsoft Office namespace to avoid conflict with other redirection attributes. The data source can be any supported Web query data type, such as HTML, Text, or XML. When this attribute is used, you do not have to worry about getting the correct content when you refresh. The Web query contains information about the path to the data source and can pull the updated data directly from that source.

 Top of Page

Create and edit a Web query

  1. Do one of the following to either create a new Web query or edit an existing Web query:

Create a new Web query  

    1. Do either of the following:
      • In Office Excel, on the Data tab, in the Get External Data group, click From Web.
      • In the browser, browse to the Web page from which you want to query the data, right-click the Web page, and then click Export to Microsoft Excel on the shortcut menu.

The New Web Query dialog box appears.

    1. In the New Web Query dialog box, enter the URL (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.) for the Web page from which you want to get data. You can type the URL, paste it from a copied address, or click the arrow next to the Address list and select a recently used address.

 Note    The maximum length of a URL is 255 characters.

    1. Click Go.

Edit an existing Web query  

    1. On the Data tab, in the Connections group, click Connections.

    1. In the Workbook Connections dialog box, select the Web query, and then click Properties.
    2. In the Connection Properties dialog box, click the Definition tab, and then click Edit Query.
  1. Click the button next to the tables that you want to import, or click the button in the upper-left corner of the page to import the entire page.

Tip  If there are no buttons next to the tables on the page, click Show Icons at the top of the dialog box to display them.

  1. To set the formatting and import options for how data is returned, click Options, and then do one or more of the following in the Web Query Options dialog box:
    1. Under Formatting, click one of the following:
    2. Under Import settings for preformatted
       blocks, select one or more of the following: 
              
      • Import
         blocks into columns   Blocks of data surrounded by 
         tags will be imported into separate columns on the worksheet. Clear this check box to return data into a single column.
      • Treat consecutive delimiters as one  If you have characters that define, or delimit, the text for each column in your
         sections (such as commas), you can specify that when Excel encounters more than one of these delimiters together, they will be treated as one. As a result, a blank column is not placed between each consecutive delimiter. This check box is available only when you select the Import 
         blocks into columns check box.
      • Use the same import settings for the entire section  Select this check box to use your setting for Treat consecutive delimiters as one for all preformatted sections on the Web page. Clear this check box to use your setting for the first preformatted section only, or if you want Excel to determine the best settings. This check box is available only when you select the Import
         blocks into columns check box.
    3. Under Other Import settings, select one or more of the following:
      • Disable date recognition  This option ensures that numbers on a Web page that appear similar to dates appear as numbers on the worksheet. For example, a sports standing score of 03-07 is recognized by Excel as the date March 7 unless this option is selected.
      • Disable Web query redirections  This option ensures that the Web query is not redirected to a different data source from what you see on the Web page that you are querying. Select this check box for compatibility with queries that are created in previous versions of Excel.
  2. Click OK. The Web query is saved with your workbook.
  3. Optionally, save the Web query to a Web query file (.iqy) so that you can run the query in other workbooks and so that you can create a parameter query.

For more information, see Create a Web query parameter query.

Save a Web query to a Web query file

    1. In the Edit Web Query dialog box, click Save Query .
    2. Locate the folder in which you want to save the query.
    3. Enter a file name in the File name box.
    4. Click Save.

The query is saved in a text file with an .iqy file name extension.

  1. Click Import.

In the Import Data dialog box, do one of the following:

    1. To return the data from the Web page to the selected worksheet, click Existing worksheet. On your worksheet, click the cell where you want to place the upper-left corner of 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.), and then click OK.
    2. To return the data to a new worksheet, click New worksheet, and then click OK. Excel adds a new worksheet to your workbook and automatically starts the external data range in the upper-left corner of the new worksheet.

After you click OK, a Background Refresh icon appears on the status bar (status bar: A horizontal bar at the bottom of the screen that displays information about the current condition of the program, such as the status of items in the window, the progress of the current task, or information about the selected item.) to indicate that the query is running. To check the status of the query, double-click the refresh icon.

 Top of Page

Copy data from a Web page

  1. In your Web browser (Web browser: Software that interprets HTML files, formats them into Web pages, and displays them. A Web browser, such as Windows Internet Explorer, can follow hyperlinks, transfer files, and play sound or video files that are embedded in Web pages.), select the data that you want to copy.
  2. Copy the data.

 Note    See your browser's Help for information about a copy operation.

  1. Switch to Excel.
  2. Click in the upper-left corner of the worksheet area where you want the copied data to appear.
  3. On the Home tab, in the Clipboard group, click Paste.

  1. If the data does not appear as you expect, click Paste Options and then click one of the following options:
    • Keep Source Formatting to make no changes.
    • Match Destination Formatting to match the existing cell formatting.
    • Create Refreshable Web Query to create a query to the Web page from which you copied the data. If the Web page changes at a later time, you can refresh the data.

 Top of Page

Run a saved Web query file

Web queries are saved in text files with .iqy file name extensions. The saved queries are useful for sharing the same Web query in different workbooks or with other users and for creating a Web query parameter query.

  1. Click the Microsoft Office Button , and then click Open.
  2. In the Look in box, locate the folder where your query file is stored.
  3. Select the Web query that you want to run.
  4. Click Open.
  5. In the Import Data dialog box, do one of the following:
  6. If the Web query is a parameter query, the Enter Parameter Value dialog box prompts you to enter parameters, unless you already set the parameter query to return a specific value.

To edit the settings for the parameters, click Parameters in the Import Data dialog box in step 5.

For more information, see Customize a parameter query.

Excel runs the query. A Background Refresh icon appears on the status bar (status bar: A horizontal bar at the bottom of the screen that displays information about the current condition of the program, such as the status of items in the window, the progress of the current task, or information about the selected item.) to indicate that the query is running. To check the status of the query, double-click the refresh icon.

 Top of Page

Make a Web page redirect a Web query to another data source

  1. Include the Microsoft Office namespace declaration in the opening HTML (HTML: The standard markup language used for documents on the World Wide Web. HTML uses tags to indicate how Web browsers should display page elements such as text and graphics and how to respond to user actions.) tag of your Web page:

 

 

where the value is a URL (Uniform Resource Locator (URL): An address that specifies a protocol (such as HTTP or FTP) and a location of an object, document, World Wide Web page, or other destination on the Internet or an intranet, for example: http://www.microsoft.com/.) (relative or absolute) to which you want to redirect your Web query.

 Note     Users can bypass redirection on Web pages by selecting the Disable Web query redirections check box in the Web Query Options dialog box. You can access this from the New Web Query dialog box.

 Top of Page

Issue: I am having problems sorting, filtering, or searching external data from a Web query

External data from a Web query may contain leading, trailing, or multiple embedded Unicode (Unicode: A character encoding standard developed by the Unicode Consortium. By using more than one byte to represent each character, Unicode enables almost all of the written languages in the world to be represented by using a single character set.) space characters (value 160). These space characters can sometimes cause unexpected results when you sort or filter data, or perform a search operation.

For more information about how to handle these and other characters, see Remove spaces and nonprinting characters from text.

 Top of Page

 

 

Excel > Importing data

Exchange (copy, import, export) data between Excel and Access

Excel 2007

There are several ways to exchange data between Microsoft Office Access and Microsoft Office Excel.

  • To bring data into Excel from Access, you can copy data from an Access datasheet and paste it into an Excel worksheet, connect to an Access database from an Excel worksheet, or export Access data into an Excel worksheet.
  • To bring data into Access from Excel, you can copy data from an Excel worksheet and paste it into an Access datasheet, import an Excel worksheet into an Access table, or link to an Excel worksheet from an Access table.

 Notes 

  • The word "import" has two different meanings between Excel and Access. In Excel, when you import, you make a permanent connection to data that can be refreshed. In Access, when you import, you bring data into Access once, but without a permanent data connection.
  • You cannot save an Excel workbook as an Access database. Neither Excel nor Access provides functionality to create an Access database from Excel data.

What do you want to do?

Work with Access data in Excel

If your data is in Access and you want to work with the data in Excel:

 

If the delimiter character occurs between text qualifiers, Excel omits the qualifiers in the imported value. If no delimiter character occurs between text qualifiers, Excel includes the qualifier character in the imported value. Hence, "Dallas Texas" (using the quotation mark text qualifier) is imported into one cell as "Dallas Texas".

Data preview  Review the text in this box to verify that the text will be separated into columns on the worksheet as you want it.

Step 2 of 3 (Fixed width data)

Data preview  Set field widths in this section. Click the preview window to set a column break, which is represented by a vertical line. Double-click a column break to remove it, or drag a column break to move it.

Step 3 of 3

Click the Advanced button to do one or more of the following:

  • Specify the type of decimal and thousands separators that are used in the text file. When the data is imported into Excel, the separators will match those that are specified for your country/region in Regional Settings (Windows Control Panel).
  • Specify that one or more numeric values may contain a trailing minus sign.

Column data format  Click the data format of the column that is selected in the Data preview section. If you do not want to import the selected column, click Do not import column (skip).

After you select a data format option for the selected column, the column heading under Data preview displays the format. If you select Date, select a date format in the Date box.

Choose the data format that closely matches the preview data so that Excel can convert the imported data correctly. For example:

  • To convert a column of all currency number characters to the Excel Currency format, select General.
  • To convert a column of all number characters to the Excel Text format, select Text.
  • To convert a column of all date characters, each date in the order of year, month, and day, to the Excel Date format, select Date, and then select the date type of YMD in the Date box.

Excel will import the column as General if the conversion could yield unintended results. For example:

  • If the column contains a mix of formats, such as alphabetical and numeric characters, Excel converts the column to General.
  • If, in a column of dates, each date is in the order of year, month, and date, and you select Date along with a date type of MDY, Excel converts the column to General format. A column that contains date characters must closely match an Excel built-in date or custom date formats.

If Excel does not convert a column to the format that you want, you can convert the data after you import it. For more information, see the following Help topics:

 

See Also

 

 

Excel > Importing data

Use Microsoft Query to retrieve external data

Excel 2007

You can use Microsoft Query to retrieve data from external sources. By using Microsoft Query to retrieve data from your corporate databases (database: A collection of data related to a particular subject or purpose. Within a database, information about a particular entity, such as an employee or order, is categorized into tables, records, and fields.) and files, you don't have to retype the data that you want to analyze in Excel. You can also refresh your Excel reports and summaries automatically from the original source database whenever the database is updated with new information.

What do you want to do?

Learn more about Microsoft Query

Connect to a data source

Use the Query Wizard to define a query

Work with the data in Excel

Learn more about Microsoft Query

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

Types of databases that you can access   You can retrieve data from several 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 from text files.

Microsoft Office 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 also 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 retrieve information from data sources that are not 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 the documentation for the database, or contact your database vendor.

Selecting data from a database   You retrieve data from a database by creating a query, which is a question that you ask 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 specific product by region. You can retrieve a part of the data by selecting only the data for the product and region that you want to analyze.

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

Updating your worksheet in one operation   Once you have external data in an Excel workbook, whenever your database changes, you can refresh (refresh: To update data from an external data source. Each time you refresh data, you see the most recent version of the information in the database, including any changes that were made to the data.) the data to update your analysis without having to re-create your summary reports and charts. For example, you can create a monthly sales summary and refresh it every month when the new sales figures come in.

How Microsoft Query uses data sources   After you set up a data source for a particular database, you can use it whenever you want to create a query to select and retrieve data from that database without having to retype all of the connection information. Microsoft Query uses the data source to connect to the external database and to show you what data is available. After you create your query and return the data to Excel, Microsoft Query provides the Excel workbook with both the query and data source information so that you can reconnect to the database when you want to refresh the data.

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

  1. Connect to a data source.
  2. Use the Query Wizard to define a query.
  3. Work with the data in Excel.

 Top of Page

Connect to a data source

What is a data source?   A data source is a stored set of information that allows Excel and Microsoft Query to connect to an external database. When you use Microsoft Query to set up a data source, you give the data source a name, and then supply the name and the location of the database or server, the type of database, and your logon and password information. The information also includes the name of an OBDC driver or a data source driver, which is a program that makes connections to a specific type of database.

To set up a data source by using Microsoft Query:

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.
  2. Do one of the following:
  3. Double-click .

-or-

Click , and then click OK.

The Create New Data Source dialog box is displayed.

  1. In step 1, type a name to identify the data source.
  2. In step 2, click a driver for the type of database that you are using as your data source.

If the external database that you want to access is not supported by the ODBC drivers that are installed with Microsoft Query, then you need to obtain and install a Microsoft Office-compatible ODBC driver from a third-party vendor, such as the manufacturer of the database. Contact the database vendor for installation instructions.

 Note    OLAP databases do not require ODBC drivers. When you install Microsoft Query, drivers are installed for databases that were created by using Microsoft SQL Server Analysis Services. To connect to other OLAP databases, you need to install a data source driver and client software.

  1. Click Connect, and then provide the information that is needed to connect to your data source. For databases, Excel workbooks, and text files, the information that you provide depends on the type of data source that you selected. You may be asked to supply a logon name, a password, the version of the database that you are using, the database location, or other information specific to the type of database.

Use strong passwords that combine uppercase and lowercase letters, numbers, and symbols. Weak passwords don't mix these elements. Strong password: Y6dh!et5. Weak password: House27. Passwords should be 8 or more characters in length. A pass phrase that uses 14 or more characters is better. For more information, see Help protect your personal information with strong passwords.

It is critical that you remember your password. If you forget your password, Microsoft cannot retrieve it. Store the passwords that you write down in a secure place away from the information that they help protect.

  1. After you enter the required information, click OK or Finish to return to the Create New Data Source dialog box.
  2. If your database has tables (table: A collection of data about a particular subject that is stored in records (rows) and fields (columns).) and you want a particular table to display automatically in the Query Wizard, click the box for step 4, and then click the table that you want.
  3. If you don't want to type your logon name and password when you use the data source, select the Save my user ID and password in the data source definition check box. The saved password is not encrypted. If the check box is unavailable, see your database administrator to determine whether this option can be made available.

Security  Avoid saving logon information when connecting to data sources. This information may be stored as plain text, and a malicious user could access the information to compromise the security of the data source.

After you complete these steps, the name of your data source appears in the Choose Data Source dialog box.

 Top of Page

Use the Query Wizard to define a query

Use the Query Wizard for most queries   The Query Wizard makes it easy to select and bring together data from different tables and fields in your database. Using the Query Wizard, you can select the tables and fields that you want to include. An inner join (a query operation that specifies that rows from two tables are combined based on identical field values) is created automatically when the wizard recognizes a primary key field in one table and a field with the same name in a second table.

You can also use the wizard to sort the result set and to do simple filtering. In the final step of the wizard, you can choose to return the data to Excel, or further refine the query in Microsoft Query. After you create the query, you can run it in either Excel or in Microsoft Query.

To start the Query Wizard, perform the following steps.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.
  2. In the Choose Data Source dialog box, make sure that the Use the Query Wizard to create/edit queries check box is selected.
  3. Double-click the data source that you want to use.

-or-

Click the data source that you want to use, and then click OK.

Work directly in Microsoft Query for other types of queries   If you want to create a more complex query than the Query Wizard allows, you can work directly in Microsoft Query. You can use Microsoft Query to view and to change queries that you start creating in the Query Wizard, or you can create new queries without using the wizard. Work directly in Microsoft Query when you want to create queries that do the following:

  • Select specific data from a field   In a large database, you might want to choose some of the data in a field and omit data that you don't need. For example, if you need data for two of the products in a field that contains information for many products, you can use criteria (criteria: Conditions you specify to limit which records are included in the result set of a query or filter.) to select data for only the two products that you want.
  • Retrieve data based on different criteria each time you run the query   If you need to create the same Excel report or summary for several areas in the same external data such as a separate sales report for each region you can create a parameter query (parameter query: A type of query that, when you run it, prompts for values (criteria) to use to select the records for the result set so that the same query can be used to retrieve different result sets.). When you run a parameter query, you are prompted for a value to use as the criterion when the query selects records. For example, a parameter query might prompt you to enter a specific region, and you could reuse this query to create each of your regional sales reports.
  • Join data in different ways   The inner joins that the Query Wizard creates are the most common type of join used in creating queries. Sometimes, however, you want to use a different type of join. For example, if you have a table of product sales information and a table of customer information, an inner join (the type created by the Query Wizard) will prevent the retrieval of customer records for customers who have not made a purchase. Using Microsoft Query, you can join these tables so that all the customer records are retrieved, along with sales data for those customers who have made purchases.

To start Microsoft Query, perform the following steps.

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query.
  2. In the Choose Data Source dialog box, make sure that the Use the Query Wizard to create/edit queries check box is clear.
  3. Double-click the data source that you want to use.

-or-

Click the data source that you want to use, and then click OK.

Reusing and sharing queries   In both the Query Wizard and Microsoft Query, you can save your queries as a .dqy file that you can modify, reuse, and share. Excel can open .dqy files directly, which allows you or other users to create additional external data ranges from the same query.

To open a saved query from Excel:

  1. On the Data tab, in the Get External Data group, click From Other Sources, and then click From Microsoft Query. The Choose Data Source dialog box is displayed.
  2. In the Choose Data Source dialog box, click the Queries tab.
  3. Double-click the saved query that you want to open. The query is displayed in Microsoft Query.

If you want to open a saved query and Microsoft Query is already open, click the Microsoft Query File menu, and then click Open.

If you double-click a .dqy file, Excel opens, runs the query, and then inserts the results into a new worksheet.

If you want to share an Excel summary or report that is based on external data, you can give other users a workbook that contains an external data range, or you can create a template (template: A workbook that you create and use as the basis for other similar workbooks. You can create templates for workbooks and worksheets. The default template for workbooks is called Book.xlt. The default template for worksheets is called Sheet.xlt.). A template allows you to save the summary or report without saving the external data so that the file is smaller. The external data is retrieved when a user opens the report template.

 Top of Page

Work with the data in Excel

After you create a query in either the Query Wizard or Microsoft Query, you can return the data to an Excel worksheet. The data then becomes an 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.) or a PivotTable report (PivotTable report: An interactive, crosstabulated Excel report that summarizes and analyzes data, such as database records, from various sources including ones external to Excel.) that you can format and refresh.

Formatting retrieved data   In Excel, you can use tools, such as charts or automatic subtotals, to present and to summarize the data retrieved by Microsoft Query. You can format the data, and your formatting is retained when you refresh the external data. You can use your own column labels instead of the field names, and add row numbers automatically.

Excel can automatically format new data that you type at the end of a range to match the preceding rows. Excel can also automatically copy formulas that have been repeated in the preceding rows and extends them to additional rows.

 Note    In order to be extended to new rows in the range, the formats and formulas must appear in at least three of the five preceding rows.

You can turn on this option (or off again) at any time:

  1. Click the Microsoft Office Button , click Excel Options, and then click the Advanced category.
  2. In the Edit section, select the Extend data range formats and formulas check box to turn off this option. To turn off automatic data range formatting again, clear this check box.

Refreshing external data   When you refresh external data, you run the query to retrieve any new or changed data that matches your specifications. You can refresh a query in both Microsoft Query and Excel. Excel provides several options for refreshing queries, including refreshing the data whenever you open the workbook and automatically refreshing it at timed intervals. You can continue to work in Excel while data is being refreshed, and you can also check the status while the data is being refreshed. For more information, see Refresh connected (imported) data.

 Top of Page

 

See Also

 

 

Excel > Importing data

Import data from a SharePoint list

Excel 2007

You can import and analyze data from a list on a Microsoft Windows SharePoint Services 3.0 or Windows SharePoint Services 2.0 site. For example, you can import a parts inventory list from a SharePoint site and then create a PivotChart report (PivotChart report: A chart that provides interactive analysis of data, like a PivotTable report. You can change views of data, see different levels of detail, or reorganize the chart layout by dragging fields and by showing or hiding items in fields.) in Microsoft Office Excel 2007 to summarize and compare the data.

In this article

Overview

Import data from a SharePoint list

Update data from a SharePoint list

Overview

To import data from a SharePoint site, you must have Office Excel 2007 installed on the computer where you are working with the SharePoint list.

When you import data from a SharePoint site to Office Excel 2007, you can analyze it in Office Excel 2007, but any changes that you make in Excel are not reflected in the list on the SharePoint site.

To use an Office program to manipulate data from a list on a SharePoint site and have those changes reflected on the SharePoint site, you can use Microsoft Office Access 2007 or a solution that uses Visual Basic for Applications (VBA).

You can also manipulate data from a SharePoint list in a workbook that is saved as an Excel 97-2003 workbook (.xls format). However, if you save an Excel 2003 workbook as an Office Excel 2007 workbook (.xlsx format), only a read-only connection is available from the SharePoint list.

As the list continues to be updated on the site, you can update your workbook with the latest data from the SharePoint site. When you update your data from the SharePoint site, the original data from the SharePoint site and any changes that you made in Office Excel 2007 are overwritten.

In Office Excel 2007, you can import data into an existing worksheet or create a new worksheet. If you have folders in your list on the SharePoint site, the structure does not appear on the resulting Excel worksheet.

When you import data from a SharePoint list with a read-only connection to the list, the Item Type and Path columns are added to the data on the worksheet. These columns enable you to filter and sort the data, based on its type and location on the site  for example, if it was located in a subfolder of the list.

To import data from a list on a SharePoint site, you must have a connection to the SharePoint site and permission to read the list.

 Top of Page

Import data from a SharePoint list

You can import data from lists on a Windows SharePoint Services 3.0 or Windows SharePoint Services 2.0 site. When you import data from a SharePoint list, some steps differ, depending on which version is running on your SharePoint site.

How to tell which version is running on the SharePoint site

  • On a Windows SharePoint Services 3.0 site, a menu with your user name or account may appear in the upper-right corner.
  • On a Windows SharePoint Services 2.0 site, the top-level navigation in the upper-left corner may contain links for Home, Documents and Lists, and Help.
  1. Do one the following on a SharePoint site:

Windows SharePoint Services 3.0

    1. If your list is not already open, click its name on the Quick Launch. If the name of your list doesn't appear, click View All Site Content, and then click the name of your list.
    2. On the Actions menu , click Export to Spreadsheet.
    3. If you are prompted to confirm the operation, click OK.

Windows SharePoint Services 2.0

    1. If your list is not already open, click Documents and Lists, and then click the name of your list.
    2. On the page that displays the list, under Actions, click Export to spreadsheet.
  1. In the File Download dialog box, click Open.
  2. If you are prompted whether to enable data connections on your computer, click Enable if you believe the connection to the data on the SharePoint site is safe to enable.
  3. Do one of the following:
    1. If no workbook is open, Excel creates a new blank workbook and inserts the data as a table on a new worksheet.
    2. If a workbook is open, do the following in the Import Data dialog box that appears:
      1. Under Select how you want to view this data in your workbook, click Table, PivotTable Report, or PivotChart and PivotTable Report.
      2. Under Where do you want to put the data, click Existing worksheet, New worksheet, or New workbook.

If you click Existing worksheet, click the cell where you want to place the upper-left corner of the list.

      1. Click OK.

 Top of Page

Update data from a SharePoint list

As people continue to update the list on the SharePoint site, you can update the data on your worksheet with the latest changes. Doing this overwrites the data that was originally imported from the SharePoint list and any changes that you made to the data in Excel.

  1. Click anywhere in the table that you want to update.
  2. On the Design tab, in the External Table Data group, click Refresh.

Tip  If you no longer want a connection between your worksheet and the SharePoint site, you can unlink the table. On the Design tab, in the External Table Data group, click Unlink.

 Top of Page

 

), "Dallas, Texas" is imported into one cell as Dallas, Texas. If no character or the apostrophe (') is specified as the text qualifier, "Dallas, Texas" is imported into two adjacent cells as "Dallas and Texas".
                  

Microsoft® and Microsoft Excel® are registered trademarks of Microsoft Corporation.
©2003-2013 LikeOffice - Privacy Policy - Contact us