Defect #7039

CSV export. Line breaks. Excel

Added by John Yani about 7 years ago. Updated over 2 years ago.

Status:ClosedStart date:2010-12-03
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Issues
Target version:-
Resolution:Wont fix Affected version:1.1.0

Description

When I make csv export of issues and open the result in Excel, line breaks (CR/LF) in description field are not correctly displayed.

Classeur1.csv Magnifier - Example CSV file (64 Bytes) Etienne Massip, 2011-03-22 17:30

csv_issue_description_eol.patch Magnifier - IssueHelper change (604 Bytes) Etienne Massip, 2011-03-26 16:34

csv_text_fields_eol.patch Magnifier - FasterCSV change (1.31 KB) Etienne Massip, 2011-03-26 16:34

Classeur1.csv Magnifier - CSV with CRLF in a cell (66 Bytes) Jean-Philippe Lang, 2011-03-27 19:39

excel2007.png - Display in Excel 2007 (2.2 KB) Jean-Philippe Lang, 2011-03-27 19:39

excel2010.png (3.05 KB) Rolf Stetter, 2011-04-28 17:58

faster_csv.rb.patch Magnifier - Patch that doesn't work (2.58 KB) Sven Müller, 2011-09-09 15:42


Related issues

Related to Redmine - Defect #7096: CSV Issues Export + Ruby 1.9 + Thin + non-ascii character... Closed 2010-12-11
Related to Redmine - Feature #7037: CSV export encoding and excel. UTF-8 and BOM Closed 2010-12-03
Duplicated by Redmine - Defect #7958: Irregular CSV file (caused by "Description" field) Closed 2011-03-22

History

#1 Updated by John Yani about 7 years ago

Open Office Calc BTW handles it fine.
Excel version: 2007

#2 Updated by Jonas Schulz almost 7 years ago

Please review this bug, it is still open in version 1.1. The CR/LF issue in the description field will prevent the correct import under excel 07.

Are there any other chance to drop out that field without modification of the issues_helper.rb?

Thank you in advance!

#3 Updated by Etienne Massip almost 7 years ago

  • Category set to Issues
  • Affected version (unused) set to 1.1.0
  • Affected version set to 1.1.0

#4 Updated by Etienne Massip over 6 years ago

Data of cells containing line breaks in Excel 2003 is outputted the following way in the resulting CSV file :
  • cell content is surrounded by quotes
  • line breaks are LF only

Here's an example (separator is semi-colon).

#5 Updated by Etienne Massip over 6 years ago

  • Target version set to Candidate for next major release

#6 Updated by Etienne Massip over 6 years ago

2 patches :
  1. either simply replace \r\n EOLs with \n in issue description (IssueHelper change)
  2. or replace \r\n EOLs with \n in all text fields (FasterCSV change)

#7 Updated by Jean-Philippe Lang over 6 years ago

Works fine for me with Excel 2007. The same file with CRLF and what it gives in Excel 2007:

Display in Excel 2007

#8 Updated by Etienne Massip over 6 years ago

Gosh, right.

Excel 2003 renders the \r with a square character inside the cell :/

So that's basically an Excel version support question...

#9 Updated by Etienne Massip over 6 years ago

Jonas, could you post a sample of a non-working Excel 2007 workbook, please ?

#10 Updated by Etienne Massip over 6 years ago

  • Target version deleted (Candidate for next major release)

#11 Updated by Rolf Stetter over 6 years ago

Jean Philippe,
this is the result with Excel 2010 when opening your sample CSV:

#12 Updated by Etienne Massip over 6 years ago

Just raise the height of line 3.

#13 Updated by Rolf Stetter over 6 years ago

ups.. you are right!
So, I am very interested in getting your fix released..
Don't want to start patching.

#14 Updated by Etienne Massip over 6 years ago

This issue should probably be closed as a "Won't fix" since Excel >= 2007 opens Redmine exported CSV files containing EOLs just fine and supporting old Excel 2003 seems quite unnecessary now.

My patch simply replace CRLF inside column data with LF so it can be opened with Excel 2003.

#15 Updated by Sven Müller over 6 years ago

In german Wikipedia there is a hint:

Trennzeichen ist stillschweigend Semikolon, wenn die CSV Datei nach ANSI-Norm gespeichert ist

Trennzeichen ist stillschweigend Tabulator, wenn die CSV Datei in Unicode-Format gespeichert ist

The delimiter semicolon is expected when file is in ANSI.
The delimiter TAB is expected when file is in Unicode.

So is there a way to configure Redmine, to select eigther comma or semicolon for cvs files?

#16 Updated by Sven Müller over 6 years ago

I#ve tried to path one file. But in exported.csv are still comma

Any idea why?

#17 Updated by Etienne Massip over 6 years ago

Sven Müller wrote:

I#ve tried to path one file. But in exported.csv are still comma

Any idea why?

Did you read the issue description?

#18 Updated by Ikon Ikon almost 5 years ago

Hello guys,
still problems for me in Excel 2010. You need to do this:

*Use ";" to separate fields (column separator)
*Use CR+LF for line break out of text fields (row separator)
*Use LF for line break inside text fields (these are line breaks that will break cell text on multiple lines)

In that case, Excel will be happy. Tell me please the script to edit in order to fix the bug.

Thanks!

#19 Updated by Sergey Prosin almost 5 years ago

Hi, lkon.
To fix column separator you could edit your regional settings in folder \config\locales. Keep in mind the language you use in interface. Tha name of parameter is general_csv_separator. I'm not a redmine expert ;) so I'm not informed about solutions of line breaks, but may be you could find somethind in these files too.

#20 Updated by Doan Phuoc almost 4 years ago

Hi all,
I used redmine 2.3.3 and i can't fix it. May be help me.
I want export csv whith vietnamese language.
Thanks

#21 Updated by Toshi MARUYAMA over 2 years ago

  • Status changed from New to Closed
  • Resolution set to Wont fix

Etienne Massip wrote:

This issue should probably be closed as a "Won't fix" since Excel >= 2007 opens Redmine exported CSV files containing EOLs just fine and supporting old Excel 2003 seems quite unnecessary now.

I agree.

CSV character encoding in this issue is duplicate of #11116.

Also available in: Atom PDF