csv writer needs more quoting rules

@smontanaro

BPO	23041
Nosy	@smontanaro, @rhettinger, @bitdancer, @berkerpeksag, @serhiy-storchaka, @yoonghm, @erdnaxeli, @msetina
PRs	gh-67230: add quoting rules to csv module #29469
Files	issue23041.patch: Patch for resolving issue 23041. According to message 232560 and 232563 issue23041_test.patch: Test cases added for testing as behaviour proposed in message 232560

^{Note: these values reflect the state of the issue at the time it was migrated and might not reflect the current state.}

Show more details

GitHub fields:

assignee = 'https://github.com/smontanaro'
closed_at = None
created_at = <Date 2014-12-12.16:36:45.576>
labels = ['easy', 'type-feature', 'library', '3.11']
title = 'csv needs more quoting rules'
updated_at = <Date 2022-02-24.00:07:17.586>
user = 'https://bugs.python.org/samwyse'

bugs.python.org fields:

activity = <Date 2022-02-24.00:07:17.586>
actor = 'samwyse'
assignee = 'skip.montanaro'
closed = False
closed_date = None
closer = None
components = ['Library (Lib)']
creation = <Date 2014-12-12.16:36:45.576>
creator = 'samwyse'
dependencies = []
files = ['37444', '37445']
hgrepos = []
issue_num = 23041
keywords = ['patch', 'easy']
message_count = 26.0
messages = ['232560', '232561', '232563', '232596', '232607', '232630', '232676', '232677', '232681', '261141', '261146', '261147', '341460', '358461', '396621', '396641', '396642', '396643', '401607', '401608', '405951', '406013', '406015', '406017', '412463', '413867']
nosy_count = 11.0
nosy_names = ['skip.montanaro', 'rhettinger', 'samwyse', 'r.david.murray', 'berker.peksag', 'serhiy.storchaka', 'krypten', 'yoonghm', 'tegdev', 'erdnaxeli', 'msetina']
pr_nums = ['29469']
priority = 'normal'
resolution = None
stage = 'patch review'
status = 'open'
superseder = None
type = 'enhancement'
url = 'https://bugs.python.org/issue23041'
versions = ['Python 3.11']

Linked PRs

The csv module currently implements four quoting rules for dialects: QUOTE_MINIMAL, QUOTE_ALL, QUOTE_NONNUMERIC and QUOTE_NONE. These rules treat values of None the same as an empty string, i.e. by outputting two consecutive quotes. I propose the addition of two new rules, QUOTE_NOTNULL and QUOTE_STRINGS. The former behaves like QUOTE_ALL while the later behaves like QUOTE_NONNUMERIC, except that in both cases values of None are output as an empty field. Examples follow.

Current behavior (which will remain unchanged)

>>> csv.register_dialect('quote_all', quoting=csv.QUOTE_ALL)
>>> csv.writer(sys.stdout, dialect='quote_all').writerow(['foo', None, 42])
"foo","","42"

>>> csv.register_dialect('quote_nonnumeric', quoting=csv.QUOTE_NONNUMERIC)
>>> csv.writer(sys.stdout, dialect='quote_nonnumeric').writerow(['foo', None, 42])
"foo","",42

Proposed behavior

>>> csv.register_dialect('quote_notnull', quoting=csv.QUOTE_NOTNULL)
>>> csv.writer(sys.stdout, dialect='quote_notnull').writerow(['foo', None, 42])
"foo",,"42"

>>> csv.register_dialect('quote_strings', quoting=csv.QUOTE_STRINGS)
>>> csv.writer(sys.stdout, dialect='quote_strings').writerow(['foo', None, 42])
"foo",,42

As an enhancement, this could be added only to 3.5. The proposal sounds reasonable to me.

David: That's not a problem for me.

Sorry I can't provide real patches, but I'm not in a position to compile (much less test) the C implementation of _csv. I've looked at the code online and below are the changes that I think need to be made. My use cases don't require special handing when reading empty fields, so the only changes I've made are to the code for writers. I did verify that the reader code mostly only checks for QUOTE_NOTNULL when parsing. This means that completely empty fields will continue to load as zero-length strings, not None. I won't stand in the way of anyone wanting to "fix" that for these new rules.

