Error Trying to Create a Table from a CSV

0

Hello, I kindly ask your help or advice regarding this issue, as I’m starting with JasperSoft Studio:

  1. I start to generate reports with CSV data as source for start small (I’m not looking obtain data from DBs or SQLs queries yet)
  2. I can generate a blank report from CSV data, but only if I drag the fields to de Detail1 section (and Jaspersoft Studio automatically generate items in the Column header section)
  3. However, if I use the same CSV data to create a table, and that table is placed in the Detail1 section, I receive the following compilation error “Query language not set”, even in the Data Adapter options I set “use query executor mode”.

 

Regards.

radw62's picture
5
Joined: Jun 9 2016 - 8:20am
Last seen: 2 years 9 months ago

7 Answers:

1

1. Click on the table.

2. Open up "Dataset and Query Dialog"

3. Select "csv" as Language

hozawa's picture
45069
Joined: Apr 24 2010 - 4:31pm
Last seen: 1 month 1 week ago
0

Hello, your answer worked for quit the error. However, the table neither any data appear in the preview.

Regards.

radw62's picture
5
Joined: Jun 9 2016 - 8:20am
Last seen: 2 years 9 months ago
0

There's probably some error in your report setting. Are you able to get data to display using database datasource? Try setting it up with database and switching over to csv after getting it working.

hozawa's picture
45069
Joined: Apr 24 2010 - 4:31pm
Last seen: 1 month 1 week ago
0

Hello, if I create the table according to the following tutorial: http://community.jaspersoft.com/wiki/tables-and-crosstabs-jaspersoft-studio, I get the same values through 830 pages:

 

However, with other report and CSV as data source, the report is completely empty.

Regards.

 

radw62's picture
5
Joined: Jun 9 2016 - 8:20am
Last seen: 2 years 9 months ago
1

Hi,

I am facing the same issue. I have followed the tutorial from http://community.jaspersoft.com/wiki/tables-and-crosstabs-jaspersoft-studio . The tutorial uses the sample database and then it works. Now I (and the original poster also) want to know how a table can be used with a csv file.

In the next example I have got a report which uses a csv file as datasource. I also want to have some extra details in the summary. Therefore I want to use a table, which must use the same csv file datasource. I have created the dataset with query string csv.  The Dialog "dataset and query" can connect to the csv file and shows the data at the Date preview tabs (in jaspersoft studio). So you should think the dataset is correct connected.

When printing a preview the table was not printed. It looks like the table was not printed, because he thinks the dataset is empty. To force the table to print I have set the property "When No Data type" to "All sections No Detail". Now the table (Header and footer) is printed, but the content remains empty.

I have got a test report to show the problem:

<?xml version="1.0" encoding="UTF-8"?>
<!-- Created with Jaspersoft Studio version 6.2.0.final using JasperReports Library version 6.2.0  -->
<!-- 2016-11-21T16:12:00 -->
<jasperReport xmlns="http://jasperreports.sourceforge.net/jasperreports" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports http://jasperreports.sourceforge.net/xsd/jasperreport.xsd" name="Factuur_Customer name 0" pageWidth="595" pageHeight="842" columnWidth="555" leftMargin="0" rightMargin="0" topMargin="0" bottomMargin="0" isSummaryWithPageHeaderAndFooter="true" uuid="013c93eb-5427-4be1-91cc-20de31640e40">
    <property name="com.jaspersoft.studio.data.defaultdataadapter" value="TestDataAdapter.xml"/>
    <style name="Table_TH" mode="Opaque" backcolor="#F0F8FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_CH" mode="Opaque" backcolor="#BFE1FF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <style name="Table_TD" mode="Opaque" backcolor="#FFFFFF">
        <box>
            <pen lineWidth="0.5" lineColor="#000000"/>
            <topPen lineWidth="0.5" lineColor="#000000"/>
            <leftPen lineWidth="0.5" lineColor="#000000"/>
            <bottomPen lineWidth="0.5" lineColor="#000000"/>
            <rightPen lineWidth="0.5" lineColor="#000000"/>
        </box>
    </style>
    <subDataset name="Dataset1" uuid="d5e1b818-d16c-4a48-b7e0-aec9506fa8c4">
        <property name="com.jaspersoft.studio.data.defaultdataadapter" value="Factuur_DataAdapter.xml"/>
        <parameter name="net.sf.jasperreports.csv.source" class="java.lang.String" isForPrompting="false"/>
        <parameter name="CSV_INPUT_STREAM" class="java.io.InputStream" isForPrompting="false"/>
        <parameter name="CSV_URL" class="java.net.URL" isForPrompting="false"/>
        <parameter name="CSV_FILE" class="java.io.File" isForPrompting="false"/>
        <parameter name="net.sf.jasperreports.csv.encoding" class="java.lang.String" isForPrompting="false"/>
        <parameter name="CSV_READER" class="java.io.Reader" isForPrompting="false"/>
        <parameter name="net.sf.jasperreports.csv.column.names" class="java.lang.String" isForPrompting="false"/>
        <parameter name="CSV_COLUMN_NAMES_ARRAY" class="java.lang.String[]" isForPrompting="false"/>
        <parameter name="CSV_DATE_FORMAT" class="java.text.DateFormat" isForPrompting="false"/>
        <parameter name="net.sf.jasperreports.csv.date.pattern" class="java.lang.String" isForPrompting="false"/>
        <parameter name="net.sf.jasperreports.csv.field.delimiter" class="java.lang.String" isForPrompting="false"/>
        <parameter name="CSV_NUMBER_FORMAT" class="java.text.NumberFormat" isForPrompting="false"/>
        <parameter name="net.sf.jasperreports.csv.number.pattern" class="java.lang.String" isForPrompting="false"/>
        <parameter name="net.sf.jasperreports.csv.record.delimiter" class="java.lang.String" isForPrompting="false"/>
        <parameter name="CSV_USE_FIRST_ROW_AS_HEADER" class="java.lang.Boolean" isForPrompting="false"/>
        <parameter name="CSV_LOCALE" class="java.util.Locale" isForPrompting="false"/>
        <parameter name="net.sf.jasperreports.csv.locale.code" class="java.lang.String" isForPrompting="false"/>
        <parameter name="CSV_TIMEZONE" class="java.util.TimeZone" isForPrompting="false"/>
        <parameter name="net.sf.jasperreports.csv.timezone.id" class="java.lang.String" isForPrompting="false"/>
        <queryString language="csv">
            <![CDATA[]]>
        </queryString>
        <field name="DESCRIPTION" class="java.lang.String"/>
        <field name="TARIFF" class="java.lang.String"/>
        <field name="VAT" class="java.lang.String"/>
        <field name="VATAMOUNT" class="java.lang.String"/>
        <field name="UNITPRICE" class="java.lang.String"/>
        <field name="QUANTITY" class="java.lang.String"/>
    </subDataset>
    <parameter name="DECIMAL_SEPARATOR" class="java.lang.Character" isForPrompting="false">
        <defaultValueExpression><![CDATA[',']]></defaultValueExpression>
    </parameter>
    <parameter name="DATE_PATTERN" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["dd-MM-yyyy"]]></defaultValueExpression>
    </parameter>
    <parameter name="TIME_PATTERN" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA["H:mm"]]></defaultValueExpression>
    </parameter>
    <parameter name="INV_NUMBER" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
    </parameter>
    <parameter name="INV_DATE" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
    </parameter>
    <parameter name="INV_ADDRESS" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
    </parameter>
    <parameter name="INV_REFERENCE" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
    </parameter>
    <parameter name="CUSTOMER_NAME" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
    </parameter>
    <parameter name="CUSTOMER_DEBNO" class="java.lang.String" isForPrompting="false">
        <defaultValueExpression><![CDATA[""]]></defaultValueExpression>
    </parameter>
    <parameter name="net.sf.jasperreports.csv.source" class="java.lang.String" isForPrompting="false"/>
    <parameter name="CSV_INPUT_STREAM" class="java.io.InputStream" isForPrompting="false"/>
    <parameter name="CSV_URL" class="java.net.URL" isForPrompting="false"/>
    <parameter name="CSV_FILE" class="java.io.File" isForPrompting="false"/>
    <parameter name="net.sf.jasperreports.csv.encoding" class="java.lang.String" isForPrompting="false"/>
    <parameter name="CSV_READER" class="java.io.Reader" isForPrompting="false"/>
    <parameter name="net.sf.jasperreports.csv.column.names" class="java.lang.String" isForPrompting="false"/>
    <parameter name="CSV_COLUMN_NAMES_ARRAY" class="java.lang.String[]" isForPrompting="false"/>
    <parameter name="CSV_DATE_FORMAT" class="java.text.DateFormat" isForPrompting="false"/>
    <parameter name="net.sf.jasperreports.csv.date.pattern" class="java.lang.String" isForPrompting="false"/>
    <parameter name="net.sf.jasperreports.csv.field.delimiter" class="java.lang.String" isForPrompting="false"/>
    <parameter name="CSV_NUMBER_FORMAT" class="java.text.NumberFormat" isForPrompting="false"/>
    <parameter name="net.sf.jasperreports.csv.number.pattern" class="java.lang.String" isForPrompting="false"/>
    <parameter name="net.sf.jasperreports.csv.record.delimiter" class="java.lang.String" isForPrompting="false"/>
    <parameter name="CSV_USE_FIRST_ROW_AS_HEADER" class="java.lang.Boolean" isForPrompting="false"/>
    <parameter name="CSV_LOCALE" class="java.util.Locale" isForPrompting="false"/>
    <parameter name="net.sf.jasperreports.csv.locale.code" class="java.lang.String" isForPrompting="false"/>
    <parameter name="CSV_TIMEZONE" class="java.util.TimeZone" isForPrompting="false"/>
    <parameter name="net.sf.jasperreports.csv.timezone.id" class="java.lang.String" isForPrompting="false"/>
    <field name="DESCRIPTION" class="java.lang.String"/>
    <field name="TARIFF" class="java.lang.Double">
        <fieldDescription><![CDATA[]]></fieldDescription>
    </field>
    <pageHeader>
        <band height="52"/>
    </pageHeader>
    <columnHeader>
        <band height="16">
            <frame>
                <reportElement mode="Opaque" x="40" y="0" width="515" height="16" backcolor="#E8E8E8" uuid="7c742394-2348-404b-8468-8060b217a3f0"/>
                <box>
                    <bottomPen lineWidth="1.0" lineColor="#C0C0C0"/>
                </box>
                <textField isStretchWithOverflow="true">
                    <reportElement x="435" y="0" width="80" height="16" forecolor="#000000" uuid="1fc4665a-d935-4361-a729-883b58d44b1a"/>
                    <box topPadding="2" leftPadding="3" bottomPadding="2" rightPadding="3"/>
                    <textElement textAlignment="Right">
                        <font fontName="Arial" size="10" isBold="true"/>
                    </textElement>
                    <textFieldExpression><![CDATA["Bedrag"]]></textFieldExpression>
                </textField>
            </frame>
        </band>
    </columnHeader>
    <detail>
        <band height="21">
            <frame>
                <reportElement x="40" y="0" width="435" height="16" uuid="caa61f36-2373-4f1b-83a9-1bdaaf969315"/>
                <box>
                    <bottomPen lineWidth="1.0" lineColor="#E8E8E8"/>
                </box>
                <textField isStretchWithOverflow="true">
                    <reportElement x="0" y="0" width="435" height="16" forecolor="#000000" uuid="04492601-0f29-4381-aad3-521be28a6d7b"/>
                    <box topPadding="2" leftPadding="3" bottomPadding="2" rightPadding="3"/>
                    <textElement textAlignment="Left" markup="html">
                        <font fontName="Arial" size="10" isBold="false"/>
                    </textElement>
                    <textFieldExpression><![CDATA[$F{DESCRIPTION}]]></textFieldExpression>
                </textField>
            </frame>
            <textField>
                <reportElement x="475" y="0" width="80" height="16" uuid="152bfdf9-599f-49cd-a4a3-c5a278fb0edb"/>
                <textElement textAlignment="Right"/>
                <textFieldExpression><![CDATA[$F{TARIFF}]]></textFieldExpression>
            </textField>
        </band>
    </detail>
    <pageFooter>
        <band height="26"/>
    </pageFooter>
    <summary>
        <band height="338" splitType="Stretch">
            <property name="com.jaspersoft.studio.unit.height" value="pixel"/>
            <componentElement>
                <reportElement x="40" y="10" width="270" height="120" uuid="1a912e0f-f66f-453c-842b-c5ae31615695">
                    <property name="com.jaspersoft.studio.layout" value="com.jaspersoft.studio.editor.layout.VerticalRowLayout"/>
                    <property name="com.jaspersoft.studio.components.autoresize.proportional" value="true"/>
                </reportElement>
                <jr:table xmlns:jr="http://jasperreports.sourceforge.net/jasperreports/components" xsi:schemaLocation="http://jasperreports.sourceforge.net/jasperreports/components http://jasperreports.sourceforge.net/xsd/components.xsd" whenNoDataType="AllSectionsNoDetail">
                    <datasetRun subDataset="Dataset1" uuid="c55cf82a-b6ec-440b-8444-fdf195c3af5f">
                        <dataSourceExpression><![CDATA[((net.sf.jasperreports.engine.data.JRCsvDataSource) $P{REPORT_DATA_SOURCE})]]></dataSourceExpression>
                    </datasetRun>
                    <jr:column width="135" uuid="c392b946-d762-48bf-9fc5-dbeefb8fbc5c">
                        <jr:columnHeader style="Table_CH" height="20">
                            <staticText>
                                <reportElement x="0" y="0" width="135" height="20" uuid="467f7255-7fc0-413b-aa23-74327d49c05c"/>
                                <text><![CDATA[DESCRIPTION]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:columnFooter style="Table_CH" height="20"/>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="135" height="30" uuid="5b02c5eb-0e8c-4a3c-a70e-77d8efffd644"/>
                                <textFieldExpression><![CDATA[$F{DESCRIPTION}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                    <jr:column width="135" uuid="5ec9dafe-1e97-495a-b983-a33a60cbe377">
                        <jr:columnHeader style="Table_CH" height="20">
                            <staticText>
                                <reportElement x="0" y="0" width="135" height="20" uuid="aa0f6ea6-24f7-4885-8e66-ebffa5a9c203"/>
                                <text><![CDATA[TARIFF]]></text>
                            </staticText>
                        </jr:columnHeader>
                        <jr:columnFooter style="Table_CH" height="20"/>
                        <jr:detailCell style="Table_TD" height="30">
                            <textField>
                                <reportElement x="0" y="0" width="135" height="30" uuid="ecc9f93f-4d1e-4ab3-aa6c-0037ee30132f"/>
                                <textFieldExpression><![CDATA[$F{TARIFF}]]></textFieldExpression>
                            </textField>
                        </jr:detailCell>
                    </jr:column>
                </jr:table>
            </componentElement>
        </band>
    </summary>
</jasperReport>

And this is my data adapter:

<?xml version="1.0" encoding="UTF-8" ?>
<csvDataAdapter class="net.sf.jasperreports.data.csv.CsvDataAdapterImpl"><name>Test data adapter</name><dataFile xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:type="repositoryDataLocation"><location>TestData.csv</location></dataFile><recordDelimiter>&#xd;
</recordDelimiter><fieldDelimiter>,</fieldDelimiter><useFirstRowAsHeader>true</useFirstRowAsHeader><locale xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:java="http://java.sun.com" xsi:type="java:java.lang.String">nl_NL</locale><timeZone xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:java="http://java.sun.com" xsi:type="java:java.lang.String">Europe/Berlin</timeZone><datePattern>yyyy-MM-dd H:mm:ss</datePattern><numberPattern>#.#</numberPattern><queryExecuterMode>false</queryExecuterMode><columnNames>DESCRIPTION</columnNames><columnNames>TARIFF</columnNames><columnNames>VAT</columnNames><columnNames>VATAMOUNT</columnNames><columnNames>UNITPRICE</columnNames><columnNames>QUANTITY</columnNames></csvDataAdapter>

And this is my csv file:

"DESCRIPTION","TARIFF","VAT","VATAMOUNT","UNITPRICE","QUANTITY"
"Artikel 1 omschrijving","100.0","21.0","21.0","100","1"
"Artikel 2 omschrijving","200.0","21.0","42.0","100","2"
"Artikel 2 omschrijving","200.0","21.0","42.0","100","2"
"Artikel 2 omschrijving","200.0","21.0","42.0","100","2"
"Artikel 2 omschrijving","200.0","21.0","42.0","100","2"
"Artikel 2 omschrijving","200.0","21.0","42.0","100","2"

Someone a solution?

Thanks.

rinse's picture
34
Joined: Jul 12 2011 - 6:40am
Last seen: 9 months 4 days ago
0

Hi Rinse,

 

I'm in the same situation. Did you find any solution?

alexjferreira94's picture
Joined: Aug 21 2017 - 6:49am
Last seen: 1 year 10 months ago
0

What I did was, I went on to the dataset, right-clicked and selected 'Connect to the Main Dataset' and then complete the dialog. Then thereafter went to the 'Dataset and Query' option to set the language to CSV. The preview then loads correctly displaying all the data.

nokuthula.mbatha98's picture
Joined: Oct 26 2017 - 10:54pm
Last seen: 1 year 5 months ago
Feedback