CSV Plugin

This is a simple plugin that will display CSV data in table form. The data can either be inline between <csv> and </csv> or be read from a file in the media area using <csv wiki:test.csv></csv>. Both file and inline data may be specified at the same time, but right now that just generates two tables. If you are planning to use csv files in the media area, you will most likely need to include the .csv file extension in mime.local.conf in the conf area so you can upload .csv files. Something like this should get you started:

            csv     application/msexcel

The <csv> tag allows for two options to be specified as well as a file reference.

Cells that include a comma as part of the data are expected to be contained between double quotes, which is the typical behaviour of most systems when exporting .csv files.

You can install this plugin with the plugin manager. Use this download link (.zip, 4k).
Alternatively you can use the following link, which incorporates the changes from Gert (See below) as well is: download link (.zip, 4k).

To install, put the following PHP file in /lib/plugins/csv/syntax.php.

<?php
/*
 * CSV Plugin: displays a cvs formatted file or inline data as a table
 * Usage:
 * <csv[ options] file></csv>
 * or
 * <csv[ options]>
 * "a","b","c"
 * "1","2","3"
 * </csv>
 *
 * Where the file is a wiki reference to a file in the media area
 * If you use .csv as the file extension, you will most likely need to add
 * an entry to mime.local.conf in the conf area so you can upload .csv files.
 * Something like this should get you started:
 *      csv     application/msexcel 
 *
 * Using both the file and inline methods at the same time will result
 * in two tables being generated, any options defined will apply to both.
 *
 * The plugin allows for two options that can be set in the <csv> tag:
 *
 * hdr_rows=<n>, where <n> is the number or rows at the start of the
 * csv to encode as column headings. The default is 1.
 *
 * span_empty_cols=[0,1], this tells the plugin to create colspans for
 * each empty (two adjacent commas) cell following a cell with content.
 *
 * Embedded commas should be handled by surrounding the field 
 * with "" (which most systems do by default).  If you need to preserve
 * the "" around a field, then it should be surrounded by "" as well.
 *
 * @license    GPL 2 (http://www.gnu.org/licenses/gpl.html)
 * @author     Steven Danz (steven-danz@kc.rr.com)
 */
 
if(!defined('DOKU_INC')) define('DOKU_INC',realpath(dirname(__FILE__).'/../../').'/');
if(!defined('DOKU_PLUGIN')) define('DOKU_PLUGIN',DOKU_INC.'lib/plugins/');
require_once(DOKU_PLUGIN.'syntax.php');
 
/*
 * All DokuWiki plugins to extend the parser/rendering mechanism
 * need to inherit from this class
 */
class syntax_plugin_csv extends DokuWiki_Syntax_Plugin {
 
    /*
     * return some info
     */
    function getInfo(){
        return array(
            'author' => 'Steven Danz',
            'email'  => 'steven-danz@kc.rr.com',
            'date'   => '2005-11-08',
            'name'   => 'CSV Plugin',
            'desc'   => 'Displays a CSV file, or inline CSV data, as a table',
            'url'    => 'http://www.dokuwiki.org/plugin:csv',
        );
    }
 
    /*
     * What kind of syntax are we?
     */
    function getType(){
        return 'container';
    }
 
    /*
     * Where to sort in?
     */
    function getSort(){
        return 155;
    }
 
    /*
     * Paragraph Type
     */
    function getPType(){
        return 'block';
    }
 
    /*
     * Connect pattern to lexer
     */
    function connectTo($mode) {
      $this->Lexer->addEntryPattern("<csv[^>]*>(?=.*?\x3C/csv\x3E)",$mode,'plugin_csv');
    }
 
    function postConnect() {
      $this->Lexer->addExitPattern("</csv>",'plugin_csv');
    }
 
