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]
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);
?>
/**
* 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",...
"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");}
}
}
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>
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:
- the input file (datafile): here it is MyFile.csv
- you may hide some columns, to do this you have to:
- provide the number of hidden columns through the parameter number_hidden
- list then all titles (from the actual csv file first column contents) through the parameters hidden0, hidden1...
- you may have some columns as input fields, to do this you have to:
- provide the number of input columns through the parameter number_input
- list then all titles (from the actual csv file first column contents) through the parameters input0, input1...
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 ;-)
- Although a Java-based solution may sound nice, there are actually some big disadvantages to this:
- People who choose Wikka as a solution because they already know PHP or would be prepared to learn some, may be disappointed to also have to learn Java (which is, frankly, not as easy to learn as PHP); and one language is quite enough to deal with for a beginner. And apart from learning the language, not everyone has a Java compiler to build an applet either: A PHP solution could at least be "easily" adapted by anyone who needs a tweak.
- The solution requires an applet to be embedded rather than an action code - which is not something even experienced Wiki page authors are likely to be be familiar with (at the very least there should be an action "wrapper" which outputs the applet HTML element (or better: an object since applet is deprecated)).
- This solution requires the site visitor to have Java (JRE at least) installed and enabled and of the correct version (I just went through downloading and installing a new JRE just to be able to see a FreeMind mindmap example...); this would exclude many visitors fom seeing the data.
- The solution requires JavaScript which many site vistotors have diabled (or don't have available at all); relying on JavaScript for functionality is therefore not a good idea (although it's fine to use as enhancement of functionality that is provided without JavaScript. Again, this would exclude many visitors from seeing thr data.
- All that said, it may not be so hard to translate the code from Java to PHP, and would welcome a proposal so we can compare the two alternatives in the same language. --JavaWoman (who is not a Java expert ;-))
CategoryUserContributions
On the contrary, if you have a , in your file but use another seperator, the part after the comma will vanish.
And I have no clue why :(
while ($data = fgetcsv($id, filesize($filename)))
because unless specified fgetcsv assumes the default "seperator" is the comma (see http://ca.php.net/manual/en/function.fgetcsv.php).....I think you have to do something like
while ($data = fgetcsv($id, filesize($filename), $seperator))
so that the seperator is set for the function to what is set by the parameter in the action call.
Hope that helps. mike
Now that seems a bit silly to me - if you set header="on" why not actually do what it says: create a *header*? Just use th instead of td in that case!
But no real solution because it is a mess with more than a few cells
I am working on it. At the moment there is no link but how about a param like export="csv" file="myfile"? Anyway, there are some things to correct before i will post the code. (Like not adding the whole content to the file everytime the page is called :).
@jw: i have thought about your suggested way, would surely be easier, but i haven't tried out yet.