typedef enum {
QUOTE_MINIMAL, QUOTE_ALL, QUOTE_NONNUMERIC, QUOTE_NONE,
QUOTE_STRINGS, QUOTE_NOTNULL
} QuoteStyle;

static StyleDesc quote_styles[] = {
{ QUOTE_MINIMAL, "QUOTE_MINIMAL" },
{ QUOTE_ALL, "QUOTE_ALL" },
{ QUOTE_NONNUMERIC, "QUOTE_NONNUMERIC" },
{ QUOTE_NONE, "QUOTE_NONE" },
{ QUOTE_STRINGS, "QUOTE_STRINGS" },
{ QUOTE_NOTNULL, "QUOTE_NOTNULL" },
{ 0 }
};

        switch (dialect->quoting) {
        case QUOTE_NONNUMERIC:
            quoted = !PyNumber_Check(field);
            break;
        case QUOTE_ALL:
            quoted = 1;
            break;
        case QUOTE_STRINGS:
            quoted = PyString_Check(field);
            break;
        case QUOTE_NOTNULL:
            quoted = field != Py_None;
            break;
        default:
            quoted = 0;
            break;
        }

" csv.QUOTE_MINIMAL means only when required, for example, when a\n"
" field contains either the quotechar or the delimiter\n"
" csv.QUOTE_ALL means that quotes are always placed around fields.\n"
" csv.QUOTE_NONNUMERIC means that quotes are always placed around\n"
" fields which do not parse as integers or floating point\n"
" numbers.\n"
" csv.QUOTE_STRINGS means that quotes are always placed around\n"
" fields which are strings. Note that the Python value None\n"
" is not a string.\n"
" csv.QUOTE_NOTNULL means that quotes are only placed around fields\n"
" that are not the Python value None.\n"

Samwyse, are these suggestions just based on ideas of what could be done or have you encountered real-world CSV data exchanges that couldn't be handled by the CSV module?

It doesn't look like a difficult change, but is it really needed? I guess my reaction is the same as Raymond's. Are there real-world uses where the current set of quoting styles isn't sufficient?

Used function PyUnicode_Check instead of PyString_Check

Yes, it's based on a real-world need. I work for a Fortune 500 company and we have an internal tool that exports CSV files using what I've described as the QUOTE_NOTNULL rules. I need to create similar files for re-importation. Right now, I have to post-process the output of my Python program to get it right. I added in the QUOTE_STRINGS rule for completeness. I think these two new rules would be useful for anyone wanting to create sparse CSV files.

If I understand correctly, your software needs to distinguish between

# wrote ["foo", "", 42, None] with quote_all in effect
"foo","","42",""

and

# wrote ["foo", None, 42, ""] with quote_nonnull in effect
"foo",,"42",""

so you in effect want to transmit some type information through a CSV file?

Skip, I don't have any visibility into how the Java program I'm feeding data into works, I'm just trying to replicate the csv files that it exports as accurately as possible. It has several other quirks, but I can replicate all of them using Dialects; this is the only "feature" I can't. The files I'm looking at have quoted strings and numbers, but there aren't any quoted empty strings. I'm using a DictWriter to create similar csv files, where missing keys are treated as values of None, so I'd like those printed without quotes. If we also want to print empty strings without quotes, that wouldn't impact me at all.

Besides my selfish needs, this could be useful to anyone wanting to reduce the save of csv files that have lots of empty fields, but wants to quote all non-empty values. This may be an empty set, I don't know.

Thanks for the update berker.peksag. I'm still not convinced that the csv module should be modified just so one user (sorry samwyse) can match the input format of someone's Java program. It seems a bit like trying to make the csv module type-sensitive. What happens when someone finds a csv file containing timestamps in a format other than the datetime.datetime object will produce by default? Why is None special as an object where bool(obj) is False?

I think the better course here is to either:

subclass csv.DictWriter, use dictionaries as your element type, and have its writerow method do the application-specific work.
define a writerow() function which does something similar (essentially wrapping csv.writerow()).

If someone else thinks this is something which belongs in Python's csv module, feel free to reopen and assign it to yourself.

The csv module is already type-sensitive (with QUOTE_NONNUMERIC). I agree, that we shouldn't modify the csv module just for one user and one program.

If a standard CVS library in Java (or other popular laguages) differentiates between empty string and null value when read from CSV, it would be a serious argument to support this in Python. Quick search don't find this.

