csv module and None values

[JKPeck]

I'm trying to get the csv module (Python 2.6) to write data records
like Excel. The excel dialect isn't doing it. The problem is in
writing None values. I want them to result in just sequential commas
- ,, but csv treats None specially, as the doc says,

"To make it as easy as possible to interface with modules which
implement the DB API, the value None is written as the empty string."

I need strings to be quoted but not None values. Is there any way to
get around this special None treatment?

TIA,
Jon Peck

 

[JKPeck]

I'm trying to get the csv module (Python 2.6) to write data records
like Excel.  The excel dialect isn't doing it.  The problem is in
writing None values.  I want them to result in just sequential commas
- ,, but csv treats None specially, as the doc says,

"To make it as easy as possible to interface with modules which
implement the DB API, the value None is written as the empty string."

I need strings to be quoted but not None values.  Is there any way to
get around this special None treatment?

TIA,
Jon Peck

Solved the problem myself by giving a writer class to csv.writer that
looks for sentinel markers inserted in place of None and wipes them
out before writing to a file. Pretty ugly, but it works.

Regards,
Jon Peck

 

[Peter Otten]

I'm trying to get the csv module (Python 2.6) to write data records
like Excel. The excel dialect isn't doing it. The problem is in
writing None values. I want them to result in just sequential commas
- ,, but csv treats None specially, as the doc says,

"To make it as easy as possible to interface with modules which
implement the DB API, the value None is written as the empty string."

I need strings to be quoted but not None values. Is there any way to
get around this special None treatment?

If I understand you correctly the csv.writer already does what you want:

Python 2.6.2 (release26-maint, Apr 19 2009, 01:58:18)
[GCC 4.3.3] on linux2
Type "help", "copyright", "credits" or "license" for more information.

import csv, sys
w = csv.writer(sys.stdout)
w.writerow([1,None,2])

1,,2

just sequential commas, but that is the special treatment. Without it the
the None value would be converted to a string and the line would look like
this one:
.... def __str__(self): return "None"
....

w.writerow([1,LooksLikeNone(),2])

1,None,2

Peter

 

[John Yeung]

I'm trying to get the csv module (Python 2.6) to write data
records like Excel.  The excel dialect isn't doing it.  The
problem is in writing None values.  I want them to result
in just sequential commas - ,, but csv treats None specially,
as the doc says,

"To make it as easy as possible to interface with modules
which implement the DB API, the value None is written as
the empty string."

I need strings to be quoted but not None values.  Is there
any way to get around this special None treatment?

If you need all nonempty strings to be quoted unconditionally, then
you are not writing CSV records the way Excel writes CSV records. The
csv module is surprisingly good at behaving like Excel, and by default
writes strings with quotes only when needed (that is, when the string
itself contains commas, quotes, or newlines).

If you truly want Excel-style CSVs, just let the csv module do its
thing, as pointed out by Peter Otten.

From the sounds of it, you have specified QUOTE_NONNUMERIC or perhaps
QUOTE_ALL as the quoting property of the dialect you are using. If
so, and if this is really what you need except for *your* special None
treatment, then using a sentinel is in my opinion as good a way as any
to achieve that.

John

 

[John Yeung]

If I understand you correctly the csv.writer already does
what you want:

w.writerow([1,None,2])

1,,2

just sequential commas, but that is the special treatment.
Without it the None value would be converted to a string
and the line would look like this one:

1,None,2

No, I think he means he is getting

w.writerow([1,None,2])

1,"",2

He evidently wants to quote "all" strings, but doesn't want None to be
considered a string.

John

 

[JKPeck]

If I understand you correctly the csv.writer already does
what you want:

w.writerow([1,None,2])

1,,2

just sequential commas, but that is the special treatment.
Without it the None value would be converted to a string
and the line would look like this one:

1,None,2

No, I think he means he is getting

w.writerow([1,None,2])

1,"",2

He evidently wants to quote "all" strings, but doesn't want None to be
considered a string.

John

Exactly so. The requirement of the receiving program, which is out of
my control, is that all strings be quoted but a None in a numeric
field result in the ,, output rather than "". Excel quotes strings
conditionally, which doesn't do what is needed in this case. For
QUOTE_NONNUMERIC to quote None values makes some sense, but it gets in
the way of representing missing values in a numeric field. It would
be nice to have a choice here in the dialects.

I thought of replacing the None values with float(nan), since that has
a numeric type, but unfortunately that results in writing the string
(unquoted) nan for the value. So the sentinel approach seems to be
the best I can do.

Thanks,
Jon

 

[Peter Otten]

If I understand you correctly the csv.writer already does
what you want:

w.writerow([1,None,2])
1,,2

just sequential commas, but that is the special treatment.
Without it the None value would be converted to a string
and the line would look like this one:

1,None,2

No, I think he means he is getting

w.writerow([1,None,2])

1,"",2

He evidently wants to quote "all" strings, but doesn't want None to be
considered a string.

John

Exactly so. The requirement of the receiving program, which is out of
my control, is that all strings be quoted but a None in a numeric
field result in the ,, output rather than "". Excel quotes strings
conditionally, which doesn't do what is needed in this case. For
QUOTE_NONNUMERIC to quote None values makes some sense, but it gets in
the way of representing missing values in a numeric field. It would
be nice to have a choice here in the dialects.

I thought of replacing the None values with float(nan), since that has
a numeric type, but unfortunately that results in writing the string
(unquoted) nan for the value. So the sentinel approach seems to be
the best I can do.

How about:
.... def __str__(self): return ""
....

pseudo_none = N()
w = csv.writer(sys.stdout, quoting=csv.QUOTE_NONNUMERIC)
w.writerow([1, "foo", pseudo_none, "bar"])

1,"foo",,"bar"

Peter

 

[JKPeck]

If I understand you correctly the csv.writer already does
what you want:
w.writerow([1,None,2])
1,,2
just sequential commas, but that is the special treatment.
Without it the None value would be converted to a string
and the line would look like this one:
1,None,2
No, I think he means he is getting
w.writerow([1,None,2])
1,"",2
He evidently wants to quote "all" strings, but doesn't want None to be
considered a string.
John

Exactly so.  The requirement of the receiving program, which is out of
my control, is that all strings be quoted but a None in a numeric
field result in the ,, output rather than "".  Excel quotes strings
conditionally, which doesn't do what is needed in this case.  For
QUOTE_NONNUMERIC to quote None values makes some sense, but it gets in
the way of representing missing values in a numeric field.  It would
be nice to have a choice here in the dialects.

I thought of replacing the None values with float(nan), since that has
a numeric type, but unfortunately that results in writing the string
(unquoted) nan for the value.  So the sentinel approach seems to be
the best I can do.

How about:

...     def __str__(self): return ""
...>>> pseudo_none = N()

w = csv.writer(sys.stdout, quoting=csv.QUOTE_NONNUMERIC)
w.writerow([1, "foo", pseudo_none, "bar"])

1,"foo",,"bar"

Peter

Clever. Thanks,
Jon