PHP Script: Convert Excel or tab-delimited file to html table
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);
}
?>
8 Responses to “PHP Script: Convert Excel or tab-delimited file to html table”
Jim said:
james said:
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!
Y.T.Lim said:
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.
james said:
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!
Y.T.Lim said:
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:
$input = preg_replace (’/\t/’ , TAB_REPLACEMENT , $inputlist);
$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.
james said:
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
tabs_to_table($input);
I’ll correct my comment as well. Thank you very much for the corrections.
Y.T. said:
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";
?>
james said:
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.
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