Fortunately, if you've got a Web server and Microsoft Excel, the HTTP protocol may be the only "middleware" you need. Excel has a built-in method of grabbing data from Web pages and inserting it into a worksheet. This enables you to create a single HTML-formatted report that can be displayed in any Web browser or inserted directly into a spreadsheet. Since there are so many ways to create HTML from your data, this makes an easy and powerful way to get it into your users' spreadsheets.
Get Your Web Data into Excel Step by Step
Let's take a look at the process of getting Web data into Excel. Below are the steps needed to pull data into Excel from any Web server, including your iSeries.
- Open a new or existing spreadsheet.
- Find where you want to insert the data. Select Data, then Get External Data, and then New Web Query, as shown in Figure 1.
Figure 1: Select Data, Get External Data, New Web Query. - Fill out the New Web Query panel. Enter the URL to retrieve in step 1. (Hint: Try the one shown in Figure 2 for an example.) Then, choose whether or not you want the whole page, only the tables, or one or more specific tables on the page. If you format the data using tables, you get more control over how things are imported. We'll go through that in a bit, but for now, select Entire Page for Step 2. For this example and most applications, you'll probably need only the words on the screen, rather than the formatting as well. One exception to this is if you've got links that you want to carry through to the spreadsheet. For now, set the formatting option in Step 3 to None. Click OK.
Figure 2: Enter the URL to retrieve into your worksheet. Try http://www.mcpressonline.com/mcpressonline_popular_articles.html for a live example! - Select where you want the data to go, and click OK. Note that you can select areas on the screen while the dialog box is open, so point and click!
Figure 3: Select where you want the data to be placed. - The data is inserted into your spreadsheet, ready to be calculated upon or further parsed. If you used the URL in the above example, you'll get a list of our most popular articles, as shown in Figure 4. Note that as of Excel 2000, there is a 65K record limit per worksheet. If you have any more than that, you'll need something like Microsoft Access, which unfortunately, as of at least Office 2000, doesn't seem to have the ability to import Web pages in a manner similar to this.
Figure 4: The page is returned in the worksheet.
How Refreshing!
One of the best things about creating a query in Excel is that you can refresh it automatically. To do so, simply right-click in the query range and select "Refresh Data" from the menu. If you don't see a menu that looks something like the one shown in Figure 5, make sure that you are clicking in the query range, which is any cell that contains data from the query.
Figure 5: Get new data automatically by right-clicking in the query range and selecting "Refresh Data."
You can also set queries (both Web queries and database queries) to refresh automatically. Do this by right-clicking in the query range and selecting Data Range Properties. That brings up the screen shown in Figure 6.
Figure 6: Set the query to refresh automatically by editing the data range properties.
Parsing Character Data into Columns
Sometimes, if you don't have control over the page you are retrieving, it may come in to the worksheet in one huge column with a row for each line in the file. In such cases, you may need to split the data into separate fields. There are two ways to do this: 1) parse the column data using available Excel tools or 2) reformat your source data into a table. There are advantages and disadvantages of each.
The Text to Columns Wizard
Excel has a built-in Wizard that may help get the data looking right. If your source data is comma-separated values (CSV) or some other uniform format, Excel's "Text to Columns" feature (shown in Figure 7) will handily convert your data.
Figure 7: One way to split simple data into columns is to invoke the Text to Columns Wizard.
One problem with the method of importing the whole page and then parsing the results is that it makes getting data into Excel columns a two-step process. That is, you have to import the data and then run a process on it to make it usable. That makes it more complex to distribute your spreadsheet if you want remote users to be able to update it directly from the source data.
Figure 8: If your data format matches the possibilities on this page, the Text to Columns Wizard can easily import your data. If not, HTML tables are the answer.
Another limitation of the Text to Columns Wizard is that it won't split data into fields based upon varying criteria. It is limited to data formats that can be described in the dialog shown in Figure 8. Depending upon your data, this may or may not be a problem. For example, since there are no clear delimiters, if I wanted to get the From and To locations in the data shown in Figure 7, the Wizard can't do it.
Because of these limitations, a more robust method of retrieving some forms of data is needed. Visual Basic for Applications is one possibility, but I prefer to avoid coding on the client if possible. Fortunately, there is another possibility: importing table data.
Importing Data from Tables
If the Web page that you are importing has data formatted into tables, it makes the data import process more automatic. Excel can be told to read all the tables on a page or one or more particular ones. Using this technique, you can format Web data into tables and have it come into your spreadsheet as columns, automatically--with complete headers! If you have control over the look and layout of the HTML pages that you are importing, you can tweak them for specific types of data transfers, if necessary. No middleware needed!
The process to import data from HTML tables is nearly the same as that outlined above. The only difference comes in Step 3 from the list above: specifying what on the page to grab. In this case, select the "Only the tables" option (Figure 9) or the option to choose one or more specific tables.
Figure 9: Select the "Only the tables" option to import all the tables on a page.
When you import the table data, each cell in the table will correspond to a cell in your spreadsheet, so the rows and columns will be the same as in your table.
Serving HTML with a Smile
There are many ways to output HTML from iSeries data, whether you serve it directly or indirectly. When serving it directly, the first thing you need is a Web server instance configured and running. For more information on how to set one up, see "Configuring the HTTP Server (Powered by Apache)", which describes the setup process and has links to sources of more information.
You can serve either static files (pages) or dynamic pages. Static files are just plain-text files that contain HTML that can be created by RPG programs or any other process capable of outputting plain-text files. In contrast to static files, dynamic HTML pages are built on the fly and require a process to run on the iSeries and output the results each time they are requested. Dynamic pages give you more current data, but they require processing each time they are requested, so they may have more of an impact on your system's resources. You can create dynamic pages in many ways, including Java Server Pages (JSP) and Java servlets, an RPG-CGI (aka e-RPG) program, a Net.Data macro (see "From Zero to Web with the HTTP Wizard and Net.Data" for a quick start), or any of the numerous third-party tools designed for HTML creation.
The method you choose depends upon your skills, requirements, and pre-existing environment. When considering which type to use, one interesting possibility is to create a report program that outputs static HTML to a flat file. Then, schedule that report program to run when needed (daily, weekly, monthly, etc.). This would give you updated data, yet would minimize the number of times the report would need to be run.
Learning to Love Microsoft Excel
IT shops typically have a love/hate relationship with Microsoft Excel. The love comes from the fact that, in many aspects, it's a great tool for users to analyze their own data and create custom reports. The hate begins when users go too far in creating their own complex reporting systems that need to be fed data, creating extra work for all involved. The worst situation, which I have seen too many times, is when people take data from sources such as iSeries reports and copy/paste or even re-key that data into their spreadsheets. Fortunately, with the many ways to bring external data into Excel, it's easier to learn to love Excel rather than fight it.
Brian Singleton, formerly Editor of Midrange Computing magazine is the Director of Web Development for MCPressOnline. Over the past few years, Brian has developed a line of best-selling training videos, authored the best-selling The OS/400 and Microsoft Office Integration Handbook--Second Edition, and spoken at many popular seminars and conferences. He can be reached at This email address is being protected from spambots. You need JavaScript enabled to view it.
LATEST COMMENTS
MC Press Online