PHP Script: Convert Excel or tab-delimited file to html table
james on January 30th, 2008
Here’s a script which I wrote today while on the jorb. The purpose was so that someone could copy and paste from an excel spreadsheet into a textarea, click a button, and voila: an html table.
The nice thing was that when I pasted Excel data into the textarea, it was tab-delimited with line breaks. At that point, all I needed to do was some replacements and throw a table wrapper on it.
Here’s the code for the conversion function (sorry – i haven’t installed a php code parser yet to style this more nicely):
<?php
/**
* convert tab delimited file to html table
* @input string tab-delimited text
* found at http://james.revillini.com
* note: you are not required to keep the above copyright notice in this code.
*/
function tabs_to_table($input) {
//define replacement constants
define(‘TAB_REPLACEMENT’, ‘</td><td> ‘);
define(‘NEWLINE_REPLACEMENT’, ‘</td></tr><tr%s><td> ‘);
define(‘TABLE_BEGIN’, ‘<table><tr><td> ‘);
define(‘TABLE_END’, ‘</td><tr></table>’);</code>
//replace all tabs with end-cell, begin-cell
$input = preg_replace (’/\t/’ , TAB_REPLACEMENT , $input);
//split the list on linebreaks
$rows = preg_split (’/\r\n/’ , $input);
//replace all linebreaks with end-row, begin-row (with or without altRow class)
$output = ”;
foreach ($rows as $index => $row) {
$output .= $row . sprintf(NEWLINE_REPLACEMENT, ($index%2?”:’ class=”alt”‘));
}
//build table
$input = TABLE_BEGIN . $output . TABLE_END;
return ($input);
}
?>
April 9th, 2008 at 11:46 am
New to php, with some VB and Javascript experience. I’m trying to paste contact info, in an Excel spreadsheet, into my WordPress blog-static page.
Is the script above a function that I can call? Not for sure how to use it.
Thanks
July 27th, 2008 at 2:20 pm
Yes, it’s a snippet of php that could be pasted into your php script and then called like this:
function tabs_to_table($input);*tabs_to_table($input);Of course, input would have to be read in using php’s fread and such.
* correction suggested by Y.T. Lim – thanks!
August 2nd, 2008 at 5:40 am
I’m only writing php code occasionally. I tried out this piece of code and it didn’t work. Looks like there are quite a few faults in it. Based on my limited knowledge I managed to get it to work with a few changes:
<?php function tabs_to_table($input) { //define replacement constants define("TAB_REPLACEMENT", ""); define("NEWLINE_REPLACEMENT", ""); define("TABLE_BEGIN", ''); define("TABLE_END", ""); //replace all tabs with end-cell, begin-cell $input = preg_replace ('/\t/' , TAB_REPLACEMENT , $input); //split the list on linebreaks $rows = preg_split ("/\r\n/" , $input); //replace all linebreaks with end-row, begin-row (with or without altRow class) $output = ""; foreach ($rows as $index => $row) { $output .= $row . sprintf(NEWLINE_REPLACEMENT, ($index%2?"":' class="alt"')); } //build table $input = TABLE_BEGIN . $output . TABLE_END; return $input; } // Get Tab delimted text from file $filename = "Tab_delimited_file.txt"; $handle = fopen($filename, "r"); $contents = fread($handle, filesize($filename)); fclose($handle); // Start building the HTML file echo "\n"; echo "DEK Document Number Register\n"; echo "\n"; print(tabs_to_table($contents)); echo "\n"; echo "\n"; ?>It’s not hard to see that the code always adds a blank row at the end.
August 2nd, 2008 at 8:55 pm
Hi, Y.T. Lim. Sorry if there were faults, but I’m pretty sure what I posted is exactly what I used to do my conversion. Additionally, I think some of your code got eaten by wordpress, so if you’d like, please post again and wrap your code in [ code]…[ /code] tags (without the spaces) and I think it will come through. Otherwise, what you posted isn’t going to work for anyone.
Thanks for writing in. Always nice to have contributions and enhancements!
August 3rd, 2008 at 3:41 am
Sorry, I’m not familiar with wordpress either
When I cut-n-paste your original code, quite a few strange characters appeared in the text file. It took me a while to clean them up. Was that a problem of wordpress as well?
I’m curious about this piece of code:
$inputlist hasn’t been initialized anywhere, how does this work?
Also, you mentioned using “function tabs_to_table($input);” to invoke the function. Did you really mean “tabs_to_table($input);”?
/Y.T.
August 3rd, 2008 at 12:07 pm
Y.T. You are correct on both counts. $inputlist should have read $input. I think I probably made a snap decision to change the variable name in the function declaration and, for some reason, did not change it in the body of the function. I am going to go and correct that in the original post. Thanks for pointing that out.
And yes, I should have also said to invoke the function with
I’ll correct my comment as well. Thank you very much for the corrections.
August 5th, 2008 at 12:24 am
James, I’ll post the version that I’ve settled with, which I consider to be a slightly improved version than the one I posted before (no extra blank row at the end of the table). I think the code can be better written with the use of css.
/Y.T.
<?php function tabs_to_table($input) { //define replacement constants define('TAB_REPLACEMENT', ""); define('NEWLINE_REPLACEMENT', "\n"); define('ROW_BEGIN', ""); define('TABLE_BEGIN', "\n"); define('TABLE_END', "\n"); //replace all tabs with end-cell, begin-cell $input = preg_replace ("/\t/" , TAB_REPLACEMENT , $input); //split the list on linebreaks $rows = preg_split ("/\r\n/" , $input); //replace all linebreaks with end-row $output = ""; foreach ($rows as $index => $row) { if (strlen($row) > 0) { $output .= ROW_BEGIN . $row . NEWLINE_REPLACEMENT; } } //build table $input = TABLE_BEGIN . $output . TABLE_END; return $input; } // Get Tab delimited text from file $filename = "Tab_delimited_file.txt"; $handle = fopen($filename, "r"); $contents = fread($handle, filesize($filename)); fclose($handle); // Start building the HTML file echo "\n"; echo "Tab delimited Text to HTML Table\n"; echo "\n"; print(tabs_to_table($contents)); echo "\n"; echo "\n"; ?>August 5th, 2008 at 9:46 am
good stuff! I’ll scrap mine and put your rewrite into the original post so people will find this first, as it has some clear improvements. I think I’ll also make the file name a parameter to really make the function flexible. give me a few days to get to that.