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.
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?
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="
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.
After many frustrating hours, trying to solve the exact same problem ... I have a "simple" solution:
- Open the "properties" for your project (<abc>.jrxml)
- Select Jaspersoft Studio
- Select Exporters
- Select CSV Exporter
- Choose whether you want to make this change globally (i.e. Workspace), or only for the "Project", or only for the "File"
- 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.