XL97: How to Use a Web Data Source for a PivotTable

 Last reviewed: February 12, 1998
Article ID: Q164020
The information in this article applies to:
  • Microsoft Excel 97 for Windows

SUMMARY

Microsoft Excel 97 contains many Internet-related features, including Web queries, hyperlinks, and .htm file support. However, when you create a PivotTable, you cannot directly refer to data that is on an 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.

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.

MORE INFORMATION

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 file have the following extensions: .idc, .htx, and .iqy. Also, you must create a data source for the .csv file on your Web server.

Creating the .Csv File

To create a sample .csv file, use the following steps:

  1. Start Microsoft Excel and create a new workbook.

  2. 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

  3. On the File menu, click Save As. In the Save As dialog box, type Csvsource in the File Name box. Then, click "CSV (Comma delimited)(*.csv)" in the Save As Type list.

  4. Click Save. Then, click OK to save only the active sheet.

  5. Close Csvsource.csv and move it to the final location on your Web server.

Creating 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.

To create the DSN, use the following steps:

  1. In the Control Panel, double-click the ODBC icon.

  2. In the Data Sources dialog box, click the System DSN tab.

  3. In the System Data Sources dialog box, click Add.

  4. In the Add Data Source dialog box, under Installed ODBC Drivers, click "Microsoft Text Driver (*.txt,*.csv)." Then, click Finish.

  5. In the ODBC Text Setup dialog box type "CSV_Source" (without the quotation marks) in the "Data Source Name" box.

  6. Clear the Use Current Directory check box. Then, click Select Directory.

  7. In the Select Directory dialog box, locate and select the folder that contains Csvsource.csv. Then, click OK.

  8. Click OK in the ODBC Text Setup dialog box. Then, click OK in the System Data Sources dialog box.

Creating the .Idc File

An .idc file is a text file that you can create with any text editor. To do this use the following steps:

  1. Start Notepad.

  2. 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

  3. Save this file as Csv_query.idc in the Scripts folder on your Web server.

  4. Close the file.

Creating the .Htx File

An .htx file is a text file that you can create with any text editor. To do this, use the following steps:

  1. Start Notepad.

  2. Type the following text in the Notepad document: 

          <HTML>
      <BODY>


          <%begindetail%>


          <%if CurrentRecord EQ 0 %>
      <TABLE>
      <TR>
      <TH><B>Name</B></TH>
      <TH><B>Amount</B></TH>
      </TR>
      <%endif%>


          <TR>
      <TD><%Name%></TD>
      <TD><%Amount%></TD>
      </TR>
      <%enddetail%>
      </TABLE>


          </BODY>
      </HTML>

  3. Save the file as Csv_form.htx in the Scripts folder on your Web server.

  4. Close the file.

Creating the .Iqy Web Query File

An .iqy file is a text file that you can create with any text editor. To do this, use the following steps:

  1. On the computer with Microsoft Excel 97, start Notepad.

  2. Type the following in the Notepad document: 

          WEB
      1
      http://web server name/scripts/csv_query.idc

    NOTE: In the HTTP URL, "web server name" is the name of the Web server that contains the .csv, .idc, and .htx files.

  3. On the File menu, click Save As.

  4. Change current directory to the Program Files\Microsoft Office\Queries folder on your computer, and then click Save.

  5. In the File Name box type "CSV_Web.iqy" (INCLUDING the quotation marks).

    NOTE: If you are using a text editor (for example, Notepad) and you fail to include the quotation marks in Step 4, the text editor may rename your file to CSV_Web.iqy.txt.

  6. Close the file.

Performing the Web Query

To Perform the Web query, use the following steps:

  1. Start Microsoft Excel 97.

  2. On the Data menu, point to Get External Data, and then click Run Web Query.

  3. Click CSV_Web.iqy, and then click Get Data.

  4. Click OK in the "Returning External Data to Microsoft Excel" dialog box.

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.

Creating the PivotTable

To create the PivotTable, use the following steps:

  1. Switch to Sheet2 in the workbook.

  2. On the Data menu, click PivotTable Report.

  3. In the "PivotTable Wizard – Step 1 of 4" dialog box, click "Microsoft Excel list or database" and click Next.

  4. In the "PivotTable Wizard – Step 2 of 4" dialog box, type "Sheet1!CSV_Web" (without the quotation marks) in the Range RefEdit box. Then, click Next.

  5. In the "PivotTable Wizard – Step 3 of 4" dialog box, drag Name to the ROW field, and then drag Amount to the DATA field. Click Finish.

    A PivotTable that is based on the data in Sheet1 is created.

  6. Save this workbook as Web_Test.xls.

Updating the PivotTable When Data Changes

To update the PivotTable, use the following steps:

  1. Change the SQL statement in the .idc file on your Web server to the following: 

          SQLStatement:
      +Select "Name", "Amount"
      +From "Csvsource.csv"

  2. Save the .idc file.

  3. On the computer with Microsoft Excel 97, open Web_Test.xls.

  4. On Sheet1, select any cell in the table of data that is returned by the Web query. Then, 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.

  5. Switch to Sheet2 and select any cell in the PivotTable.

  6. On the Data menu, click Refresh Data.

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

For additional information, please see the following articles in the Microsoft Knowledge Base: 

   ARTICLE-ID: Q157482
   TITLE     : XL97: How to Create Web Query (.iqy) Files

   ARTICLE-ID: Q162051
   TITLE     : XL97: How to Specify Dynamic Web Query Parameters

   ARTICLE-ID: Q162080
   TITLE     : XL97: How to Programmatically Perform a Web Query

REFERENCES

For more information about Web queries, click the Index tab in Microsoft Excel Help, type the following text 

   web, queries
and then double-click the selected text to go to the "Create a Web Query" topic.

KBCategory: kbtool kbusage kbhowto
KBSubcategory: xlweb xlquery
Additional reference words: 97 XL97
Keywords : xlquery xlweb kbtool kbualink97
Version : WINDOWS:97
Platform : WINDOWS
Issue type : kbhowto

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 12, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.