Converting HTML Table to a CSV automatically using PHP?

Question 1

I am just in need to convert a this html table automatically in csv using PHP. Can someone provide any idea how to do this? Thanks.

$table = '<table border="1">
<tr>
<th>Header 1</th>
<th>Header 2</th>
</tr>
<tr>
<td>row 1, cell 1</td>
<td>row 1, cell 2</td>
</tr>
<tr>
<td>row 2, cell 1</td>
<td>row 2, cell 2</td>
</tr>
</table>';

Guys, I just need $table to convert in only .csv file, which could be automatically generated using some PHP function. We can define path for that csv file to /test/home/path_to_csv

Question 2

You can use str_get_html http://simplehtmldom.sourceforge.net/

include "simple_html_dom.php";
$table = '<table border="1">
<tr>
<th>Header 1</th>
<th>Header 2</th>
</tr>
<tr>
<td>row 1, cell 1</td>
<td>row 1, cell 2</td>
</tr>
<tr>
<td>row 2, cell 1</td>
<td>row 2, cell 2</td>
</tr>
</table>';

$html = str_get_html($table);



header('Content-type: application/ms-excel');
header('Content-Disposition: attachment; filename=sample.csv');

$fp = fopen("php://output", "w");

foreach($html->find('tr') as $element)
{
        $th = array();
        foreach( $element->find('th') as $row)  
        {
            $th [] = $row->plaintext;
        }

        $td = array();
        foreach( $element->find('td') as $row)  
        {
            $td [] = $row->plaintext;
        }
        !empty($th) ? fputcsv($fp, $th) : fputcsv($fp, $td);
}


fclose($fp);

Question 3

I found an answer online and updated the question on this page to show others how it is done.

UPDATE - PROBLEM SOLVED

First off, I had mistakenly believed that the source table (the one in adjacent-lists format) needed to be altered to include a source node. This is not the case. Secondly, I found a class via BING that does the trick. I've altered it for PHP5 and converted the original author's mysql related bits to basic PHP. He was using some DB class. You can convert them to your own database abstraction class later if you want.

Obviously, if your "source table" has other columns that you want to move to the nested set table, you will have to adjust the write method in the class below.

Hopefully this will save someone else from the same problems in the future.

<?php

