<?xml version="1.0" encoding="UTF-8"?>

<sample>

  <name>csvdatasource</name>
  <title>CSV Data Source Sample</title>
  <description>Shows how the CSV data source and the CSV query executer implementations could be used to fill reports.</description>

  <mainFeature ref="csvdatasource"/>
  <mainFeature ref="csvqueryexecuter"/>
  <secondaryFeature name="datasources" sample="datasource" title="Data Sources"/>
  <secondaryFeature name="queryexecuters" sample="hibernate" title="Query Executers"/>
  
  <!-- csvdatasource -->
  
  <feature name="csvdatasource" title="CSV Data Source">
    <description>
How to fill a report using data from a CSV file.
    </description>
    <since>1.2.0</since>
    <documentedBy>
      <author>
    	<name>Sanda Zaharia</name>
    	<email>shertage@users.sourceforge.net</email>
      </author>
    </documentedBy>
  	<otherSample ref="datasource"/>
    <content>
<b>CSV Data Sources</b>
<br/>
<br/>
Report filling is one of the basic steps during the report generation. After the report compilation, 
significant report data are read from the report data source, or calculated from report expressions, 
and the generated <api href="net/sf/jasperreports/engine/JasperReport.html">JasperReport</api> object 
is filled section by section. 
<br/>
Data sources are very useful when data come as a set of structured records, either extracted from a 
relational database, or loaded from specific files. In order to become more familiar with data source 
objects please consult the <a href="../datasources.html#datasources">Data Sources</a> section.
<br/>
Sometimes data that users need to fill the report with is found in plain text files, in a 
certain format, such as the popular CSV (comma-separated value).
<br/>
JasperReports provides an implementation for such a data source, by wrapping the CSV 
data from a text file into a <api href="net/sf/jasperreports/engine/data/JRCsvDataSource.html">JRCsvDataSource</api>. 
The CSV data source usually needs to read a file from disk, or at least from an input stream. 
<br/>
CSV is a file format with very few formatting rules: data rows are separated by a record 
delimiter (text sequence) and fields inside each row are separated by a field delimiter 
(character). Fields containing delimiter characters can be placed inside quotes. If fields 
contain quotes themselves, these are duplicated (for example, <code>"John ""Doe"""</code> will be 
displayed as <code>John "Doe"</code>).
<br/>
The most common delimiters for CSV files are:
<ul>
<li>comma (<code>,</code>) - as field delimiter</li>
<li>newline (<code>\n</code>) - as record delimiter</li>
</ul>
Users can override these default values by calling <code>setFieldDelimiter(char)</code> and 
<code>setRecordDelimiter(String)</code> of the <code>JRCsvDataSource</code> class. 
<br/>
There are two categories of CSV files:
<ol>
<li>files with a header record containing column names, all the other records containing field values;</li>
<li>files without a header record; all records contain only field values. This is the default category.</li>
</ol>
For the files in the first category, column names are read from the first row in the CSV file. 
But the engine should be informed that the input file belongs to the 
first category. This can be done by calling the <code>setUseFirstRowAsHeader(true)</code> method of the <code>JRCsvDataSource</code>.
<br/>
If files belong to the second category (default), column names have to be provided separately, taking into account that 
the column names order should be the same as the column order in a data record. If this is not possible, 
the default naming convention is to use the <code>COLUMN_</code> prefix followed by the zero-based column index.
<br/>
For instance, if is known that a particular column is the third column in the record 
(index=2), then one could name the corresponding field "COLUMN_2" and use the 
column data without problems.
<br/> 
Another problem when working with CSV files is related to data types. Handling data types 
for fields in CSV data sources is special since the CSV file format 
does not provide such information. This kind of matter is solved by trying to match each field in 
the data source to its corresponding report field type. For number and date/time fields, 
converting text values to <code>java.lang.Number</code> and <code>java.util.Date</code> values respectively 
requires parsing using format objects. This is controlled by specifying the date and 
number format objects to be used with the <code>JRCsvDataSource</code> instance by calling its 
<code>setDateFormat(DateFormat)</code> and <code>setNumberFormat(NumberFormat)</code> methods 
before passing it to the report-filling process.
<br/>
<br/>
<b>CSV Data Source Example</b>
<br/>
<br/>
In our example data records are stored in the /data/CsvDataSource.txt file. Although the file extension is <code>.txt</code>, 
it contains structured data according to the CSV rules:
<pre><![CDATA[
  "Dallas",47,"Janet Fuller","445 Upland Pl.","Trial"
  "Lyon",38,"Andrew Heiniger","347 College Av.","Active"
  "Dallas",43,"Susanne Smith","2 Upland Pl.","Active"
  "Berne",22,"Bill Ott","250 - 20th Ave.","Active"
  "Boston",32,"Michael Ott","339 College Av.","Trial"
  "Dallas",4,"Sylvia Ringer","365 College Av.","Active"
  "Boston",23,"Julia Heiniger","358 College Av.","Active"
  "Chicago",39,"Mary Karsen","202 College Av.","Active"
  "Dallas",40,"Susanne Miller","440 - 20th Ave.","Trial"
  "Berne",9,"James Schneider","277 Seventh Av.","Active"
  "Dallas",36,"John Steel","276 Upland Pl.","Suspended"
  "Chicago",35,"George Karsen","412 College Av.","Suspended"
  "Dallas",37,"Michael Clancy","19 Seventh Av.","Deleted"
  "Lyon",2,"Anne Miller","20 Upland Pl.","Active"
  "Dallas",0,"Laura Steel","429 Seventh Av.","Active"
  "Lyon",28,"Susanne White","74 - 20th Ave.","Deleted"
  "Paris",5,"Laura Miller","294 Seventh Av.","Active"
  "Lyon",17,"Laura Ott","443 Seventh Av.","Active"
  "New York",46,"Andrew May","172 Seventh Av.","Active"
  "New York",44,"Sylvia Ott","361 College Av.","Active"
  "Dallas",19,"Susanne Heiniger","86 - 20th Ave.","Active"
  "Chicago",11,"Julia White","412 Upland Pl.","Active"
  "Dallas",10,"Anne Fuller","135 Upland Pl.","Active"
  "New York",41,"Bill King","546 College Av.","Deleted"
  "Oslo",45,"Janet May","396 Seventh Av.","Active"
  "Paris",18,"Sylvia Fuller","158 - 20th Ave.","Trial"
  "San Francisco",48,"Robert White","549 Seventh Av.","Active"
  "Paris",25,"Sylvia Steel","269 College Av.","Suspended"
  "San Francisco",7,"James Peterson","231 Upland Pl.","Active"
  "Oslo",42,"Robert Ott","503 Seventh Av.","Trial"
]]></pre>
The file has no header row with column names. Column names are set independently, as shown in the 
<code>getDataSource()</code> method in the /src/CsvDataSourceApp.java file: 
<pre><![CDATA[
  private static JRCsvDataSource getDataSource() throws JRException
  {
    String[] columnNames = new String[]{"city", "id", "name", "address", "state"};
    JRCsvDataSource ds = new JRCsvDataSource(JRLoader.getLocationInputStream("data/CsvDataSource.txt"));
    ds.setRecordDelimiter("\r\n");
    ds.setColumnNames(columnNames);
    return ds;
  }
]]></pre>
The five column names are: <code>city</code>, <code>id</code>, <code>name</code>, <code>address</code> and <code>state</code>, 
in this particular order. Field names are identical to the column names defined here.
<br/>
The <code>JRCsvDataSource</code> object prepared above is passed to the engine at fill time (see again the /src/CsvDataSourceApp.java file):
<pre><![CDATA[
  public void fill() throws JRException
  {
    long start = System.currentTimeMillis();
    //Preparing parameters
    Map parameters = new HashMap();
    parameters.put("ReportTitle", "Address Report");
    parameters.put("DataFile", "CsvDataSource.txt - CSV data source");
    Set states = new HashSet();
    states.add("Active");
    states.add("Trial");
    parameters.put("IncludedStates", states);

    JasperFillManager.fillReportToFile("build/reports/CsvDataSourceReport.jasper", parameters, getDataSource());
    System.err.println("Filling time : " + (System.currentTimeMillis() - start));
  }
]]></pre>
The <code>IncludedStates</code> parameter defined above is used for data filtering. Only records with <code>Active</code> 
or <code>Trial</code> states will be taken into account:
<br/>
<br/>
<code>
&#160;&#160;&lt;parameter name="IncludedStates" class="java.util.Set"/&gt;
<br/>
<br/>
&#160;&#160;...
<br/>
<br/>
&#160;&#160;&lt;filterExpression&gt;&lt;![CDATA[$P{IncludedStates}.contains($F{state})]]&gt;&lt;/filterExpression&gt;
</code>
<br/>
<br/>
<subtitle name="runSample">Running the Sample</subtitle>
<br/>
<br/>
Running the sample requires the <a href="http://ant.apache.org/">Apache Ant</a> library. Make sure that <code>ant</code> is already installed on your system (version 1.5 or later).
<br/>
In a command prompt/terminal window set the current folder to <code>demo/samples/csvdatasource</code> within the JasperReports source project and run the <code>&gt; ant test view</code> command.
<br/>
It will generate all supported document types containing the sample report in the <code>demo/samples/csvdatasource/build/reports</code> directory. 
<br/>
Then the report will open in the JasperReports internal viewer.
    </content>
  </feature>

  <!-- csvdatasource -->
  
  <feature name="csvqueryexecuter" title="CSV Query Executer">
    <description>