    /*
     * Handle the matches
     */
    function handle($match, $state, $pos, &$handler){
        if ($state == DOKU_LEXER_ENTER) {
            // default values for options
            $hdr_rows  = 1;
            $span_cols = 0;
            $file      = '';
 
            /* process possible options */
            $match = trim(substr($match, 4, -1));
            $data = preg_split("/\s/",$match,-1);
            while (count($data) > 0) {
                $entry = array_shift($data);
                if (preg_match("/^hdr_rows=([0-9]*)/",$entry,$matches)) {
                    $hdr_rows = $matches[1];
                } elseif (preg_match("/^span_empty_cols=([0-9]*)/",$entry,$matches)) {
                    $span_cols = $matches[1];
                } else {
                    $file = $entry;
                }
            }
 
            if ($file != '') {
                return array('file', $file, $hdr_rows, $span_cols);
            } else {
                return array('options', $hdr_rows, $span_cols);
            }
        } elseif ($state == DOKU_LEXER_UNMATCHED) {
            // clear out all the spaces, if anything is left, use it
            $clean = preg_replace('/\s/', '', $match);
            if ($clean != '') {
                return array('inline', $match);
            }
        }
        return array();
    }
 
    /*
     * Create output
     */
    function render($mode, &$renderer, $data) {
        global $csv_hdr_rows;   // global since the lexer_enter sets these in one match,
        global $csv_span_cols;  // then uses them in for the data (for inline data)
 
        if($mode == 'xhtml'){
            if (!isset($csv_hdr_rows)) $csv_hdr_rows = 1;
            if (count($data) > 0) {
                $type = array_shift($data);
                $process = 1;
                if ($type == 'file') {
                    // prevent caching to ensure the included data is always fresh
                    // and so we don't cache any errors that might be generated 
                    // from permission problems
                    $renderer->info['cache'] = FALSE;
 
                    $file = $data[0];
                    if (auth_quickaclcheck(getNS($file).':*') < AUTH_READ) {
                        $auth = 0;
                        $content = "";
                    } else {
                        $auth = 1;
                        $file = mediaFN($file);
                        $csv_hdr_rows = $data[1];
                        $csv_span_cols = $data[2];
                        if(@file_exists($file)) {
                            // grab the entire file as a string
                            $content = file_get_contents($file);
                        }
                    }
                } elseif ($type == 'options') {
                    $csv_hdr_rows = $data[0];
                    $csv_span_cols = $data[1];
                    $process = 0;
                } elseif ($type == 'inline') {
                    $content = $data[0];
                } else {
                    $renderer->doc .= "Not sure what this is about " . $type ;
                    $process = 0;
                }
 
                if ($process) {
                    // clean up the input data
                    // clear any trailing or leading empty lines from the data set
                    $content = preg_replace("/[\r\n]*$/","",$content);
                    $content = preg_replace("/^\s*[\r\n]*/","",$content);
 
                    // Not sure if PHP handles the DOS \r\n or Mac \r, so being paranoid
                    // and converting them if the exist to \n
                    $content = preg_replace("/\r\n/","\n",$content);
                    $content = preg_replace("/\r/","\n",$content);
 
                    if ($content != "") {
                        $renderer->table_open();
                        $row = 1;
                        while($content != "") {
                            $renderer->tablerow_open();
                            $cells = $this->csv_explode_row($content);
                            // some spreadsheet systems (i.e., excell) appear to 
                            // denote column spans with a completely empty cell
                            // (to adjacent commas) and an 'empty' cell will
                            // contain at least one blank space, so if the user
                            // asks, use that for attempting to span columns 
                            // together
                            $spans = array();
                            $span  = 0;
                            $current = 0;
                            foreach($cells as $cell) {
                                if ($cell == '' && $csv_span_cols) {
                                    $spans[$current] = 0;
                                    $spans[$span]++;
                                } else {
                                    $spans[$current] = 1;
                                    $span = $current;
                                }
                                $current++;
                            }
                            $current = 0;
                            foreach($cells as $cell) {
                                $cell = preg_replace('/\\\\\\\\/','<br>',$cell);
                                if ($spans[$current] > 0) {
                                    $align = 'left';
                                    if ($spans[$current] > 1) {
                                        $align = 'center';
                                    }
                                    if ($row <= $csv_hdr_rows) {
                                        $renderer->tableheader_open($spans[$current], $align);
                                    } else {
                                        $renderer->tablecell_open($spans[$current], $align);
                                    }
                                    $renderer->doc .= $cell;
                                    if ($row <= $csv_hdr_rows) {
                                        $renderer->tableheader_close();
                                    } else {
                                        $renderer->tablecell_close();
                                    }
                                }
                                $current++;
                            }
                            $renderer->tablerow_close();
                            $row++;
                        }
                        $renderer->table_close();
                    } else {
                        if ($type == 'file') {
                            if ($auth == 0) {
                                $renderer->doc .= "You do not have authorization to read " . $data[0];
                            } elseif(@file_exists($file)) {
                                $renderer->doc .= "Data file from " . $data[0] . " is empty";
                            } else {
                                $renderer->doc .= "Could not locate " . $data[0];
                            }
                        }
                    }
                }
                return true;
            }
        }
        return false;
    }
 
