Showing the content of a csv-file


Last edited by NilsLindenberg:
Modified links pointing to docs server
Mon, 28 Jan 2008 00:14 UTC [diff]


if you save this as actions/showcsv.php, you have the usage:

{{showcsv file="uploads/file" seperator="," header="on" class="csvtableclass"}}

- file is the name of the file (needs to be on the same server as the wikka?)
- seperator is the seperator for the entries
- if you set header="on", the first line will be used as the table-header.
- class is the css-class for the table. Standard is csvtable.

use this only with trusted files or not at all till the possible security hole are closed (see below)!

To do
- test if its possible to include files from other servers. Perhaps better to allow only files in the upload-path of wikka (possible security hole).
- add "csvtable" css
- documentation!!

Notes
- The file now requieres the HandleCsvData library.
- html tags in a csv-file are stripped (note that everything between <?php ?> will not be shown, too)

<?php
/**
* Prints a table based on a csv-textfile.
*
* With this action a csv file, which has to be on the same server as the wikki, is read and its content presented in a table.
*
* @author Nils Lindenberg (http://wikka.jsnx.com/NilsLindenberg)
*
* @param    string file mandatory: the name of the file which should be shown
* @param    char delimeter  optional: the delimeter of the entries in the csv-file. Standard is ","
* @param    ? header optional: if set to "on", the first entry will be shown strong. Standard is "off";
*
*/


require_once('library/handlecsvdata.php');

// ***Get the params ***
$header= 'off';
$separator = ",";
$filename = $vars['file'].".csv";
$tableclass = $vars['tableclass'];
if ($vars['separator']) $separator = $vars['separator'];
if ($vars['header']) $header = $vars['header'];

// *** Get the data and print the table ***
if (is_array($table = GetCsvData($filename, $separator, $tableclass))) PrintCsvTable($table, $header);
?>



Alternate Java based solution

It is a great idea to be able to read csv files. I would like to propose an alternative re-using some Java code I built years ago.
The concept is just a bit different as the input file has to be in this form:
"Title for column 1","Title for column 2",...
"Row 1 data 1","Row 1 data 2",...
"Row 2 data 1","Row 2 data 2",...
 

This allows to have some data with the separator inside the data not being split and considered as two data.

import java.awt.*;
import java.awt.event.*;
import java.applet.*;
import java.io.*;
import java.util.*;
import netscape.javascript.JSObject;
import java.net.URL;

public class CsvReader extends Applet {

 private JSObject win;
 private int number_hidden;
 private int number_input;
 private String[] args =new String[258];
 private Hashtable hiddens= new Hashtable();
 private Hashtable inputs= new Hashtable();
 private String[] result = new String[1];

/* this method parses one data line from the input file and generates  HTML code. Some elements may have to be hidden or have to be inputable depending on the parameters used*/

private String parseLign(String value)
{
String table;
table="<tr>";
int compt=0;
int i;
String temp="";
 while((i=value.indexOf(",\""))!=-1)
      {
      temp = value.substring(1,i-1);
      value = value.substring(i+1);
      if (hiddens.containsKey(args[compt]))
        {
        compt++;
        continue;
        }
      if (inputs.containsKey(args[compt])) table+="<td> <INPUT type=text size=8 value="+temp+" ></td>";
      else table+="<td>"+temp+"</td>";
      compt++;
      }
  if (!hiddens.containsKey(args[compt]))
    {

    if (inputs.containsKey(args[compt])) table+="<td> <INPUT type=text size=8 value="+value.substring(1,value.length()-1)+" ></td>";
    else table+="<td>"+value.substring(1,value.length()-1)+"</td>";
    }
  table+="</tr>";
    return table;
 }


 /* this method parses the 1st data line (considered as titles) as generates a string table that contains all titles for all columns */

  private void parseArgs(String str) {
    int compt =0;
    int i;
    String temp;
    while((i=str.indexOf(",\""))!=-1)
      {
      temp = str.substring(1,i-1);
      str= str.substring(i+1);
      args[compt] = temp;
      compt++;
      }
    args[compt]=str.substring(1,str.length()-1);


  }


  /* this method uses the table of the column titles (built here above)  and the input parameters (elements to be hidden, elements to be input) stocked as hashtable to generate the HTML code for the titles*/

  private String parseTitle() {
    String str= "<TABLE border=\"2\" bordercolor=\"#0033CC\" bgcolor=\"#999999\"><TR>";
    int i =0;
    while (args[i]!=null)
      {
        if (hiddens.containsKey(args[i])) {i++;continue;}
        str += "<td>"+args[i]+"</td>";
        i++;
      }
    str+= "</tr>";
    return str;
  }

 //Applet initialisation
  public void init() {
    try
      {
      win = JSObject.getWindow(this);
      number_hidden=new Integer(getParameter("number_hidden")).intValue();
      number_input=new Integer(getParameter("number_input")).intValue();
      for (int i=0;i<number_hidden;i++)
        {
        hiddens.put(getParameter("hidden"+i),"hidden");
        }
      for (int i=0;i<number_input;i++)
        {
        inputs.put(getParameter("input"+i),"input");
        }
      URL url = new URL(getCodeBase(),getParameter("datafile"));
      BufferedReader br = new BufferedReader(new InputStreamReader(url.openStream()));
      parseArgs(br.readLine());
      String temp;
      result[0]= parseTitle();
      while ((temp=br.readLine())!=null)
        {
         result[0] += parseLign(temp);
        }
        result[0] += "</table>";
        win.call("display",result);
      }
   catch (Exception e)
    {showStatus("Error during initialisation");}


  }

}


Finally your HTML code should look like this:

<script LANGUAGE="JavaScript">
function display(str){document.body.insertAdjacentHTML('beforeEnd',str);}
</SCRIPT>
</head>
<body>
<APPLET
CODE = "CsvReader.class"
WIDTH = 1
HEIGHT = 1
HSPACE = 0
VSPACE = 0
ALIGN = center
MAYSCRIPT
>
<PARAM name=datafile value=data/MyFile.csv>
<PARAM name=number_hidden value=2>
<PARAM name=hidden0 value=TitleX>
<PARAM name=hidden1 value=TitleY>
<PARAM name=number_input value=3>
<PARAM name=input0 value=TitleA>
<PARAM name=input1 value=TitleB>
<PARAM name=input2 value=TitleC>

</APPLET>


As you can see, there is one line of javascript called by the java code (win.call("display",result);) to display the html code in your page.

How to use it?

The input file has to be formatted as explained above (in fact a csv file with all data inside double quotes and the first row being the column titles).
The applet needs a few parameters:

To Do

Test it intensively,
Decide what to do when input have been made in the input fields (replace the input file?),
Get the comments of a java expert: looking at the user nicknames in this wiki we should find some ;-)


CategoryUserContributions
There are 9 comments on this page. [Show comments]
Valid XHTML :: Valid CSS: :: Powered by WikkaWiki