HTML Table to MYSQL DB

Fiddler has asked for the wisdom of the Perl Monks concerning the following question:

Monks, I'm in dire need of some direction. I'm trying to read some html tables and ingest them into an Sql DB using perl. I cant for the life of me understand regex and matching tags So let me give see if i can explain this better. A sample of the table looks like this (machine generated)

<BR><B>
00:00 - 00:15
</B>
<TABLE BORDER>
<TR>
 <TD> Channel </TD>
<TD> Call Letters </TD>
<TD>  Count </TD>
<TD> Percent  </TD>
<TD> Title   </TD>

</TR>
<TR></TR>
 <TR>
<TD align=right>
      1 </TD>
<TD >
&nbsp;   ch1 &nbsp; </TD>
<TD align=right>
    22 </TD>
<TD >
&nbsp;  6.52% &nbsp; </TD>
<TD >
Program 1  </TD>

</TR>
 <TR>
<TD align=right>
      3 </TD>
<TD >
&nbsp;   Ch1 &nbsp; </TD>
<TD align=right>
     5 </TD>
<TD >
&nbsp;  1.48% &nbsp; </TD>
<TD >
Program2 </TD>

</TR>
 <TR>
<TD align=right>
      4 </TD>
<TD >
&nbsp;   Ch 3 &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.29% &nbsp; </TD>
<TD >
Program3    </TD>

</TR>
 <TR>
<TD align=right>
      5 </TD>
<TD >
&nbsp; CH53 &nbsp; </TD>
<TD align=right>
     5 </TD>
<TD >
&nbsp;  1.48% &nbsp; </TD>
<TD >
Program4 </TD>

</TR>
 <TR>
<TD align=right>
      6 </TD>
<TD >
&nbsp;      C &nbsp; </TD>
<TD align=right>
     3 </TD>
<TD >
&nbsp;  0.89% &nbsp; </TD>
<TD >
Program5 </TD>

</TR>
</TABLE>
[download]

It pretty much loops like this throughout, what i'm trying to do is convert this to a csv or something and ingest it into a DB. If i can directly write to the DB when looping through that'll be ever better. Let me know if you can help me. With any solution. P.S this is my script so far.


use warnings;
use strict;
use HTML::TableExtract;
use LWP::Simple;

my $file ="/path/to/file/file.htm";
my $T = HTML::TableExtract->new();
my $table = $T->parse_file($file)
             ->first_table_found;

my @rows = $table->rows;

   foreach my $row ($T->rows) {
      print join(',', @$row), "\n";
   }
[download]

It only processes the FIRST table tho... not the rest of the document.

Comment on HTML Table to MYSQL DB Select or Download Code

Replies are listed 'Best First'.

Re: HTML Table to MYSQL DB
by moritz (Cardinal) on Nov 02, 2011 at 14:58 UTC

I'd try one of the HTML parsing modules from CPAN, for example HTML::TableExtract, HTML::TreeBuilder or Mojo::DOM.

Perl 6 - second systems done right

[reply]

A reply falls below the community's threshold of quality. You may see it by logging in.

Re: HTML Table to MYSQL DB
by choroba (Cardinal) on Nov 02, 2011 at 15:01 UTC

HTML::TableExtract

[reply]

A reply falls below the community's threshold of quality. You may see it by logging in.

Re: HTML Table to MYSQL DB
by zentara (Cardinal) on Nov 02, 2011 at 17:12 UTC

extract data using HTML::TableExtract

#!/usr/bin/perl
use strict;
use warnings;
use diagnostics;
use HTML::TableExtract;

my $table_1 = '
<table><tr><td>foo</td><td>bar</td></tr>
<tr><td>baz</td><td>quux</td></tr></table>';

my $table_2 = '
<table><tr><td>bof</td><td>xyzzy</td></tr>
<tr><td>bat</td><td>gazonk</td></tr></table>';

my $te = HTML::TableExtract->new();
$te->parse($table_1);

foreach my $ts ($te->tables) {
   print "Table (", join(',', $ts->coords), "):\n";
   foreach my $row ($ts->rows) {
      print join(',', @$row), "\n";
   }
 }