/**


--
-- Table structure for table `adjacent_table`
--

DROP TABLE IF EXISTS `adjacent_table`;
CREATE TABLE IF NOT EXISTS `adjacent_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `father_id` int(11) DEFAULT NULL,
  `category` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;

--
-- Dumping data for table `adjacent_table`
--

INSERT INTO `adjacent_table` (`id`, `father_id`, `category`) VALUES
(1, 0, 'Books'),
(2, 0, 'CD''s'),
(3, 0, 'Magazines'),
(4, 1, 'Hard Cover'),
(5, 1, 'Large Format'),
(6, 3, 'Vintage');

--
-- Table structure for table `nested_table`
--

DROP TABLE IF EXISTS `nested_table`;
CREATE TABLE IF NOT EXISTS `nested_table` (
  `lft` int(11) NOT NULL DEFAULT '0',
  `rgt` int(11) DEFAULT NULL,
  `id` int(11) DEFAULT NULL,
  `category` varchar(128) DEFAULT NULL,
  PRIMARY KEY (`lft`),
  UNIQUE KEY `id` (`id`),
  UNIQUE KEY `rgt` (`rgt`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

*/

    /**
     * @class   tree_transformer
     * @author  Paul Houle, Matthew Toledo
     * @created 2008-11-04
     * @url     http://gen5.info/q/2008/11/04/nested-sets-php-verb-objects-and-noun-objects/
     */
    class tree_transformer 
    {

        private $i_count;
        private $a_link;

        public function __construct($a_link) 
        {
            if(!is_array($a_link)) throw new Exception("First parameter should be an array. Instead, it was type '".gettype($a_link)."'");
            $this->i_count = 1;
            $this->a_link= $a_link;
        }

        public function traverse($i_id) 
        {
            $i_lft = $this->i_count;
            $this->i_count++;

            $a_kid = $this->get_children($i_id);
            if ($a_kid) 
            {
                foreach($a_kid as $a_child) 
                {
                    $this->traverse($a_child);
                }
            }
            $i_rgt=$this->i_count;
            $this->i_count++;
            $this->write($i_lft,$i_rgt,$i_id);
        }   

        private function get_children($i_id) 
        {
            return $this->a_link[$i_id];
        }

        private function write($i_lft,$i_rgt,$i_id) 
        {

            // fetch the source column
            $s_query = "SELECT * FROM `adjacent_table` WHERE `id`  = '".$i_id."'";
            if (!$i_result = mysql_query($s_query))
            {
                echo "<pre>$s_query</pre>n";
                throw new Exception(mysql_error());  
            }
            $a_source = array();
            if (mysql_num_rows($i_result))
            {
                $a_source = mysql_fetch_assoc($i_result);
            }

            // root node?  label it unless already labeled in source table
            if (1 == $i_lft && empty($a_source['category']))
            {
                $a_source['category'] = 'ROOT';
            }

            // insert into the new nested tree table
            // use mysql_real_escape_string because one value "CD's"  has a single '
            $s_query = "
                INSERT INTO `nested_table`
                (`id`,`lft`,`rgt`,`category`)
                VALUES (
                    '".$i_id."',
                    '".$i_lft."',
                    '".$i_rgt."',
                    '".mysql_real_escape_string($a_source['category'])."'
                )
            ";
            if (!$i_result = mysql_query($s_query))
            {
                echo "<pre>$s_query</pre>n";
                throw new Exception(mysql_error());  
            }
            else
            {
                // success:  provide feedback
                echo "<p>$s_query</p>n";
            }
        }
    }

    mysql_connect('localhost','USER','PASSWORD') or die(mysql_error());
    mysql_select_db('DATABASE') or die(mysql_error());

    // build a complete copy of the adjacency table in ram
    $s_query = "SELECT `id`,`father_id` FROM `adjacent_table`";
    $i_result = mysql_query($s_query);
    $a_rows = array();
    while ($a_rows[] = mysql_fetch_assoc($i_result));
    $a_link = array();
    foreach($a_rows as $a_row) 
    {
        $i_father_id = $a_row['father_id'];
        $i_child_id = $a_row['id'];
        if (!array_key_exists($i_father_id,$a_link)) 
        {
            $a_link[$i_father_id]=array();
        }
        $a_link[$i_father_id][]=$i_child_id;
    }

    $o_tree_transformer = new tree_transformer($a_link);
    $o_tree_transformer->traverse(0);

?>

Here is the output:

INSERT INTO nested_table (id,lft,rgt,category) VALUES ( '4', '3', '4', 'Hard Cover' )

INSERT INTO nested_table (id,lft,rgt,category) VALUES ( '5', '5', '6', 'Large Format' )

INSERT INTO nested_table (id,lft,rgt,category) VALUES ( '1', '2', '7', 'Books' )