How to fill a report using the CSV query executer.
    </description>
    <since>4.0.0</since>
    <documentedBy>
      <author>
    	<name>Sanda Zaharia</name>
    	<email>shertage@users.sourceforge.net</email>
      </author>
    </documentedBy>
  	<otherSample ref="ejbql"/>
  	<otherSample ref="hibernate"/>
  	<otherSample ref="mondrian"/>
  	<otherSample ref="xmldatasource"/>
    <content>
<subtitle name="qryExecuter">Using The CSV Query Executer</subtitle>
<br/>
<br/>
The other (and highly recommended) option available for preparing a <api href="net/sf/jasperreports/engine/data/JRCsvDataSource.html">JRCsvDataSource</api> 
is to let the JasperReports built-in CSV query executer to prepare one for us. When the query language is set to <code>CSV</code> or <code>csv</code>, the 
CSV query executer registered for this language will take the responsibilty to create and populate the needed CSV data source. JasperReports ships with a 
default implementation of the CSV query executer, named <api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuter.html">JRCsvQueryExecuter</api>.
<br/>
In order to create a valid CSV data source this query executer needs:
<ul>
<li>To access the resource containing the CSV data. Depending on its type, the resource could be located using one of the following:
<ul>
<li><api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_READER">CSV_READER</api> parameter, 
if the resource is available as <code>java.io.Reader</code> object</li>
<li><api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_INPUT_STREAM">CSV_INPUT_STREAM</api> parameter, 
if the resource is available as <code>java.io.InpuStream</code> object</li>
<li><api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_URL">CSV_URL</api> parameter, 
if the resource is available is located at a given URL</li>
<li><api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_FILE">CSV_FILE</api> parameter, 
if the resource is available as <code>java.io.File</code> object</li>
<li><api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_SOURCE">CSV_SOURCE</api> String parameter or property, 
representing a valid path to the requested resource</li>
</ul>
</li>
<li>To know the charset encoding for the CSV data, in order to create a data source with an appropriate encoding. The encoding can be set using the 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_ENCODING">CSV_ENCODING</api> parameter or property.</li>
<li>To know the <code>java.util.Locale</code> associated with the CSV data, in order to create a properly localized data source. The <code>java.util.Locale</code> can be set using the 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_LOCALE">CSV_LOCALE</api> or 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_LOCALE_CODE">CSV_LOCALE_CODE</api>
parameter.</li>
<li>To know the <code>java.util.TimeZone</code> associated with the CSV data, in order to create a properly localized data source. The <code>java.util.TimeZone</code> can be set using the 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_TIMEZONE">CSV_TIMEZONE</api> or 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_TIMEZONE_ID">CSV_TIMEZONE_ID</api>
parameter.</li>
<li>To know the number format associated with the CSV data, in order to create proper <code>java.lang.Number</code> objects from numeric fields stored in the CSV data. The number format can 
be set using the <api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_NUMBER_FORMAT">CSV_NUMBER_FORMAT</api> or 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_NUMBER_PATTERN">CSV_NUMBER_PATTERN</api>
parameter.</li>
<li>To know the date format associated with the CSV data, in order to create proper <code>java.util.Date</code> objects from date fields stored in the CSV data. The date format can be set using the 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_DATE_FORMAT">CSV_DATE_FORMAT</api> or 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_DATE_PATTERN">CSV_DATE_PATTERN</api>
parameter.</li>
<li>To know the field delimiter character, in order to parse the CSV resource in a proper way. The field delimiter can be set using the 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_FIELD_DELIMITER">CSV_FIELD_DELIMITER</api> parameter or property. 
The default field delimiter is a comma.</li>
<li>To know the record delimiter sequence, in order to parse the CSV resource in a proper way. The record delimiter can be set using the 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_RECORD_DELIMITER">CSV_RECORD_DELIMITER</api> parameter or property.</li>
<li>To know the column order and column names, to be used when create the mapping between fields in the report template and fields in the data source. Column names can be set using the 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_COLUMN_NAMES">CSV_COLUMN_NAMES</api> parameter or property, 
or the <api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_COLUMN_NAMES_ARRAY">CSV_COLUMN_NAMES_ARRAY</api> parameter. In 
various situations the first row of the CSV resource contains no data, but column names. If so, the query executer will read the column names from this row if the 
<api href="net/sf/jasperreports/engine/query/JRCsvQueryExecuterFactory.html#CSV_USE_FIRST_ROW_AS_HEADER">CSV_USE_FIRST_ROW_AS_HEADER</api> 
parameter/property is set to <code>true</code>. In this case column names stored in <code>CSV_COLUMN_NAMES</code> or <code>CSV_COLUMN_NAMES_ARRAY</code> will be neglected.</li>
</ul>
Once all this input stuff is loaded, the query executer will lookup for the CSV resource to read and extract data to populate the requested 
<api href="net/sf/jasperreports/engine/data/JRCsvDataSource.html">JRCsvDataSource</api>.
<br/>
<br/>
<subtitle name="qryExecuterSample">The CSV Query Executer Sample</subtitle>
<br/>
<br/>
An example of CSV Query Executer is available in the <code>reports/CsvQueryExecuterReport.jrxml</code> template. 
<br/>
There are two properties set at report level:
<pre><![CDATA[
<property name="net.sf.jasperreports.csv.column.names" value="city, id, name, address, state"/>
<property name="net.sf.jasperreports.csv.source" value="data/CsvDataSource.txt"/>]]></pre>
The first one stores the column names, the last one stores a relative path to the CSV resource file.
<br/>
Further we see a report parameter containing the record delimiter sequence:
<pre><![CDATA[
<parameter name="net.sf.jasperreports.csv.record.delimiter" class="java.lang.String">
  <defaultValueExpression><![CDATA["\r\n"]] ></defaultValueExpression>
</parameter>]]></pre>
Notice also that the query language is set to <code>csv</code>, meaning that the CSV query executer will be enabled to prepare the data source for the report:
<pre><![CDATA[
<queryString language="csv"/>]]></pre>
Field names in the report are the same as column names set in the <code>net.sf.jasperreports.csv.column.names</code> property:
<pre><![CDATA[
<field name="id" class="java.lang.Integer"/>
<field name="name" class="java.lang.String"/>
<field name="address" class="java.lang.String"/>
<field name="city" class="java.lang.String"/>
<field name="state" class="java.lang.String"/>]]></pre>
In the <code>src/CsvDataSourceApp.java</code> one can see the absence of the data source or connection info at fill time:
<pre><![CDATA[
// query executer filling
{
  start = System.currentTimeMillis();
  Map parameters = new HashMap();
  parameters.put("ReportTitle", "Address Report");
  parameters.put("DataFile", "CsvDataSource.txt - CSV query executer");
  Set states = new HashSet();
  states.add("Active");
  states.add("Trial");
  parameters.put("IncludedStates", states);

  JasperFillManager.fillReportToFile("build/reports/CsvQueryExecuterReport.jasper", parameters);
  System.err.println("Report : CsvQueryExecuterReport.jasper. Filling time : " + (System.currentTimeMillis() - start));
}]]></pre>
This is because the report datasource will be provided by the query executer.
<br/>
To see the result, run the sample as shown <a href="#runSample">here</a>, and take a look at <code>CsvQueryExecuterReport.*</code> 
documents in the <code>build/reports</code> output folder.
    </content>
  </feature>

</sample>