    // Explode CSV string, consuming it as we go
    // RFC 4180 claims that a CSV is allowed to have a cell enclosed in ""
    // that embeds a newline.  Convert those newlines to \\ (trying to keep
    // to the DokuWiki syntax) which we will key off of later in render()
    // as an embedded newline. 
    // Careful, there could be both embedded newlines, commas and quotes
    // One thing to remember is that a row must end with a newline
    function csv_explode_row(&$str, $delim = ',', $qual = "\"") {
        $len = strlen($str);
        $inside = false;
        $word = '';
        for ($i = 0; $i < $len; ++$i) {
            $next = $i+1;
            if ($str[$i]==$delim && !$inside) {
                $out[] = $word;
                $word = '';
            } elseif ($str[$i] == $qual && (!$inside || $next == $len || $str[$next] == $delim || $str[$next] == "\n")) {
                $inside = !$inside;
            } elseif ($str[$i] == $qual && $next != $len && $str[$next] == $qual) {
                $word .= $str[$i];
                $i++;
            } elseif ($str[$i] == "\n") {
                if ($inside) {
                    $word .= '\\\\';
                } else {
                    $str = substr($str, $next);
                    $out[] = $word;
                    return $out;
                }
            } else {
                $word .= $str[$i];
            }
        }
        $str = substr($str, $next);
        $out[] = $word;
        return $out;
    }
}
 
//Setup VIM: ex: et ts=4 enc=utf-8 :

• 2005-11-11 - Initial Version
  • Added options for hdr_rows and span_empty_cols
• 2005-11-20
  • Updated to allow for embedded newlines in double quoted cells