@serhiy-storchaka

I'll try to summarise the state of this issue:

The proposed feature was to add more quoting rules to the csv writer. This has been implemented (with docs and tests)¹. @serhiy-storchaka requested in #67230 (comment) that the issue be held open:

Wait, #29469 only changes writer, not reader. There are no tests for reader with new quoting styles, so we cannot be sure that nothing is broken. [...]

@samwyse remarked in #67230 (comment):

[..] I can't really see an easy way [for csv reader] to distinguish between «,,» and «,"",». So unless someone more knowledgeable than I thinks that it's simple, I propose that we punt this down the road, making the two new quoting rules illegal for reader objects for the time being. [...]

@smontanaro #67230 (comment):

Thinking more about the problem, I think it's wrong to do anything to the reader. In its entire history, the reader has never done any sort of type inference. That would be exactly what distinguishing an empty field from a quoted empty field would be, a (small) bit of type inference. [...]

The better course at this point would be to adjust the docs to state that the two new quoting rules have no effect in the reader. [...]

@smontanaro #67230 (comment):

Would someone like to work on a PEP regarding type conversion in the reader?

I suggest to close this as completed (the csv writer feature was implemented) and to open up a new issue regarding what to do with the csv reader.

we should amend the issue title to reflect that the feature request was for the csv writer only ↩

the reader has never done any sort of type inference.

But it does.

>>> next(csv.reader(['123,"123"']))
['123', '123']
>>> next(csv.reader(['123,"123"'], quoting=csv.QUOTE_NONNUMERIC))
[123.0, '123']

If quoting style is QUOTE_NONNUMERIC, it returns non-quoted field as numeric. I expect the same for None.

As I already wrote, I suggest to summarise the needed reader changes in a new issue.

the reader has never done any sort of type inference.

But it does.
>>> next(csv.reader(['123,"123"']))
['123', '123']
>>> next(csv.reader(['123,"123"'], quoting=csv.QUOTE_NONNUMERIC))
[123.0, '123']
If quoting style is QUOTE_NONNUMERIC, it returns non-quoted field as numeric. I expect the same for None.

That's not really inferring the type in my book. It's effectively being told by the programmer that unquoted numeric strings are to be returned as numbers. If it was inferring the type, your first example would also spit out a float as the first value.

Right, and I suggest that QUOTE_NOTNULL is told by the programmer that unquoted empty strings are to be returned as None.

Right, and I suggest that QUOTE_NOTNULL is told by the programmer that unquoted empty strings are to be returned as None.

Can you please suggest this csv reader change in a new issue?

It is perhaps too late to make this change in 3.12, so we can open a new issue.

Opened #113732.

gh-67230: Add versionadded notes for QUOTE_NOTNULL and QUOTE_STRINGS (#…

pythongh-67230: Add versionadded notes for QUOTE_NOTNULL and QUOTE_ST…

[3.12] gh-67230: Add versionadded notes for QUOTE_NOTNULL and QUOTE_S…

pythongh-67230: Add versionadded notes for QUOTE_NOTNULL and QUOTE_ST…

Navigation Menu

Repository navigation

csv writer needs more quoting rules #67230

Linked PRs

71 remaining items

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

csv writer needs more quoting rules #67230

Description

Linked PRs

Activity

samwyse commented on Dec 12, 2014

bitdancer commented on Dec 12, 2014

samwyse commented on Dec 12, 2014

rhettinger commented on Dec 13, 2014

smontanaro commented on Dec 13, 2014

krypten commented on Dec 14, 2014

samwyse commented on Dec 15, 2014

smontanaro commented on Dec 15, 2014

samwyse commented on Dec 15, 2014

smontanaro commented on Mar 2, 2016

serhiy-storchaka commented on Mar 3, 2016

71 remaining items

erlend-aasland commented on Jan 5, 2024

Footnotes

serhiy-storchaka commented on Jan 5, 2024

erlend-aasland commented on Jan 5, 2024

smontanaro commented on Jan 5, 2024

serhiy-storchaka commented on Jan 5, 2024

erlend-aasland commented on Jan 5, 2024

serhiy-storchaka commented on Jan 5, 2024

serhiy-storchaka commented on Jan 5, 2024

Metadata

Metadata

Assignees

Labels

Projects

Milestone

Relationships

Development

Issue actions