3

I have a ~2 MB html file that I need to parse that contains about 500 rows and about 70 columns. I need to clean it up obviously in a way that I can later enter it into a SQL Server database. I have parsed files in the past with Perl but never an html file and I was wondering if there are any modules I should check out before I do plain matching and formatting.

A little update:

<td class="tableHeaderDarkCenter">CUSIP/ISIN</td>

<td class="tableHeaderDarkCenter">Stock Ticker</td>

<td class="tableHeaderDarkCenter">MLCC Code</td>

<td class="tableHeaderDarkCenter">Bond Ticker</td>

<td class="tableHeaderDarkCenter">Issuer Name</td>

<td class="tableHeaderDarkCenter">Convertible Price(USD)</td>

<td class="tableHeaderDarkCenter">Par Amount</td>

<td class="tableHeaderDarkCenter">Coupon</td>

<td class="tableHeaderDarkCenter">Maturity/Mandatory Conversion Date</td>

<td class="tableHeaderDarkCenter">Outstanding Amt ($MM)</td>

<td class="tableHeaderDarkCenter">Bonds/Shrs Outstanding</td>

<td class="tableHeaderDarkCenter">Market Value($MM)</td>

<td class="tableHeaderDarkCenter">Index Weight(%)</td>

<td class="tableHeaderDarkCenter">YTM(%)</td>

<td class="tableHeaderDarkCenter">YTP(%)</td>

<td class="tableHeaderDarkCenter">Greater of YTM/YTP(%)</td>

<td class="tableHeaderDarkCenter">Duration</td>

<td class="tableHeaderDarkCenter">Currency</td>

<td class="tableHeaderDarkCenter">Country</td>

<td class="tableHeaderDarkCenter">Series</td>

<td class="tableHeaderDarkCenter">Accrued Interest</td>

<td class="tableHeaderDarkCenter">Current Yield(%)</td>

<td class="tableHeaderDarkCenter">Yield Advantage(%)</td>

<td class="tableHeaderDarkCenter">Moody Rating</td>

<td class="tableHeaderDarkCenter">S&P Rating</td>

<td class="tableHeaderDarkCenter">Avg. Rating</td>

<td class="tableHeaderDarkCenter">Internal Rating</td>

<td class="tableHeaderDarkCenter">Collateral Type</td>

<td class="tableHeaderDarkCenter">Status</td>

<td class="tableHeaderDarkCenter">Security Type</td>

<td class="tableHeaderDarkCenter">Announce Date</td>

<td class="tableHeaderDarkCenter">Issue Date</td>

<td class="tableHeaderDarkCenter">At-Issue Yield</td>

<td class="tableHeaderDarkCenter">At-Issue Prem</td>

<td class="tableHeaderDarkCenter">Delta</td>

<td class="tableHeaderDarkCenter">Gamma</td>

<td class="tableHeaderDarkCenter">RHO</td>

<td class="tableHeaderDarkCenter">Theoretical Value</td>

<td class="tableHeaderDarkCenter">Theoretical Discount (%)</td>

<td class="tableHeaderDarkCenter">Cheap (%)</td>

<td class="tableHeaderDarkCenter">Conversion Ratio</td>

<td class="tableHeaderDarkCenter">Parity Cash Adjustment</td>

<td class="tableHeaderDarkCenter">Payback</td>

<td class="tableHeaderDarkCenter">Implied Volatility(%)</td>

<td class="tableHeaderDarkCenter">Implied Spread</td>

<td class="tableHeaderDarkCenter">Parity Delta</td>

<td class="tableHeaderDarkCenter">Conversion Premium(%)</td>

<td class="tableHeaderDarkCenter">Investment Value Premium(%)</td>

<td class="tableHeaderDarkCenter">Investment Value(Bond floor)</td>

<td class="tableHeaderDarkCenter">Price to Par</td>

<td class="tableHeaderDarkCenter">Next Put Date</td>

<td class="tableHeaderDarkCenter">Yrs to Put</td>

<td class="tableHeaderDarkCenter">Yrs to Mat/Mand Conv Date</td>

<td class="tableHeaderDarkCenter">Yrs to Mat/Yrs to Put</td>

<td class="tableHeaderDarkCenter">Years to Call</td>

<td class="tableHeaderDarkCenter">1-Day Total Return(%)</td>

<td class="tableHeaderDarkCenter">1-WK Total Return(%)</td>

<td class="tableHeaderDarkCenter">MTD Total Return(%)</td>

<td class="tableHeaderDarkCenter">QTD Total Return(%)</td>