• 2005-12-08
  • Fixed use of ACL checks to check only the namespace of the media (ACLs don't apply to the media files themselves)

This plugin is very usefull for me. Thankyou for making it available.

Is it possible to addapt this plugin to use the standard media syntax of double curly braces, and use the file extension to identify and render it as a .csv table? That way, by adding the plugin and the mime type, no special syntax would be required.

I agree, that would be a nice change. - Allen

Question about plugin manager:

ermm, how? what URL do we use?

if logged in as administrator in your own wiki, you will see the link to the plugin manager (if installed and activated) on admin control panel — Freddy 2005-11-27 17:35

yes, but … what URL do we use?!?

okay… there is none… :/ then do it manually, like always. create syntax.php etc. … — Freddy 2005-11-27 19:20

This plugin is producing the following errors at the top of the page.

Warning: preg_grep(): Unknown modifier 'v' in /usr/share/dokuwiki/inc/auth.php on line 259

Warning: Invalid argument supplied for foreach() in /usr/share/dokuwiki/inc/auth.php on line 261

But the tables are created correctly. Is there anyway to turn these messages off.

—ES 2005-12-07

Sorry about all that. The plugin was using the ACL checks incorrectly and causing the trouble. Should be better now. I'm working finding space to host the .zip/.tgz file so you can use the plugin manager (as advertised).

It's not working: i see the plugin in the pluginmanager, but it returns only <csv> “a”,”b”,”c” “1”,”2”,”3” </csv> - means its not parsing it into a table. what can i do?
later if found another csv plugin that works for me http://wiki.ioslo.net/dokuwiki/csv — florian 2006-02-21

I also have a problem : example code that I copy pasted works fine, but my own csv contents does not get parsed into a table. Is there some type of format constraint (line feed / Carriage Return; ASCII only …) that is required ? On the other hand using a csv file rather than inlined content does work. Saved!

— alice 2006-04-21

I love this plugin, but is it possible to allow standard Wiki notations to be included in the CSV file being imported? For example, I'd love to use [[<InternalWikiLink>]] in fields of the table thus allowing me to link to content from the table created. Otherwise, I've got to repeat the information elsewhere to enable further content - which seems kinda pointless.

—- MarkB 2006-05-30

I would be very nice, if it would be possible to user Wiki notations – especially the [[<InternalWikiLink>]] syntax.

—- Patrick

I tried to make it render other wiki markup in table cells, but no luck. I added this to the top of the class def, using examples from other extensions:

    function getAllowedTypes()
        { return array('substition','protected','disabled','formatting','paragraphs');
    }

    function accepts($mode) {
        return ('plugin_csv' != $mode);
    }

With a simple table every row becomes a header row, as if each row is starting a new table. Line breaks (within quotes) and wiki markup also cause new header rows to start.

— 2007-7-18 - KyngChaos

— 2008-09-17 - Oneri

to use internal wiki markup change line

$renderer->doc .= $cell;

to

$renderer->nest(p_get_instructions($cell));

and comment (delete)

$cell = preg_replace('/\\\\\\\\/','<br>',$cell);

—

The plugin is useful but I'm having issues with it. It doesn't complete all the rows. Some of the far right columns are missing the “|”¹⁾ to end the row.

—- 8-30-06 | 2006 EDT |Rob

Delimiter option

I made a small hack to add the option 'delim'. For example, the delimiter can be switched to semicolon by using the following syntax <csv … delim=;></csv>

Here is the diff with the old syntax.php file :

--- syntax.php.old      2007-01-08 11:38:31.000000000 +0100
+++ syntax.php  2007-01-08 11:51:17.000000000 +0100
@@ -98,6 +98,7 @@
             // default values for options
             $hdr_rows  = 1;
             $span_cols = 0;
+            $delim = ',';
             $file      = '';
 
             /* process possible options */
@@ -109,15 +110,17 @@
                     $hdr_rows = $matches[1];
                 } elseif (preg_match("/^span_empty_cols=([0-9]*)/",$entry,$matches)) {
                     $span_cols = $matches[1];
+                } elseif (preg_match("/^delim=(.)/", $entry, $matches)) {
+                    $delim = $matches[1];
                 } else {
                     $file = $entry;
                 }
             }
 
             if ($file != '') {
-                return array('file', $file, $hdr_rows, $span_cols);
+                return array('file', $file, $hdr_rows, $span_cols, $delim);
             } else {
-                return array('options', $hdr_rows, $span_cols);
+                return array('options', $hdr_rows, $span_cols, $delim);
             }
         } elseif ($state == DOKU_LEXER_UNMATCHED) {
             // clear out all the spaces, if anything is left, use it
@@ -135,6 +138,7 @@
     function render($mode, &$renderer, $data) {
         global $csv_hdr_rows;   // global since the lexer_enter sets these in one match,
         global $csv_span_cols;  // then uses them in for the data (for inline data)
+        global $csv_delim;
 
         if($mode == 'xhtml'){
             if (!isset($csv_hdr_rows)) $csv_hdr_rows = 1;
@@ -156,6 +160,7 @@
                         $file = mediaFN($file);
                         $csv_hdr_rows = $data[1];
                         $csv_span_cols = $data[2];
+                        $csv_delim = $data[3];
                         if(@file_exists($file)) {
                             // grab the entire file as a string
                             $content = file_get_contents($file);
@@ -164,6 +169,7 @@
                 } elseif ($type == 'options') {
                     $csv_hdr_rows = $data[0];
                     $csv_span_cols = $data[1];
+                    $csv_delim = $data[2];
                     $process = 0;
                 } elseif ($type == 'inline') {
                     $content = $data[0];
@@ -188,7 +194,7 @@
                         $row = 1;
                         while($content != "") {
                             $renderer->tablerow_open();
-                            $cells = $this->csv_explode_row($content);
+                            $cells = $this->csv_explode_row($content, $csv_delim);
                             // some spreadsheet systems (i.e., excell) appear to 
                             // denote column spans with a completely empty cell
                             // (to adjacent commas) and an 'empty' cell will

— Michael 2007-01-08

Can you provide the new syntax.php ? It would be much more easier for all of us (the newbies).
Is it possible to use tab as delimiter ?
why ? Because when you cut and paste from excel you have a nice tab delimited table.

I love this plugin. But it sure would be nice if you could add a parameter for reducing font size and/or cell padding, because my table is very long and just a bit too wide for the average screen.

Fonts etc. are a matter of presentation (i.e. CSS) not markup (HTML). So just adjust your styling rules to accomplish what you want.
— Matthias Watermann 2007-02-08 10:02

—- steve 2007-05-16

I love it but also would love for wiki notation ie www.cnn.com to flow through it. -steve 2007-05-16

— Bob 14 Nov 2007

Excellent plugin. It appears to be limited to 3044 rows if the csv data is inserted into the page. This restriction is not present when the csv file is uploaded to the wiki and the csv code points at that. The advantage of importing the data onto the page is that it is automatically indexed and can therefore be searched.

Is this cap related to the csv plugin or is it a dokuwiki limitation? Can it be increased?

—- Corey Nov-17-07 Is is possible to point to the csv file that is not in the Media area such as http:\\some.com\somefir\somefile.csv? aliasonline@mac.com

—- Bob 18 Nov 2007

It is, but again the contents would not be indexed, which is what I'm really after.

—- w_m0zart 28 Aug 2008

To display special characters properly like the German 'umlaut' the advise from Gert (2008-04-16 see below) should be used. Add 'UTF-8' in the following function: htmlentities($type, ENT_QUOTES, 'UTF-8')
Despite modifying the syntax.php code and refreshing the DokuWiki-page, it still showed strange characters instead of the umlaut, just as if nothing changed. The solution for my modifications being visible, seemed to be that I just had to wrote something on that page and with that force the modified syntax.php to run.
A link for downloading this php file with the other changes from Gert as well is: Download Link. (.zip file).

— Gert, 2008-04-16

Regarding the open XSS security issue I would suggest to use the following quick fix (hopefully it will not break the functionality, but some playing around made me satisfied that each of the below denoted source code lines should be fixed). For further information on internationalizations add ”, ‘UTF-8′” or ”, ‘ISO-8859-1′”

htmlentities($type, ENT_QUOTES, 'UTF-8')

at the end of htmlentities as described at RSnakes blog entry HTMLSpecialChars Strikes Again.

171c171
<                     $renderer->doc .= "Not sure what this is about " . $type ;
---
>                     $renderer->doc .= "Not sure what this is about " . htmlentities($type, ENT_QUOTES) ;
224c224
<                                     $renderer->doc .= $cell;
---
>                                     $renderer->doc .= htmlentities($cell, ENT_QUOTES);
240c240
<                                 $renderer->doc .= "You do not have authorization to read " . $data[0];
---
>                                 $renderer->doc .= "You do not have authorization to read " . htmlentities($data[0], ENT_QUOTES);
242c242
<                                 $renderer->doc .= "Data file from " . $data[0] . " is empty";
---
>                                 $renderer->doc .= "Data file from " . htmlentities($data[0], ENT_QUOTES) . " is empty";
244c244
<                                 $renderer->doc .= "Could not locate " . $data[0];
---
>                                 $renderer->doc .= "Could not locate " . htmlentities($data[0], ENT_QUOTES);