print "\n#######################\n";


## what goes here if I want to dump table_1 ?
## it appends, not resetting

$te->parse($table_1);

foreach my $ts ($te->tables) {
   print "Table (", join(',', $ts->coords), "):\n";
   foreach my $row ($ts->rows) {
      print join(',', @$row), "\n";
   }
 }
[download]

I'm not really a human, but I play one on earth.
Old Perl Programmer Haiku ................... flash japh

[reply]
[d/l]

Re^2: HTML Table to MYSQL DB

by Fiddler (Initiate) on Nov 02, 2011 at 18:16 UTC

Thank you so much. i will add this to my code.. Will post the results as they come

[reply]

Re^3: HTML Table to MYSQL DB

by Fiddler (Initiate) on Nov 03, 2011 at 15:27 UTC

00:00 - 00:30

Channel	Call Letters	Count	Percent	Title
1	IETV	3	0.81%	IETV
3	CNC3	1	0.27%	CNC3 Overnight
4	TV 4	1	0.27%	TV 4
5	CCNTV6	16	4.35%	For Your Eyes Only
6	C	2	0.54%	C Overnight
7	GAYEL	2	0.54%	Macajuel Time
8	IBN	1	0.27%	Press TV
9	ACTS	2	0.54%	ACTS - Family Education
10	EWTN	2	0.54%	Daily Mass: Our Lady
12	WIN	1	0.27%	WIN

00:30 - 01:00

Channel	Call Letters	Count	Percent	Title
1	IETV	1	0.37%	IETV
3	CNC3	3	1.12%	CNC3 Overnight
4	TV 4	1	0.37%	TV 4
5	CCNTV6	15	5.63%	For Your Eyes Only
6	C	4	1.50%	C Overnight
7	GAYEL	1	0.37%	Macajuel Time
9	ACTS	4	1.50%	ACTS - Family Education
12	WIN	1	0.37%	WIN
14	WI	3	1.12%	Local Sports
15	SYN	1	0.37%	Synergy Nights


<BR><B>
00:00 - 00:30
</B>
<TABLE BORDER>
<TR>
 <TD> Channel </TD>
<TD> Call Letters </TD>
<TD>  Count </TD>
<TD> Percent  </TD>
<TD> Title   </TD>

</TR>
<TR></TR>
 <TR>
<TD align=right>
      1 </TD>
<TD >
&nbsp;   IETV &nbsp; </TD>
<TD align=right>
     3 </TD>
<TD >
&nbsp;  0.81% &nbsp; </TD>
<TD >
IETV   </TD>

</TR>
 <TR>
<TD align=right>
      3 </TD>
<TD >
&nbsp;   CNC3 &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.27% &nbsp; </TD>
<TD >
CNC3 Overnight </TD>

</TR>
 <TR>
<TD align=right>
      4 </TD>
<TD >
&nbsp;   TV 4 &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.27% &nbsp; </TD>
<TD >
TV 4   </TD>

</TR>
 <TR>
<TD align=right>
      5 </TD>
<TD >
&nbsp; CCNTV6 &nbsp; </TD>
<TD align=right>
    16 </TD>
<TD >
&nbsp;  4.35% &nbsp; </TD>
<TD >
For Your Eyes Only </TD>

</TR>
 <TR>
<TD align=right>
      6 </TD>
<TD >
&nbsp;      C &nbsp; </TD>
<TD align=right>
     2 </TD>
<TD >
&nbsp;  0.54% &nbsp; </TD>
<TD >
C Overnight </TD>

</TR>
 <TR>
<TD align=right>
      7 </TD>
<TD >
&nbsp;  GAYEL &nbsp; </TD>
<TD align=right>
     2 </TD>
<TD >
&nbsp;  0.54% &nbsp; </TD>
<TD >
Macajuel Time </TD>

</TR>
 <TR>
<TD align=right>
      8 </TD>
<TD >
&nbsp;    IBN &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.27% &nbsp; </TD>
<TD >
Press TV </TD>

</TR>
 <TR>
