Bug 395025

Summary:	csv writer generates invalid file in case field delimiter is used in any field
Product:	[Applications] kmymoney	Reporter:	Ralf Habacker <ralf.habacker>
Component:	exporter	Assignee:	KMyMoney Devel Mailing List <kmymoney-devel>
Status:	RESOLVED FIXED
Severity:	normal
Priority:	NOR
Version:	4.8.1
Target Milestone:	---
Platform:	Other
OS:	Microsoft Windows
Latest Commit:	https://commits.kde.org/kmymoney/638b0eacfdcc79cf67db747889f50381259fd703	Version Fixed In:	4.8.3, 5.0.2
Sentry Crash Report:
Bug Depends on:	395040
Bug Blocks:
Attachments:	test file

Description Ralf Habacker 2018-06-04 12:25:19 UTC

While working on bug 328050 it turned out that the csvwriter generates invalid csv files in case the field separator is used in any field. 

Examples: 
amount fields uses ',' as decimal delimiter and field separator is ','
memo fields contains ',' and field separator is ','
memo fields contains ';' and field separator is ';'

In those cases the related line in the csv file will be parsed by clients as having additional fields.

Datum,Zahlungsempfänger,Betrag,Konto/Kategorie,Notiz,Status,Nummer
2018-05-11,,-0,10,invest:securities,,,
2018-05-11,,0,00,invest:securities,,,

The decimal delimiter in column "Betrag" adds an additional column to the line

https://en.wikipedia.org/wiki/Comma-separated_values#RFC_4180_standard gives some hints how to setup csv files

For the amount fields it may help to use '.' as decimal separator and to disable the thousand separator:

Datum,Zahlungsempfänger,Betrag,Konto/Kategorie,Notiz,Status,Nummer
2018-05-11,,-0.10,invest:securities,,,
2018-05-11,,0.00,invest:securities,,,

or to quote the related values as shown below

Datum,Zahlungsempfänger,Betrag,Konto/Kategorie,Notiz,Status,Nummer
2018-05-11,,"-0,10",invest:securities,,,
2018-05-11,,"0,00",invest:securities,,,

Memo field values need to be quoted to avoid this issue.

Comment 1 Ralf Habacker 2018-06-04 13:16:46 UTC

see https://phabricator.kde.org/D13330 for the related review

Comment 2 Ralf Habacker 2018-06-05 08:13:50 UTC

Created attachment 113086 [details]
test file

Test file appended to validate fixed csv writer

Comment 3 Ralf Habacker 2018-06-05 10:21:16 UTC

Git commit 12fa3bbffb164737c8fd0b564c5596c4933a5d35 by Ralf Habacker.
Committed on 05/06/2018 at 10:21.
Pushed by habacker into branch '4.8'.

Fix 'csv writer generates invalid file in case field delimiter is used in any field'

This commit fixes all fields which may have field separators like
payee, acccount, category, memo, number and money related fields.

Additional fixes:
- the commit also handle double quotes inside string fields according
  RFC 4180

Note: The previous implementation removed single quotes from memo fields,
which is now applied to all string fields because all string fields are
formatted by the same method. If this could be removed, please file a
related request.
FIXED-IN:4.8.3

Test Plan: compiled and exported to csv on linux

Reviewers: #kmymoney, tbaumgart

Reviewed By: tbaumgart

Subscribers: tbaumgart

Differential Revision: https://phabricator.kde.org/D13330

Signed-off-by: Ralf Habacker <ralf.habacker@freenet.de>

M  +52   -43   kmymoney/plugins/csvexport/csvwriter.cpp
M  +2    -0    kmymoney/plugins/csvexport/csvwriter.h

https://commits.kde.org/kmymoney/12fa3bbffb164737c8fd0b564c5596c4933a5d35

Comment 4 Thomas Baumgart 2018-06-05 15:40:06 UTC

Git commit 638b0eacfdcc79cf67db747889f50381259fd703 by Thomas Baumgart.
Committed on 05/06/2018 at 15:33.
Pushed by tbaumgart into branch 'master'.

Allow field delimiter as character in CSV exports

This commit fixes all fields which may have field separators like
payee, acccount, category, memo, number and money related fields.

Additional fixes:
- the commit also handles double quotes inside string fields according
  to RFC 4180

Note: The previous implementation removed single quotes from memo
fields, which is now applied to all string fields because all string
fields are formatted by the same method. If this could be removed,
please file a related request.

Test Plan: compiled and exported to csv on linux

Reviewers: #kmymoney, tbaumgart

Reviewed By: tbaumgart

Subscribers: tbaumgart

Differential Revision: https://phabricator.kde.org/D13330

Signed-off-by: Ralf Habacker <ralf.habacker@freenet.de>

Cherry-picked from 12fa3bbffb164737c8fd0b564c5596c4933a5d35 and adjusted
for master

M  +52   -38   kmymoney/plugins/csv/export/csvwriter.cpp
M  +3    -0    kmymoney/plugins/csv/export/csvwriter.h

https://commits.kde.org/kmymoney/638b0eacfdcc79cf67db747889f50381259fd703