Microsoft KB Archive/211908

= HOW TO: Use a Web Data Source for a PivotTable in Excel 2000 =

Article ID: 211908

Article Last Modified on 8/21/2007

-

APPLIES TO


 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q211908



For a Microsoft Excel 97 version of this article, see 164020.

IN THIS TASK
SUMMARY Prerequisite Files
 * Create the .csv File
 * Create the Data Source for the .Csv File
 * Create the .idc File
 * Create the .htx File
 * Create the .iqy Web Query File

Perform the Web Query Create the PivotTable Report How to Update the PivotTable When Data Changes REFERENCES



SUMMARY
This step-by-step article shows you how to refer to data that is on a Hypertext Transfer Protocol (HTTP) Web server when you create a PivotTable report.

Microsoft Excel 2000 contains many Internet-related features, including Web queries, hyperlinks, and .htm file support. However, when you create a PivotTable report, you cannot directly refer to data that is on a Hypertext Transfer Protocol (HTTP) Web server.

This article includes an example that uses a Web query to retrieve data from a comma separated value (.csv) file data source on an HTTP Web server. You can use the retrieved sample data to create a PivotTable report.

NOTE: The method included in this article also works for other types of data sources; however, this article provides an example for only a .csv data source.

back to the top

Prerequisite Files
To use a Web query to retrieve data from a Web-based .csv file, you must create three files in addition to your .csv data file. These files have the following extensions: .idc, .htx, and .iqy. Additionally, you must create a data source for the .csv file on your Web server.

back to the top

Create the .csv File
To create a sample .csv file, follow these steps:  Start Excel, and then open a new workbook.  Type the following data in Sheet1:   A1: Name   B1: Amount A2: Bob   B2: 1 A3: Sue   B3: 2 A4: Tom   B4: 3 A5: Sue   B5: 4 A6: Bob   B6: 5  On the File menu, click Save As, type Csvsource in the File name box, and then click CSV (Comma delimited)(*.csv) in the Save as type list. Click Save, click OK to save only the active sheet, and then click Yes on the message. Close Csvsource.csv and move it to the final location on your Web server.

back to the top

Create the Data Source for the .csv File
On the Web server, create a system Data Source Name (DSN) for the .csv file. The steps to do this may be different on your computer; however, the process is the same.

NOTE: Because there are several versions of Microsoft Windows, the following steps may be different on your computer. If they are, see your product documentation to complete these steps.

To create the DSN, follow these steps:
 * 1) In Control Panel, double-click the ODBC Data Sources icon.
 * 2) In the ODBC Data Source Administrator dialog box, click the System DSN tab, and then click Add.
 * 3) In the Create New Data Source dialog box, under Name, click Microsoft Text Driver (*.txt,*.csv), and then click Finish.
 * 4) In the ODBC Text Setup dialog box, type CSV_Source in the Data Source Name box.
 * 5) Clear the Use Current Directory check box, and then click Select Directory.
 * 6) In the Select Directory dialog box, locate and select the folder that contains Csvsource.csv, and then click OK.
 * 7) Click OK in the ODBC Text Setup dialog box, and then click OK in the ODBC Data Source Administrator dialog box.

back to the top

Create the .idc File
Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site:

https://partner.microsoft.com/global/30000104

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

An .idc file is a text file that you can create with any text editor. To do this, follow these steps:  Start Notepad.</li>  Type the following text in the Notepad document: Datasource: CSV_Source Template: Csv_form.htx DefaultParameters: Name="Name", Amount="Amount" SQLStatement: +Select "Name", "Amount" +From "Csvsource.csv" +Where "Amount" > 2 </li> Save this file as Csv_query.idc in the Scripts folder on your Web server.</li> Close the file.</li></ol>

back to the top

Create the .htx File
An .htx file is a text file that you can create with any text editor. To do this, follow these steps:  Start Notepad.</li>  Type the following text in the Notepad document: <HTML> <BODY>

<%begindetail%>

<%if CurrentRecord EQ 0 %>

</BODY> </HTML> </li> Save the file as Csv_form.htx in the Scripts folder on your Web server.</li> Close the file.</li></ol>

back to the top

Create the .iqy Web Query File
An .iqy file is a text file that you can create with any text editor. To do this, follow these steps:  Start Notepad.</li> Type the following in the Notepad document:

WEB

1

http:// /scripts/csv_query.idc

NOTE: In the HTTP Uniform Resource Locater (URL),  is the name of the Web server that contains the .csv, .idc, and .htx files.</li> Save the file as Csv_Web.iqy in the Queries folder on your computer.

NOTE: If you do not know the location of your Queries folder, use the Find command on the Tools menu in Windows Explorer.</li> Close the file.</li></ol>

back to the top

Perform the Web Query
To perform the Web query, follow these steps:
 * 1) Start Excel.
 * 2) On the Data menu, point to Get External Data, and then click Run Saved Query.
 * 3) Click CSV_Web.iqy (if necessary browse to find the Queries folder), and then click Get Data.
 * 4) In the Returning External Data to Microsoft Excel dialog box, click Existing worksheet, click the cell where you want to place the upper-left corner of the external data range, and then click OK.

A table of data from the .csv file is returned to the active sheet. Because the Structured Query Language (SQL) statement in your .idc file restricts the Amount field to values that are greater than 2, only three records are returned.

NOTE: This also creates a defined name in the workbook called CSV_Web.

back to the top

Create the PivotTable Report
To create the PivotTable report, follow these steps:
 * 1) Switch to Sheet2 in the workbook.
 * 2) On the Data menu, click PivotTable and PivotChart Report.
 * 3) In the PivotTable and PivotChart Wizard - Step 1 of 3 dialog box, click Microsoft Excel list or database and click Next.
 * 4) In the PivotTable and PivotChart Wizard - Step 2 of 3 dialog box, type Sheet1!CSV_Web in the Range box, and then, click Next.
 * 5) In the PivotTable and PivotChart Wizard - Step 3 of 3 dialog box, click Layout. Drag the Name field button to the ROW field, and then drag the Amount field button to the DATA field. Click OK, and then click Finish.

A PivotTable that is based on the data in Sheet1 is created.
 * 1) Save this workbook as Web_Test.xls.

back to the top

How to Update the PivotTable When Data Changes
To update the PivotTable, follow these steps:  <li> Change the SQL statement in the .idc file on your Web server to the following: SQLStatement: +Select "Name", "Amount" +From "Csvsource.csv" </li> <li>Save the .idc file.</li> <li>On the computer that runs Excel 2000, open Web_Test.xls.</li> <li>On Sheet1, select any cell in the table of data that is returned by the Web query. Click Refresh Data on the Data menu.

Because you changed the SQL statement in your .idc file, all of the records in your .csv file are returned to Sheet1.</li> <li>Switch to Sheet2, and then select any cell in the PivotTable.</li> <li>On the Data menu, click Refresh Data.</li></ol>

The PivotTable is updated with the new data that is returned by the Web query.

back to the top

<div class="references_section">