Changing the CSV record delimiter

1

We're generating reports in CSV format from JasperServer, but have a few recipients who need Windows line breaks as the record delimiter. Since this is only a minority, we'd like to embed this change in the jrxml templates for these reports instead of making this change globally on the server. I've found the property and have been able to change the record delimiter by adding the following property setting into the jrxml:

<property name="net.sf.jasperreports.export.csv.record.delimiter" value="\\r\\n"/>

The problem is that no matter how we've tried to specify the value, it always uses the literal value. Leaving it out we get the default \\n delimiter, but given the above line instead of using an ascii 13 and an ascii 10 we get a very literal "\\r\\n", like this:

"...end of record1\\r\\nstart of record2..."

I can't figure out how to indicate the use of non-printing characters. I've tried doubling and even quadrupling the backslash along with anything else I could think of, but if we provide this property it uses exactly the characters provided.

Does anyone know the syntax we need to use to provide non-printing characters to this property? Either that, or is this possibly a known issue fixed in a later version? (We're currently running JS 3.0)

Any insight would be greatly appreciated.

sth's picture
sth
7
Joined: Jan 20 2010 - 2:37pm
Last seen: 9 years 10 months ago

5 Answers:

2

You should use XML entities for the characters.

Regards,

Lucian

Code:
<property name="net.sf.jasperreports.export.csv.record.delimiter" value="&#x0D;&#x0A;"/></td></tr></tbody></table>
lucianc's picture
7156
Joined: Jul 17 2006 - 1:10am
Last seen: 3 days 11 hours ago
0

Thank you, Lucian. That does exactly what we were trying to do.

sth's picture
sth
7
Joined: Jan 20 2010 - 2:37pm
Last seen: 9 years 10 months ago
0

Hi Lucian

I have tried your solutions above with no success.  Just to check, when I click on a cube it gives me certain counts.  When I click on the total it open a new window which gives me an option to do an Excel export (csv) which needs to be semi-colon delimited.  Am I looking in the right place?

Thanks

 

Carlo

carloh's picture
2
Joined: May 18 2015 - 6:11am
Last seen: 4 years 6 months ago
0

The way that I do it (and I'm not sure that it's the best way, but it works) is:

Close the file in Jaspersoft Studio

Open it with Notepad++ (or your editor of choice)

Between <jasperReport .../> and <queryString ...> you can add <property .../> lines.  Here you want to add:
    <property name="net.sf.jasperreports.export.csv.record.delimiter" value="&#x0D;&#x0A;"/>

As Lucian explained you can't use \n because it will put the literal string.  You need to use XML entities.

Save the file.

Re-open it in Jaspersoft Studio (you can use recent files)

Preview and export, your csv should have CRLF at the end of the record (in Notepad++ verify with View - Show Symbol - Show End of Line)

Personally I also use these properties:

    <property name="net.sf.jasperreports.print.keep.full.text" value="true"/>
    <property name="net.sf.jasperreports.export.csv.field.delimiter" value="|"/>

The first one allows you to use small fields in your design and the full data will export without getting truncated.

The second one uses pipe delimiters instead of commas.

dkman123's picture
Joined: May 28 2015 - 10:20am
Last seen: 4 years 5 months ago
1

After many frustrating hours, trying to solve the exact same problem ... I have a "simple" solution:

  1. Open the "properties" for your project (<abc>.jrxml)
  2. Select Jaspersoft Studio
  3. Select Exporters
  4. Select CSV Exporter
  5. Choose whether you want to make this change globally (i.e. Workspace), or only for the "Project", or only for the "File"
  6. In the "Record Delimiter" field enter "\r\n" (without quotes)

I found that when entering "\n\r", as recommended by Jaspersoft ... then the .CSV file contains <LF><CR>, which is not properly interpreted/displayed by Windows ... causing the very long line without apparent breaks.

Jon

LuttermJ's picture
Joined: Feb 5 2018 - 1:10pm
Last seen: 1 year 9 months ago
Feedback