<td class="tableHeaderDarkCenter">YTD Total Return(%)</td>

<td class="tableHeaderDarkCenter">Index Sector</td>

<td class="tableHeaderDarkCenter">Industry</td>

<td class="tableHeaderDarkCenter">CS Sector L1</td>

<td class="tableHeaderDarkCenter">CS Sector L2</td>

<td class="tableHeaderDarkCenter">CS Sector L3</td>

<td class="tableHeaderDarkCenter">CS Sector L4</td>

<td class="tableHeaderDarkCenter">CS Sector L5</td>

<td class="tableHeaderDarkCenter">ML Sector L1</td>

<td class="tableHeaderDarkCenter">ML Sector L2</td>

<td class="tableHeaderDarkCenter">ML Sector L3</td>

<td class="tableHeaderDarkCenter">ML Sector L4</td>

<td class="tableHeaderDarkCenter">GIC Sector</td>

<td class="tableHeaderDarkCenter">GIC Industry Group</td>

<td class="tableHeaderDarkCenter">GIC Industry</td>

<td class="tableHeaderDarkCenter">GIC Sub Industry</td>

<td class="tableHeaderDarkCenter">Bloomberg Sector</td>

<td class="tableHeaderDarkCenter">Stock Price(USD)</td>

<td class="tableHeaderDarkCenter">Stock Yield</td>

<td class="tableHeaderDarkCenter">1-Day Equity Total Return(%)</td>

<td class="tableHeaderDarkCenter">1-WK Equity Total Return(%)</td>

<td class="tableHeaderDarkCenter">MTD Equity Total Return(%)</td>

<td class="tableHeaderDarkCenter">QTD Equity Total Return(%)</td>

<td class="tableHeaderDarkCenter">YTD Equity Total Return(%)</td>

<td class="tableHeaderDarkCenter">Eq Mkt Value($MM)</td>

This is what occurs in the file and from the end of this until the end of the file are the corresponding values that map to the column names. Obviously a lot. I'm giving the HTML::TableExtract a shot, but I'm not sure if it is right for the situation.

serenesat
  • 4,611
  • 10
  • 37
  • 53
squiguy
  • 32,370
  • 6
  • 56
  • 63
  • 1
    Thank you for asking about the root of the problem at hand. Many novices dive right in to "how can I make this regex work?" and simply miss the bigger picture. Also, because it's so important to [understand the complexities of parsing (x)html with regex.](http://stackoverflow.com/a/1732454/18097) – Robert P Jul 06 '12 at 16:37
  • I figured I would try and make a non-generalized question. It seems pretty complex to me and I am currently giving these answers a shot. Thanks to all who answered. I shall be back soon with results. – squiguy Jul 06 '12 at 16:39
  • 1
    @squiguy: what are your misgivings about `HTML::TableExtract`? Having seen some of the data it looks like an exact fit to me. It may be difficult to get started using a new module, and if you want to show a more complete data set then I suggest you publish something on [`pastebin`](http://pastebin.com/). – Borodin Jul 06 '12 at 17:45
  • @Borodin here is the url: http://pastebin.com/2girRaVA. I'm not against it, I just am wondering how to use it and if it is right after all. You know it better than I do. I essentially want to map the top values as columns and then the next tags will be in their correct row. Let me know if you need any more clarifications. Thanks for your time! – squiguy Jul 06 '12 at 17:54
  • @squiguy: I have looked at your data and unfortunately you have omitted everything except the `` and `` tags so I can't tell how your document is structured. However I have updated my answer to show a brief program that parses an HTML file and returns each row of the first table found as a Perl array. – Borodin Jul 06 '12 at 19:57

2 Answers2

5

Your friend here is HTML::TableExtract. It is well-written (has received five out of five stars in a total of five reviews) and allows you to specify the data you want to extract in very convenient ways.

Update

To demonstrate how easy it is to apply HTML::TableExtract to some HTML data, here is some code that prints each row of the first table found in an file.

If there is more than one table in the file then you must use one of several ways the module provides to select one of them.

The data on each row is returned as a Perl array, which is simple to store in a database using DBI.

use strict;
use warnings;

use HTML::TableExtract;

my $te = HTML::TableExtract->new;
$te->parse_file('data_snippet.txt');
my $table = $te->first_table_found;

for my $row ($table->rows) {
  print "@$row\n";
}
Borodin
  • 126,100
  • 9
  • 70
  • 144
1

Use ready HTML parser like HTML::Parser, HTML::TreeBuilder and like. Then simply iterate over table in DOM.

Oleg V. Volkov
  • 21,719
  • 4
  • 44
  • 68