Difference between revisions of "Inserting Awasu Data Into Microsoft Excel"

From AwasuWiki
Jump to: navigation, search
m (Creating a Web Query)
m (Retrieving the Awasu data)
 
(8 intermediate revisions by one other user not shown)
Line 1: Line 1:
 
Beginning with Microsoft Excel 97, there is a feature named '''Web Query''' that can be used to insert text from a URL into the current Workbook.
 
Beginning with Microsoft Excel 97, there is a feature named '''Web Query''' that can be used to insert text from a URL into the current Workbook.
 +
 +
==Why Excel?==
 +
You might think "Why would I want to read my feeds in Excel? Isn't Awasu or even [http://www.awasu.com/wiki/Inserting_Awasu_Data_Into_Microsoft_Word Microsoft Word] a better option?".  Generally you'd be correct. Awasu itself provides a much better user experience for reading text-based feeds, however remember that ''one'' of Awasu (''many'') power features is the ability to extract [http://www.awasu.com/help/2.4/Advanced%20features/Templates/02-Metadata.html metadata] from feeds, store it for later use, and export the metadata via [http://www.awasu.com/help/2.4/Productivity%20tools/05-ChannelReports.html Channel Reports] and/or the [http://www.awasu.com/api Awasu API].  Sometimes the metadata is text-based, but sometimes it's numerical based as is the case with the [http://www.awasu.com/wiki/GeoRSS_Metadata_module GeoRSS] and [http://www.awasu.com/wiki/ShakeMap_Metadata_module ShakeMap] metadata.  Exporting GeoRSS and/or ShakeMap metadata to Excel would allow you to sort, filter and run numerical-based statistical formulas against the metadata graciously extracted by Awasu.
  
 
==A "live" link to the text==
 
==A "live" link to the text==
Line 8: Line 11:
 
As an example, if you want to retrieve all of a specific Workpad's items from a Workpad whose Workpad ID is 784FCEFF-BBB5-497A-B0C2-7415F5B4C50B, you would construct a URL similar to this:
 
As an example, if you want to retrieve all of a specific Workpad's items from a Workpad whose Workpad ID is 784FCEFF-BBB5-497A-B0C2-7415F5B4C50B, you would construct a URL similar to this:
  
http://localhost.:2604/workpads/list?token=MyToken&items=true&id=784FCEFF-BBB5-497A-B0C2-7415F5B4C50B&format=HTMLTable
+
http://localhost:2604/workpads/list?token=MyToken&items=true&id=784FCEFF-BBB5-497A-B0C2-7415F5B4C50B&format=HTMLTable
  
 
'''Note:''' ''Make sure to replace '''MyToken''' with your Awasu Token.  Also your TCPIP port may not be 2604, and the server doesn't have to be '''localhost''', if Remote access has been enabled in Awasu 2.4.2 Professional, you can retrieve Awasu data across a network connection. And note that I've created a custom Awasu API template that formats a Workpad's items into an HTML table and I'm using my custom template by adding "'''&format=HTMLTable'''" to the URL.''
 
'''Note:''' ''Make sure to replace '''MyToken''' with your Awasu Token.  Also your TCPIP port may not be 2604, and the server doesn't have to be '''localhost''', if Remote access has been enabled in Awasu 2.4.2 Professional, you can retrieve Awasu data across a network connection. And note that I've created a custom Awasu API template that formats a Workpad's items into an HTML table and I'm using my custom template by adding "'''&format=HTMLTable'''" to the URL.''
Line 43: Line 46:
 
'''Note:''' ''These instructions are from Excel 2003 and should work similarly with previous Excel versions. Excel 2007's "Ribbon" has dramatically changed Excel 2007's user interface, so the steps may be different in Excel 2007.''
 
'''Note:''' ''These instructions are from Excel 2003 and should work similarly with previous Excel versions. Excel 2007's "Ribbon" has dramatically changed Excel 2007's user interface, so the steps may be different in Excel 2007.''
  
''Note: 4/4/2010 Everything below has not been edited yet for Excel, it still a copy and paste from the similar word article I'll update it soon.''
 
 
The easiest method to create an Excel Web Query is to:
 
The easiest method to create an Excel Web Query is to:
 
# Type an Awasu API into Internet Explorer and press the Enter key (''wait for the data to be displayed'')
 
# Type an Awasu API into Internet Explorer and press the Enter key (''wait for the data to be displayed'')
# Right click any where in the HTML table of data in your Internet Explorer and select '''Export to Microsoft Excel''' in the popup window.
+
# Right click any where in the HTML table of data in your Internet Explorer and select '''Export to Microsoft Excel''' in the popup window
 
# A new instance of Excel will start (''even if Excel was already running'')
 
# A new instance of Excel will start (''even if Excel was already running'')
 
# Wait a few seconds (''note the spinning globe icon in Excel's status bar'')
 
# Wait a few seconds (''note the spinning globe icon in Excel's status bar'')
Line 53: Line 55:
 
While this is the easiest method there is an alternative:
 
While this is the easiest method there is an alternative:
 
# Start Excel (''if it's not already running'')
 
# Start Excel (''if it's not already running'')
# Select the '''Import External Data''' from the '''Data''' menu.
+
# Select the '''Import External Data''' from the '''Data''' menu
# Select the '''New Web Query...''' from the '''Import External Data''' sub-menu.
+
# Select the '''New Web Query...''' from the '''Import External Data''' sub-menu
# Enter an Awasu API URL in the '''Address''' text box and click the '''Go''' button.
+
# Enter an Awasu API URL in the '''Address''' text box and click the '''Go''' button
# Click the '''left arrow''' button next to the table that you want to import and then click the '''Import''' button.
+
# Click the '''left arrow''' button next to the table that you want to import and then click the '''Import''' button
 
# Wait a few seconds (''note the spinning globe icon in Excel's status bar'')
 
# Wait a few seconds (''note the spinning globe icon in Excel's status bar'')
  
==Updating the included text==
+
==Updating the Web Query==
 
There are several actions that can cause the included text to be updated from the data source ('''Awasu'''):
 
There are several actions that can cause the included text to be updated from the data source ('''Awasu'''):
# Right clicking on the field and selecting "Update Field" (updates a single field)
 
# Pressing the F9 key in Excel (updates all fields)
 
# Printing the Workbook if the "Update Fields" checkbox is ticked in the Tools, Options dialog box ("Print" tab)
 
# Opening the Workbook if the "update automatic links at Open" checkbox is ticked in the Tools, Options dialog box ("General" tab)
 
# Clicking the "Update Now" button in the Edit, Links dialog box
 
  
==Breaking the link to the text==
+
'''Manual Refresh'''
 +
# Right click anywhere with the data returned by the Web Query and select "Refresh Data"
 +
# Clicking the "Refresh Data" toolbar button on the "External Data" toolbar
 +
 
 +
'''Automated Refresh'''
 +
# Right click anywhere with the data returned by the Web Query and select "Data Range Properties..."
 +
# In the "External Data Range Properties" dialog box you can select to:
 +
## "Refresh every XX minutes" (''from 1 to 32767 minutes'')
 +
## "Refresh data on file open"
 +
 
 +
==Breaking the Web Query's link to the text==
 
To permanently break the link between Excel and Awasu:
 
To permanently break the link between Excel and Awasu:
  
# Select '''Links...''' from the '''Edit''' menu.
+
# Right click anywhere with the data returned by the Web Query and select "Data Range Properties..."
# Select the link you want to break from the '''Source file''' list box.
+
# Untick the "Save query definition" check box in the "Data Range Properties..." dialog box
# Click the '''Break Link''' button
+
# Click the '''OK''' button in the confirmation dialog box
# Click '''Yes''' in the confirmation dialog box.
+
# Click the '''OK''' button in the "Data Range Properties..."
# Click the '''OK''' button.
 

Latest revision as of 21:29, 6 February 2011

Beginning with Microsoft Excel 97, there is a feature named Web Query that can be used to insert text from a URL into the current Workbook.

Why Excel?

You might think "Why would I want to read my feeds in Excel? Isn't Awasu or even Microsoft Word a better option?". Generally you'd be correct. Awasu itself provides a much better user experience for reading text-based feeds, however remember that one of Awasu (many) power features is the ability to extract metadata from feeds, store it for later use, and export the metadata via Channel Reports and/or the Awasu API. Sometimes the metadata is text-based, but sometimes it's numerical based as is the case with the GeoRSS and ShakeMap metadata. Exporting GeoRSS and/or ShakeMap metadata to Excel would allow you to sort, filter and run numerical-based statistical formulas against the metadata graciously extracted by Awasu.

A "live" link to the text

The text inserted by the Web Query is typically a "live" link to the associated data source, so that any changes made to the original data source will automatically be reflected in your Excel Workbook. This "live" link can easily be disabled, or permanently broken altogether; depending upon your goal, a live link to the current data could be a good feature or a bad feature.

Retrieving the Awasu data

Using Awasu 2.4.2, type an API URL to retreive the data that you want inserted into the Excel Workbook. As an example, if you want to retrieve all of a specific Workpad's items from a Workpad whose Workpad ID is 784FCEFF-BBB5-497A-B0C2-7415F5B4C50B, you would construct a URL similar to this:

http://localhost:2604/workpads/list?token=MyToken&items=true&id=784FCEFF-BBB5-497A-B0C2-7415F5B4C50B&format=HTMLTable

Note: Make sure to replace MyToken with your Awasu Token. Also your TCPIP port may not be 2604, and the server doesn't have to be localhost, if Remote access has been enabled in Awasu 2.4.2 Professional, you can retrieve Awasu data across a network connection. And note that I've created a custom Awasu API template that formats a Workpad's items into an HTML table and I'm using my custom template by adding "&format=HTMLTable" to the URL.

My custom template is listed here. If you copy and paste this text make sure you save it in your API directory with the file name of workpads-list.HTMLTable. The file extension of API templates must match the value of the format paramter in the URL.

{%REPEAT% Workpads}
<table rules="all" style="border-collapse:collapse;">
  <caption>{%WORKPAD-NAME%}{%?WORKPAD-DESCRIPTION%} - {%WORKPAD-DESCRIPTION%}{%ENDIF%}</caption>
  <thead>
    <tr>
      <th>Title</th>
      <th>URL</th>
      <th>Description</th>
      <th>Notes</th>
    </tr>
  </thead>
  <tbody>
    {%REPEAT% WorkpadItems}
    <tr>
      <td>{%ITEM-TITLE%}</td>
      <td>{%ITEM-URL%}</td>
      <td>{%ITEM-DESCRIPTION%}</td>
      <td>{%ITEM-NOTES%}</td>
    </tr>
    {%/REPEAT%}
  </tbody>
</table>
{%/REPEAT%} {%SET-CONTENT-TYPE% text/html}

Creating a Web Query

Note: These instructions are from Excel 2003 and should work similarly with previous Excel versions. Excel 2007's "Ribbon" has dramatically changed Excel 2007's user interface, so the steps may be different in Excel 2007.

The easiest method to create an Excel Web Query is to:

  1. Type an Awasu API into Internet Explorer and press the Enter key (wait for the data to be displayed)
  2. Right click any where in the HTML table of data in your Internet Explorer and select Export to Microsoft Excel in the popup window
  3. A new instance of Excel will start (even if Excel was already running)
  4. Wait a few seconds (note the spinning globe icon in Excel's status bar)


While this is the easiest method there is an alternative:

  1. Start Excel (if it's not already running)
  2. Select the Import External Data from the Data menu
  3. Select the New Web Query... from the Import External Data sub-menu
  4. Enter an Awasu API URL in the Address text box and click the Go button
  5. Click the left arrow button next to the table that you want to import and then click the Import button
  6. Wait a few seconds (note the spinning globe icon in Excel's status bar)

Updating the Web Query

There are several actions that can cause the included text to be updated from the data source (Awasu):

Manual Refresh

  1. Right click anywhere with the data returned by the Web Query and select "Refresh Data"
  2. Clicking the "Refresh Data" toolbar button on the "External Data" toolbar

Automated Refresh

  1. Right click anywhere with the data returned by the Web Query and select "Data Range Properties..."
  2. In the "External Data Range Properties" dialog box you can select to:
    1. "Refresh every XX minutes" (from 1 to 32767 minutes)
    2. "Refresh data on file open"

Breaking the Web Query's link to the text

To permanently break the link between Excel and Awasu:

  1. Right click anywhere with the data returned by the Web Query and select "Data Range Properties..."
  2. Untick the "Save query definition" check box in the "Data Range Properties..." dialog box
  3. Click the OK button in the confirmation dialog box
  4. Click the OK button in the "Data Range Properties..."