| zeromancer |
05-08-2005 01:29 AM |
HTML Tables to MySQL database
I created this script when I had to use FileMaker Pro to export some data. Then I had to put it into MySQL. The only problem was the CSV wouldn't work like it was supposed to on FileMaker. But the HTML tables were great to work with.
PHP Code:
<?php
/* This script was developed to parse HTML
* tables exported by FileMaker Pro 5
* for insertion into a MySQL table.
* It assumes that each cell is on its
* own line and tags other than the table family are removed.
*/
ini_set('display_errors', '1');
error_reporting(E_STRICT);
include("mysql.inc.php"); //connects to my dB. you have to write your own.
$dir = "./data";
$files = scandir($dir);
foreach($files as $f) { // loops through the ./data folder and looks for files to parse.
if($f != "." && $f != "..") {
$in_file = "./data/".$f;
$table = preg_replace('/.htm$/','',$f);
$out = "";
$data = file($in_file);
$columns = "(";
$rep1 = array('/\s/','/\(/','/\)/');
$with1 = array('_','_','_');
foreach($data as $i) {
if(preg_match('/^<TABLE/',$i) ) { //This loop matches the initial <table> tag, which builds the CREATE TABLE query
$out = $out."CREATE TABLE IF NOT EXISTS `".$table."` (";
} elseif(preg_match('/<TH>(?P<head>[\D\S]+)<\/TH>/',$i,$matches)) { //Uses <TH> tags to name the table columns
$matches['head'] = preg_replace($rep1,$with1,$matches['head']); //removes spaces and other column name un-friendly characters
$matches['head'] = strtolower($matches['head']);
$columns = $matches['head'].",";
$out = $out."`".$matches['head']."` TINYTEXT NOT NULL,";
} elseif(preg_match('/^<\/TABLE>/',$i)) {
$out = preg_replace('/,$/',"",$out);
$out .= "\n) TYPE=INNODB";
}
}
$drop = "DROP TABLE IF EXISTS `".$table."`";
if(mysql_query($drop)) {
echo "Table `".$table."` dropped.<br />";
} else {
die(mysql_error());
}
if(mysql_query($out)) {
echo "Table `".$table."` created<br />\n";
} else {
die(mysql_error()."<p>".$out."</p>");
}
$ins = 0;
foreach($data as $i) {
if(preg_match('/^<TR>/',$i)) { //On each new row of the table (<TR>), a new INSERT statement is built
$insert = "INSERT INTO ".$table." VALUES (";
} elseif(preg_match('/<TD>(?P<cell>[\D\S]+)<\/TD>/',$i,$matches)) { //adds data from each cell to the insert statement
$test = $matches['cell'];
$rep2 = array('/<TABLE BORDER=1>+/','/<\/TABLE>/+','/<TD>+/','/<\/TD>+/','/<BR>+/','/\'/');
$with2 = array("","","",",","NULL","_");
if($res = preg_replace($rep2,$with2,$test) ) { // in the case of a small table within the cell, removes tags and replaces with ','
$insert .= "'".$res."'".","; // which will later be used to explode and implode for reading
} else {
$insert .= "'".$test."'".",";
}
} elseif(preg_match('/^<\/TR>/',$i)) { //looks for the end of the table row and closes the insert statement.
$insert = preg_replace('/,$/',"",$insert); //removes the trailing comma from the statement
$insert .= ")";
if(mysql_query($insert)) {
$ins++;
} else {
die(mysql_error());
}
unset($insert); //deletes the insert statement from memory to start with a new one.
}
}
echo $ins." inserts sucessfully executed.<br />\n";
}
}
?>
Some notes are listed in the comments, but any other questions are welcome. Just a note that you might miss: the <TH> tags are used to build the table. so if your table headers aren't made with them, your CREATE TABLE statement won't have any column names. Also, the tags must be capitalized. |
