Overview of connecting to (importing) data
This reference article discusses importing and connecting data. You will learn about tasks like importing, updating, securing, and managing data.
In this article
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 programs and users.
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 to, 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 to your workbook, which is an operation that can be time consuming and prone to error. 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 into Excel, you need access to the data. If the external data source 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 permissions, 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.
1. There are a variety of data sources that you can connect to: Analysis Services, SQL Server, Microsoft Access, other OLAP and relational databases, spreadsheets, and text files.
2. Many data sources have an associated ODBC driver or OLE DB provider.
3. A connection file defines all the information that is needed to access and retrieve data from a data source.
4. Connection information is copied from a connection file into a workbook, and the connection information can easily be edited.
5. The data is copied into a workbook so that you can use it just as you use data stored directly in the workbook.
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 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 SharePoint Foundation site. For more information about DCLs, see the section Publishing to Excel Services securely.
-
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.
After 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 (indicated by the file name that is displayed in the Connection File property on the Definition tab). After 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.
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 the source of external data. You may want to do this in case 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.
Sharing connections
Connection files are particularly useful for sharing connections on a consistent basis, making connections more discoverable, helping to improve security of connections, 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 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 returned by the query and modify the query to retrieve different results. 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 (.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.
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 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 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.
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 | Creates | OLE | ODBC | Text | HTML | XML | Share- | |
| Import Text Wizard | Yes | No | No | Yes | No | No | No | |
| PivotTable report | No | Yes | Yes | Yes | No | No | Yes | |
| PivotTable report | 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 (Excel in a web browser), 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 the links in the See Also section.
Understanding Data Access Components
Microsoft Data Access Components (MDAC) 2.8 is included with Microsoft Windows Server 2003 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 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.
Windows Vista and Windows 7 use Windows Data Access Components (Windows DAC).
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.
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:
-
Ensure that the appropriate ODBC driver is installed on the computer that contains the data source.
-
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 only on 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.
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 the section Importing data by using the Data Connection Wizard.
-
Create a blank text file with a .udl file name extension, and then edit the file, which displays the Data Link Properties dialog box.
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 that is connected to an external data source.
1. A refresh operation gets up-to-date data.
2. The connection file defines all the information that is needed to access and retrieve data from an external data source.
3. There are a variety of data sources that you can refresh: OLAP, SQL Server, Access, OLE DB, ODBC, spreadsheets, and text files.
4. 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 to gain access to the data, you can require that the password is entered each time the external data range is refreshed.
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 Access, HTML files on the World Wide Web, text files, Microsoft SQL Server, 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.
Importing data from an Access database
There are several ways to exchange data between Microsoft Access and Microsoft 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, to import means to make a permanent connection to data that can be refreshed. In Access, to 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 the functions in Excel 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, or form), or selected records in a view into an Excel worksheet. When you perform an export operation in Access, you can save the design of that operation 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 of data in Excel but also want that data to be available from within Access. You create this type of link from within the Access database, but 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 Excel 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 still 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 stored 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 that point on, you can import the data into Access. 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.
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 through your company's intranet or through a modem on your computer or network, or you can make a query against HTML or XML files that are stored on your computer.
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.
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 database server and the way that you log on to the server.
-
Page 2: Select Database and Table Use this page to specify the database, and table or query that contains the data you want.
-
Page 3: Save Data File and Connection Use this page to specify and describe the connection file and search phrases for locating the file.
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) 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 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 is a file with a .cub extension that stores a portion of the source data from an OLAP server database. Use an offline cube file to continue to make changes to PivotTable and PivotChart reports when the server is unavailable or when you are disconnected from the network.
When you connect to 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.
Importing XML data
Excel makes it easy to import Extensible Markup Language (XML) data that is created from other databases and applications, map XML elements from an XML schema 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.), in which 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.
Importing data by using the Data Connection Wizard
You can use the Data Connection Wizard to connect to an OLE DB or 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. This displays the Connection tab, in which you can 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. You may also 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.
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, or you can use advanced criteria in Query to create a more complex query and do the following:
-
Filter rows or columns of data before they are brought into Excel.
-
Create a parameter query.
-
Sort data before it is brought into Excel.
-
Join multiple tables.
Microsoft Query provides a simple front end, easily accessible from within Excel, to perform these specialized query tasks.
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:
-
You can use Visual Basic for Applications to gain access to an external data source. Depending on the data source, you can use either ActiveX Data Objects or Data Access Objects to retrieve the data. You can also define a connection string in your code that specifies the connection information. Using a connection string is useful, for example, when you want to avoid requiring system administrators or users to first create a connection file, or to simplify the installation of your application.
-
If you import data from an SQL Server database, consider using SQL Native Client, which is a standalone data access Application Programming Interface (API) that is used for both OLE DB and ODBC. It combines the SQL OLE DB Provider and the SQL ODBC Driver into one native, dynamic link library (DLL), while also providing new functionality that is separate and distinct from the Microsoft Data Access Components (MDAC). You can use SQL Native Client to create new applications or enhance existing applications that can take advantage of newer SQL Server features, such as Multiple Active Result Sets (MARS), User-Defined Types (UDT), and XML data type support.
-
The RTD function retrieves real-time data from a program that supports COM automation. The RTD COM automation add-in must be created and registered on a local computer.
-
The SQL.REQUEST function connects with an external data source and runs a query from a worksheet. The SQL.REQUEST function then returns the result as an array without the need for macro programming. If this function is not available, you must install the Microsoft Excel ODBC add-in program (XLODBC.XLA). You can install the add-in from Office.com.
For more information about creating Visual Basic for Applications, see Visual Basic Help.
Making data access more secure
When you connect to an external data source or refresh 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.
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 is stored from a trusted location.
To help improve security, you may want to disable connections to external data 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 the links in the See Also section.
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.
Important: 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. 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 may 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 in a workbook or connection file. It is more secure for users to enter their logon information each time that they log on.
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.
-
SSS Select this option to use Secure Storage Service, and then enter the appropriate identification string in the SSS ID text box. A site administrator can configure a SharePoint site to use a Secure Storage Service 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 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 users' 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 SharePoint Server and a user's client computer to automatically detect changes to the connection file and then use the most up-to-date version of that connection file. For more information, see Microsoft SharePoint Foundation 2010 Central Administration Help.
Issues about connecting to data
The following sections discuss several issues that you may encounter when you connect to external data
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:
-
Simplify your query Make sure that you include only those tables and fields that are needed for your query. If your query contains unnecessary tables or fields, delete them from the query to reduce the size of the temporary query file.
-
Use criteria to reduce the size of the result set Use criteria to retrieve only specific records rather than retrieving all of the records from a database. For more information, see Microsoft Query Help.
-
Set a limit on the number of records returned by the query Limit the number of records the query returns. In Microsoft Query, click Options on the Edit menu, select the Limit number of records returned to check box under User settings, and then enter the maximum number of records to return in the Records box.
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.
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 Excel.
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 SharePoint Foundation, 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.
Microsoft Office Tutorials: Overview Of Connecting To (Importing) Data >>>>> Download Now
ReplyDelete>>>>> Download Full
Microsoft Office Tutorials: Overview Of Connecting To (Importing) Data >>>>> Download LINK
>>>>> Download Now
Microsoft Office Tutorials: Overview Of Connecting To (Importing) Data >>>>> Download Full
>>>>> Download LINK w3