INSERT INTO nested_table (id,lft,rgt,category) VALUES ( '2', '8', '9', 'CD's' )

INSERT INTO nested_table (id,lft,rgt,category) VALUES ( '6', '11', '12', 'Vintage' )

INSERT INTO nested_table (id,lft,rgt,category) VALUES ( '3', '10', '13', 'Magazines' )

INSERT INTO nested_table (id,lft,rgt,category) VALUES ( '0', '1', '14', 'ROOT' )

Question 4

Using fopen with w will create the file if does not exist:

$list = [
    ["Name" => "John", "Gender" => "M"],
    ["Name" => "Doe", "Gender" => "M"],
    ["Name" => "Sara", "Gender" => "F"]
];

$fp = fopen($filename, 'w');
//Write the header
fputcsv($fp, array_keys($list[0]));
//Write fields
foreach ($list as $fields) {
    fputcsv($fp, $fields);
}
fclose($fp);

If you don't like fputcsv and fopen you can use this alternative:

$list = [
    ["Name" => "John", "Gender" => "M"],
    ["Name" => "Doe", "Gender" => "M"],
    ["Name" => "Sara", "Gender" => "F"]
];

$csvArray = ["header" => implode (",", array_keys($list[0]))] + array_map(function($item) {
    return implode (",", $item);
}, $list);

file_put_contents($filename, implode ("n", $csvArray));

I hope this will help you.

Question 5

This is the way I would do this:

<?php
/*
   PDO named placeholders require that the array keys are matched
   so we have to prefix them with a colon : as in 'field' becomes ':field'
   the benefit here is the array key order is irrelevant,
   so your csv could have the headers in any order.
*/
function prefixPdoArray(array $array){
    return array_map(function($item){
        return ':'.$item;
    }, $array);
}

//PDO database driver
$dsn = 'mysql:dbname=testdb;host=127.0.0.1';
$user = 'dbuser';
$password = 'dbpass';

$db = new PDO($dsn, $user, $password);

//header array should match DB fields
$header_map = [
    'field1',
    'field2',
    'field3',
    'field4',
];

$placeholders = prefixPdoArray($header_map);

//prepare the query outside of the loop
$stmt = $db->prepare('INSERT INTO `tran_detail` (`'.implode('`,`', $header_map).'`)VALUES('.implode(',', $placeholders).')');

/*
    we can dynamically build the query because $header_map and $placeholders
    are "canned" data, but you could just type it out as well.

    if you do the SQL manually you can dump $header_map and $placeholders
    and manually create $default_map. You could also dump this function
    prefixPdoArray() and just move the array map to $headers.
    so it would be a bit more efficient, but I thought I would show you
    a proper way to build the query dynamically.
*/

$default_map = array_fill_keys($placeholders, '');

//read the first line
$headers = fgetcsv($source, 1000, ","); 
//$header_count = count($csv_headers); //for error chcking if needed

//prefix csv headers
$headers =  prefixPdoArray($headers);

while ($data = fgetcsv($source, 1000, ",")){      
    /*
        array combine will throw an error if the header length
        is different then the data length.
        this indicates a missing or extra delimiter in the csv file.
        you may or may not have to check for this condition  
        -------------------------------------
        if( $header_count != count($data) ){ //do something on error }
    */
    //map file data to file headers
    $csv_mapped = array_combine( $headers, $data);

    //map file row to database query
    $csv_mapped = array_replace($default_map, $csv_mapped );

    //execute the query
    $stmt->execute($csv_mapped);
}

fclose($source);

Note I can only do limited testing on this ( no DB or files ), so for testing and explanation purposes here is the code for testing the basic functionality.

<?php
function prefixPdoArray(array $array){
    return array_map(function($item){
        return ':'.$item;
    }, $array);
}

//header array should match DB fields
$header_map = [
    'field1',
    'field2',
    'field3',
    'field4',
];

$placeholders = prefixPdoArray($header_map);
echo str_pad(' Placeholders ', 45, '-', STR_PAD_BOTH)."n";
var_dump($placeholders);

//prepare the query
echo "n".str_pad(' Raw SQL ', 45, '-', STR_PAD_BOTH)."n";
echo 'INSERT INTO `tran_detail` (`'.implode('`,`', $header_map).'`)VALUES('.implode(',', $placeholders).')';

$default_map = array_fill_keys($placeholders, '');
echo "nn".str_pad(' Default Map ', 45, '-', STR_PAD_BOTH)."n";
var_dump($default_map);

//(CANNED TEST DATA) read the first line
//example data for testing ( missing field1 ), and field3 out of order
$headers =  [
    'field3',
    'field2',
    'field4',
];
//prefix headers with placeholders
$headers =  prefixPdoArray($headers);

echo "n".str_pad(' CSV headers ', 45, '-', STR_PAD_BOTH)."n";
var_dump($headers);

//while ($data = fgetcsv($source, 1000, ",")){
    //(CANNED TEST DATA) read the data line(s)
    //example data for testing ( missing field1 ), and field3 out of order
    $data = [
        'value3',
        'value2',
        'value4',
    ];
    echo "n".str_pad(' CSV data ', 45, '-', STR_PAD_BOTH)."n";
    var_dump($data); 

    $csv_mapped = array_combine( $headers, $data);
    echo "n".str_pad(' CSV mapped data ', 45, '-', STR_PAD_BOTH)."n";
    var_dump($csv_mapped); 

    $csv_mapped = array_replace($default_map, $csv_mapped );
    echo "n".str_pad(' CSV filled data ', 45, '-', STR_PAD_BOTH)."n";
    var_dump($csv_mapped); 
//}

Outputs

    --------------- Placeholders ----------------
array(4) {
  [0]=>   string(7) ":field1"
  [1]=>   string(7) ":field2"
  [2]=>   string(7) ":field3"
  [3]=>   string(7) ":field4"
}

------------------ Raw SQL ------------------
INSERT INTO `tran_detail` (`field1`,`field2`,`field3`,`field4`)VALUES(:field1,:field2,:field3,:field4)

---------------- Default Map ----------------
array(4) {
  [":field1"]=>   string(0) ""
  [":field2"]=>   string(0) ""
  [":field3"]=>   string(0) ""
  [":field4"]=>   string(0) ""
}

---------------- CSV headers ----------------
array(3) {
  [0]=>   string(7) ":field3"
  [1]=>   string(7) ":field2"
  [2]=>   string(7) ":field4"
}

----------------- CSV data ------------------
array(3) {
  [0]=>   string(6) "value3"
  [1]=>   string(6) "value2"
  [2]=>   string(6) "value4"
}

-------------- CSV mapped data --------------
array(3) {
  [":field3"]=>   string(6) "value3"
  [":field2"]=>   string(6) "value2"
  [":field4"]=>   string(6) "value4"
}

-------------- CSV filled data --------------
array(4) {
  [":field1"]=>   string(0) ""
  [":field2"]=>   string(6) "value2"
  [":field3"]=>   string(6) "value3"
  [":field4"]=>   string(6) "value4"
}

You can check it out here.

http://sandbox.onlinephpfunctions.com/code/ab868ac6c6fbf43d74cf62ef2907b0c72e1f59bf

The most important part in the output is the last 2 arrays, as you can see how we map the data to the file headers, and then use the $default_map to fill in any missing columns. You can put whatever defaults in there you need, such as null or what have you, but you'll have to do it manually instead of using $default_map = array_fill_keys($placeholders, '');

This should make it pretty self explanatory, if not feel free to ask.

Hopefully I got everything matched up between them and for the DB and file stuff, if not it should be really close. But this is quite a bit of fairly complex code, so it's not inconceivable I may have missed something.

The important thing is this will let you map out the CSV data in an elegant way, and avoid any SQL Injection nastyness.

Question 6

You should try with

tableToGrid('#GridView1', {
    rowNum: 10,
    pager: '#pager',
    rowList: [10,20,30]
});

You can also add more jqGrid options as the second parameter of tableToGrid. You can also get reference to the colModel of the jqGrid after conversion with respect of

var cm = myGrid.getGridParam("colModel");

and then modify some parameters of the column model.

UPDATED: for example with the following code you can set some column data as edittype: "select".

for (var i = 0, l=cm.length; i < l; i += 1) {
    var colModelColumn = cm[i];
    // search for the clolumn with the name colModelColumnName.
    // variables colModelColumnName and selectedOptions are defined
    // in another code fragment above this one
    if (colModelColumn.name === colModelColumnName) {
        jQuery.extend(colModelColumn, { edittype: "select",
                                        editoptions: { value: selectedOptions }});
        break;
    }
}

Converting HTML Table to a CSV automatically using PHP?

Answers

UPDATE - PROBLEM SOLVED

php,csv