<TD align=right>
      9 </TD>
<TD >
&nbsp;   ACTS &nbsp; </TD>
<TD align=right>
     2 </TD>
<TD >
&nbsp;  0.54% &nbsp; </TD>
<TD >
ACTS - Family Education </TD>

</TR>
 <TR>
<TD align=right>
     10 </TD>
<TD >
&nbsp;   EWTN &nbsp; </TD>
<TD align=right>
     2 </TD>
<TD >
&nbsp;  0.54% &nbsp; </TD>
<TD >
Daily Mass: Our Lady </TD>

</TR>
 <TR>
<TD align=right>
     12 </TD>
<TD >
&nbsp;    WIN &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.27% &nbsp; </TD>
<TD >
WIN    </TD>

</TR>
</TABLE>

<BR><B>
00:30 - 01:00
</B>
<TABLE BORDER>
<TR>
 <TD> Channel </TD>
<TD> Call Letters </TD>
<TD>  Count </TD>
<TD> Percent  </TD>
<TD> Title   </TD>

</TR>
<TR></TR>
 <TR>
<TD align=right>
      1 </TD>
<TD >
&nbsp;   IETV &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.37% &nbsp; </TD>
<TD >
IETV   </TD>

</TR>
 <TR>
<TD align=right>
      3 </TD>
<TD >
&nbsp;   CNC3 &nbsp; </TD>
<TD align=right>
     3 </TD>
<TD >
&nbsp;  1.12% &nbsp; </TD>
<TD >
CNC3 Overnight </TD>

</TR>
 <TR>
<TD align=right>
      4 </TD>
<TD >
&nbsp;   TV 4 &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.37% &nbsp; </TD>
<TD >
TV 4   </TD>

</TR>
 <TR>
<TD align=right>
      5 </TD>
<TD >
&nbsp; CCNTV6 &nbsp; </TD>
<TD align=right>
    15 </TD>
<TD >
&nbsp;  5.63% &nbsp; </TD>
<TD >
For Your Eyes Only </TD>

</TR>
 <TR>
<TD align=right>
      6 </TD>
<TD >
&nbsp;      C &nbsp; </TD>
<TD align=right>
     4 </TD>
<TD >
&nbsp;  1.50% &nbsp; </TD>
<TD >
C Overnight </TD>

</TR>
 <TR>
<TD align=right>
      7 </TD>
<TD >
&nbsp;  GAYEL &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.37% &nbsp; </TD>
<TD >
Macajuel Time </TD>

</TR>
 <TR>
<TD align=right>
      9 </TD>
<TD >
&nbsp;   ACTS &nbsp; </TD>
<TD align=right>
     4 </TD>
<TD >
&nbsp;  1.50% &nbsp; </TD>
<TD >
ACTS - Family Education </TD>

</TR>
 <TR>
<TD align=right>
     12 </TD>
<TD >
&nbsp;    WIN &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.37% &nbsp; </TD>
<TD >
WIN    </TD>

</TR>
 <TR>
<TD align=right>
     14 </TD>
<TD >
&nbsp;     WI &nbsp; </TD>
<TD align=right>
     3 </TD>
<TD >
&nbsp;  1.12% &nbsp; </TD>
<TD >
Local Sports </TD>

</TR>
 <TR>
<TD align=right>
     15 </TD>
<TD >
&nbsp;    SYN &nbsp; </TD>
<TD align=right>
     1 </TD>
<TD >
&nbsp;  0.37% &nbsp; </TD>
<TD >
Synergy Nights </TD>

</TR>
</TABLE>
[download]

[reply]
[d/l]

Re^4: HTML Table to MYSQL DB

by Fiddler (Initiate) on Nov 03, 2011 at 18:26 UTC

Re^5: HTML Table to MYSQL DB

by choroba (Cardinal) on Nov 03, 2011 at 19:03 UTC

Some notes below your chosen depth have not been shown here

Re: HTML Table to MYSQL DB
by Anonymous Monk on Nov 10, 2011 at 10:10 UTC

See the xpath/jquery approach (follow the examples)

[reply]