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 https://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 thoughts on “PHP Script: Convert Excel or tab-delimited file to html table”

  1. 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

  2. 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!

  3. 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  ("/rn/"  , $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 Registern";
        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.

  4. 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!

  5. 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.

  6. 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.

  7. 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  ("/rn/"  , $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 Tablen";
        echo "n";
        print(tabs_to_table($contents));
        echo "n";
        echo "n";
    ?>
    
  8. 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.

Leave a Comment

Your email address will not be published. Required fields are marked *