Debian Codegraph @

Doc/library/csv.rst

raw
1 :mod:`!csv` --- CSV File Reading and Writing
2 ============================================
3
4 .. module:: csv
5 :synopsis: Write and read tabular data to and from delimited files.
6
7 .. sectionauthor:: Skip Montanaro <[email protected]>
8
9 **Source code:** :source:`Lib/csv.py`
10
11 .. index::
12 single: csv
13 pair: data; tabular
14
15 --------------
16
17 The so-called CSV (Comma Separated Values) format is the most common import and
18 export format for spreadsheets and databases. CSV format was used for many
19 years prior to attempts to describe the format in a standardized way in
20 :rfc:`4180`. The lack of a well-defined standard means that subtle differences
21 often exist in the data produced and consumed by different applications. These
22 differences can make it annoying to process CSV files from multiple sources.
23 Still, while the delimiters and quoting characters vary, the overall format is
24 similar enough that it is possible to write a single module which can
25 efficiently manipulate such data, hiding the details of reading and writing the
26 data from the programmer.
27
28 The :mod:`csv` module implements classes to read and write tabular data in CSV
29 format. It allows programmers to say, "write this data in the format preferred
30 by Excel," or "read data from this file which was generated by Excel," without
31 knowing the precise details of the CSV format used by Excel. Programmers can
32 also describe the CSV formats understood by other applications or define their
33 own special-purpose CSV formats.
34
35 The :mod:`csv` module's :class:`reader` and :class:`writer` objects read and
36 write sequences. Programmers can also read and write data in dictionary form
37 using the :class:`DictReader` and :class:`DictWriter` classes.
38
39 .. seealso::
40
41 :pep:`305` - CSV File API
42 The Python Enhancement Proposal which proposed this addition to Python.
43
44
45 .. _csv-contents:
46
47 Module Contents
48 ---------------
49
50 The :mod:`csv` module defines the following functions:
51
52
53 .. index::
54 single: universal newlines; csv.reader function
55
56 .. function:: reader(csvfile, /, dialect='excel', **fmtparams)
57
58 Return a :ref:`reader object <reader-objects>` that will process
59 lines from the given *csvfile*. A csvfile must be an iterable of
60 strings, each in the reader's defined csv format.
61 A csvfile is most commonly a file-like object or list.
62 If *csvfile* is a file object,
63 it should be opened with ``newline=''``. [1]_ An optional
64 *dialect* parameter can be given which is used to define a set of parameters
65 specific to a particular CSV dialect. It may be an instance of a subclass of
66 the :class:`Dialect` class or one of the strings returned by the
67 :func:`list_dialects` function. The other optional *fmtparams* keyword arguments
68 can be given to override individual formatting parameters in the current
69 dialect. For full details about the dialect and formatting parameters, see
70 section :ref:`csv-fmt-params`.
71
72 Each row read from the csv file is returned as a list of strings. No
73 automatic data type conversion is performed unless the :data:`QUOTE_NONNUMERIC` format
74 option is specified (in which case unquoted fields are transformed into floats).
75
76 A short usage example::
77
78 >>> import csv
79 >>> with open('eggs.csv', newline='') as csvfile:
80 ... spamreader = csv.reader(csvfile, delimiter=' ', quotechar='|')
81 ... for row in spamreader:
82 ... print(', '.join(row))
83 Spam, Spam, Spam, Spam, Spam, Baked Beans
84 Spam, Lovely Spam, Wonderful Spam
85
86 where :file:`eggs.csv` contains:
87
88 .. code-block:: text
89
90 Spam Spam Spam Spam Spam |Baked Beans|
91 Spam |Lovely Spam| |Wonderful Spam|
92
93
94 .. function:: writer(csvfile, /, dialect='excel', **fmtparams)
95
96 Return a writer object responsible for converting the user's data into delimited
97 strings on the given file-like object. *csvfile* can be any object with a
98 :meth:`~io.TextIOBase.write` method. If *csvfile* is a file object, it should be opened with
99 ``newline=''`` [1]_. An optional *dialect*
100 parameter can be given which is used to define a set of parameters specific to a
101 particular CSV dialect. It may be an instance of a subclass of the
102 :class:`Dialect` class or one of the strings returned by the
103 :func:`list_dialects` function. The other optional *fmtparams* keyword arguments
104 can be given to override individual formatting parameters in the current
105 dialect. For full details about dialects and formatting parameters, see
106 the :ref:`csv-fmt-params` section. To make it
107 as easy as possible to interface with modules which implement the DB API, the
108 value :const:`None` is written as the empty string. While this isn't a
109 reversible transformation, it makes it easier to dump SQL NULL data values to
110 CSV files without preprocessing the data returned from a ``cursor.fetch*`` call.
111 All other non-string data are stringified with :func:`str` before being written.
112
113 A short usage example::
114
115 import csv
116 with open('eggs.csv', 'w', newline='') as csvfile:
117 spamwriter = csv.writer(csvfile, delimiter=' ',
118 quotechar='|', quoting=csv.QUOTE_MINIMAL)
119 spamwriter.writerow(['Spam'] * 5 + ['Baked Beans'])
120 spamwriter.writerow(['Spam', 'Lovely Spam', 'Wonderful Spam'])
121
122 which writes :file:`eggs.csv` containing:
123
124 .. code-block:: text
125
126 Spam Spam Spam Spam Spam |Baked Beans|
127 Spam |Lovely Spam| |Wonderful Spam|
128
129
130 .. function:: register_dialect(name, /, dialect='excel', **fmtparams)
131
132 Associate *dialect* with *name*. *name* must be a string. The
133 dialect can be specified either by passing a sub-class of :class:`Dialect`, or
134 by *fmtparams* keyword arguments, or both, with keyword arguments overriding
135 parameters of the dialect. For full details about dialects and formatting
136 parameters, see section :ref:`csv-fmt-params`.
137
138
139 .. function:: unregister_dialect(name)
140
141 Delete the dialect associated with *name* from the dialect registry. An
142 :exc:`Error` is raised if *name* is not a registered dialect name.
143
144
145 .. function:: get_dialect(name)
146
147 Return the dialect associated with *name*. An :exc:`Error` is raised if
148 *name* is not a registered dialect name. This function returns an immutable
149 :class:`Dialect`.
150
151 .. function:: list_dialects()
152
153 Return the names of all registered dialects.
154
155
156 .. function:: field_size_limit()
157 field_size_limit(new_limit)
158
159 Returns the current maximum field size allowed by the parser. If *new_limit* is
160 given, this becomes the new limit.
161
162
163 The :mod:`csv` module defines the following classes:
164
165 .. class:: DictReader(f, fieldnames=None, restkey=None, restval=None, \
166 dialect='excel', *args, **kwds)
167
168 Create an object that operates like a regular reader but maps the
169 information in each row to a :class:`dict` whose keys are given by the
170 optional *fieldnames* parameter.
171
172 The *fieldnames* parameter is a :term:`sequence`. If *fieldnames* is
173 omitted, the values in the first row of file *f* will be used as the
174 fieldnames and will be omitted from the results. If
175 *fieldnames* is provided, they will be used and the first row will be
176 included in the results. Regardless of how the fieldnames are determined,
177 the dictionary preserves their original ordering.
178
179 If a row has more fields than fieldnames, the remaining data is put in a
180 list and stored with the fieldname specified by *restkey* (which defaults
181 to ``None``). If a non-blank row has fewer fields than fieldnames, the
182 missing values are filled-in with the value of *restval* (which defaults
183 to ``None``).
184
185 All other optional or keyword arguments are passed to the underlying
186 :class:`reader` instance.
187
188 If the argument passed to *fieldnames* is an iterator, it will be coerced to a :class:`list`.
189
190 .. versionchanged:: 3.6
191 Returned rows are now of type :class:`OrderedDict`.
192
193 .. versionchanged:: 3.8
194 Returned rows are now of type :class:`dict`.
195
196 A short usage example::
197
198 >>> import csv
199 >>> with open('names.csv', newline='') as csvfile:
200 ... reader = csv.DictReader(csvfile)
201 ... for row in reader:
202 ... print(row['first_name'], row['last_name'])
203 ...
204 Eric Idle
205 John Cleese
206
207 >>> print(row)
208 {'first_name': 'John', 'last_name': 'Cleese'}
209
210 where :file:`names.csv` contains:
211
212 .. code-block:: text
213
214 first_name,last_name
215 Eric,Idle
216 John,Cleese
217
218
219 .. class:: DictWriter(f, fieldnames, restval='', extrasaction='raise', \
220 dialect='excel', *args, **kwds)
221
222 Create an object which operates like a regular writer but maps dictionaries
223 onto output rows. The *fieldnames* parameter is a :mod:`sequence
224 <collections.abc>` of keys that identify the order in which values in the
225 dictionary passed to the :meth:`~csvwriter.writerow` method are written to file
226 *f*. The optional *restval* parameter specifies the value to be
227 written if the dictionary is missing a key in *fieldnames*. If the
228 dictionary passed to the :meth:`~csvwriter.writerow` method contains a key not found in
229 *fieldnames*, the optional *extrasaction* parameter indicates what action to
230 take.
231 If it is set to ``'raise'``, the default value, a :exc:`ValueError`
232 is raised.
233 If it is set to ``'ignore'``, extra values in the dictionary are ignored.
234 Any other optional or keyword arguments are passed to the underlying
235 :class:`writer` instance.
236
237 Note that unlike the :class:`DictReader` class, the *fieldnames* parameter
238 of the :class:`DictWriter` class is not optional.
239
240 If the argument passed to *fieldnames* is an iterator, it will be coerced to a :class:`list`.
241
242 A short usage example::
243
244 import csv
245
246 with open('names.csv', 'w', newline='') as csvfile:
247 fieldnames = ['first_name', 'last_name']
248 writer = csv.DictWriter(csvfile, fieldnames=fieldnames)
249
250 writer.writeheader()
251 writer.writerow({'first_name': 'Baked', 'last_name': 'Beans'})
252 writer.writerow({'first_name': 'Lovely', 'last_name': 'Spam'})
253 writer.writerow({'first_name': 'Wonderful', 'last_name': 'Spam'})
254
255 which writes :file:`names.csv` containing:
256
257 .. code-block:: text
258
259 first_name,last_name
260 Baked,Beans
261 Lovely,Spam
262 Wonderful,Spam
263
264
265 .. class:: Dialect
266
267 The :class:`Dialect` class is a container class whose attributes contain
268 information for how to handle doublequotes, whitespace, delimiters, etc.
269 Due to the lack of a strict CSV specification, different applications
270 produce subtly different CSV data. :class:`Dialect` instances define how
271 :class:`reader` and :class:`writer` instances behave.
272
273 All available :class:`Dialect` names are returned by :func:`list_dialects`,
274 and they can be registered with specific :class:`reader` and :class:`writer`
275 classes through their initializer (``__init__``) functions like this::
276
277 import csv
278
279 with open('students.csv', 'w', newline='') as csvfile:
280 writer = csv.writer(csvfile, dialect='unix')
281
282
283 .. class:: excel()
284
285 The :class:`excel` class defines the usual properties of an Excel-generated CSV
286 file. It is registered with the dialect name ``'excel'``.
287
288
289 .. class:: excel_tab()
290
291 The :class:`excel_tab` class defines the usual properties of an Excel-generated
292 TAB-delimited file. It is registered with the dialect name ``'excel-tab'``.
293
294
295 .. class:: unix_dialect()
296
297 The :class:`unix_dialect` class defines the usual properties of a CSV file
298 generated on UNIX systems, i.e. using ``'\n'`` as line terminator and quoting
299 all fields. It is registered with the dialect name ``'unix'``.
300
301 .. versionadded:: 3.2
302
303
304 .. class:: Sniffer()
305
306 The :class:`Sniffer` class is used to deduce the format of a CSV file.
307
308 The :class:`Sniffer` class provides two methods:
309
310 .. method:: sniff(sample, delimiters=None)
311
312 Analyze the given *sample* and return a :class:`Dialect` subclass
313 reflecting the parameters found. If the optional *delimiters* parameter
314 is given, it is interpreted as a string containing possible valid
315 delimiter characters.
316
317 If several delimiters fit the sample equally well ---
318 for example if both ``','`` and ``';'`` split every row consistently ---
319 the delimiters ``','``, ``'\t'``, ``';'``, ``' '`` and ``':'``
320 are preferred, in this order,
321 no matter how many times each of them occurs.
322
323 .. method:: has_header(sample)
324
325 Analyze the sample text (presumed to be in CSV format) and return
326 :const:`True` if the first row appears to be a series of column headers.
327 Inspecting each column, one of two key criteria will be considered to
328 estimate if the sample contains a header:
329
330 - the second through n-th rows contain numeric values
331 - the second through n-th rows contain strings where at least one value's
332 length differs from that of the putative header of that column.
333
334 Twenty-one rows after the header are sampled; if more than half of the
335 columns + rows meet the criteria, :const:`True` is returned.
336
337 .. note::
338
339 This method is a rough heuristic and may produce both false positives and
340 negatives.
341
342 An example for :class:`Sniffer` use::
343
344 with open('example.csv', newline='') as csvfile:
345 dialect = csv.Sniffer().sniff(csvfile.read(1024))
346 csvfile.seek(0)
347 reader = csv.reader(csvfile, dialect)
348 # ... process CSV file contents here ...
349
350
351 .. _csv-constants:
352
353 The :mod:`csv` module defines the following constants:
354
355 .. data:: QUOTE_ALL
356
357 Instructs :class:`writer` objects to quote all fields.
358
359
360 .. data:: QUOTE_MINIMAL
361
362 Instructs :class:`writer` objects to only quote those fields which contain
363 special characters such as *delimiter*, *quotechar*, ``'\r'``, ``'\n'``
364 or any of the characters in *lineterminator*.
365
366
367 .. data:: QUOTE_NONNUMERIC
368
369 Instructs :class:`writer` objects to quote all non-numeric fields.
370
371 Instructs :class:`reader` objects to convert all non-quoted fields to type :class:`float`.
372
373 .. note::
374 Some numeric types, such as :class:`bool`, :class:`~fractions.Fraction`,
375 or :class:`~enum.IntEnum`, have a string representation that cannot be
376 converted to :class:`float`.
377 They cannot be read in the :data:`QUOTE_NONNUMERIC` and
378 :data:`QUOTE_STRINGS` modes.
379
380 .. data:: QUOTE_NONE
381
382 Instructs :class:`writer` objects to never quote fields.
383 When the current *delimiter*, *quotechar*, *escapechar*, ``'\r'``, ``'\n'``
384 or any of the characters in *lineterminator* occurs in output data
385 it is preceded by the current *escapechar* character.
386 If *escapechar* is not set, the writer will raise :exc:`Error` if
387 any characters that require escaping are encountered.
388 Set *quotechar* to ``None`` to prevent its escaping.
389
390 Instructs :class:`reader` objects to perform no special processing of quote characters.
391
392 .. data:: QUOTE_NOTNULL
393
394 Instructs :class:`writer` objects to quote all fields which are not
395 ``None``. This is similar to :data:`QUOTE_ALL`, except that if a
396 field value is ``None`` an empty (unquoted) string is written.
397
398 Instructs :class:`reader` objects to interpret an empty (unquoted) field
399 as ``None`` and to otherwise behave as :data:`QUOTE_ALL`.
400
401 .. versionadded:: 3.12
402
403 .. data:: QUOTE_STRINGS
404
405 Instructs :class:`writer` objects to always place quotes around fields
406 which are strings. This is similar to :data:`QUOTE_NONNUMERIC`, except that if a
407 field value is ``None`` an empty (unquoted) string is written.
408
409 Instructs :class:`reader` objects to interpret an empty (unquoted) string as ``None`` and
410 to otherwise behave as :data:`QUOTE_NONNUMERIC`.
411
412 .. versionadded:: 3.12
413
414 The :mod:`csv` module defines the following exception:
415
416
417 .. exception:: Error
418
419 Raised by any of the functions when an error is detected.
420
421 .. _csv-fmt-params:
422
423 Dialects and Formatting Parameters
424 ----------------------------------
425
426 To make it easier to specify the format of input and output records, specific
427 formatting parameters are grouped together into dialects. A dialect is a
428 subclass of the :class:`Dialect` class containing various attributes
429 describing the format of the CSV file. When creating :class:`reader` or
430 :class:`writer` objects, the programmer can specify a string or a subclass of
431 the :class:`Dialect` class as the dialect parameter. In addition to, or instead
432 of, the *dialect* parameter, the programmer can also specify individual
433 formatting parameters, which have the same names as the attributes defined below
434 for the :class:`Dialect` class.
435
436 Dialects support the following attributes:
437
438
439 .. attribute:: Dialect.delimiter
440
441 A one-character string used to separate fields. It defaults to ``','``.
442
443
444 .. attribute:: Dialect.doublequote
445
446 Controls how instances of *quotechar* appearing inside a field should
447 themselves be quoted. When :const:`True`, the character is doubled. When
448 :const:`False`, the *escapechar* is used as a prefix to the *quotechar*. It
449 defaults to :const:`True`.
450
451 On output, if *doublequote* is :const:`False` and no *escapechar* is set,
452 :exc:`Error` is raised if a *quotechar* is found in a field.
453
454
455 .. attribute:: Dialect.escapechar
456
457 A one-character string used by the writer to escape characters that
458 require escaping:
459
460 * the *delimiter*, the *quotechar*, ``'\r'``, ``'\n'`` and any of the
461 characters in *lineterminator* are escaped if *quoting* is set to
462 :const:`QUOTE_NONE`;
463 * the *quotechar* is escaped if *doublequote* is :const:`False`;
464 * the *escapechar* itself.
465
466 On reading, the *escapechar* removes any special meaning from
467 the following character. It defaults to :const:`None`, which disables escaping.
468
469 .. versionchanged:: 3.11
470 An empty *escapechar* is not allowed.
471
472 .. attribute:: Dialect.lineterminator
473
474 The string used to terminate lines produced by the :class:`writer`. It defaults
475 to ``'\r\n'``.
476
477 .. note::
478
479 The :class:`reader` is hard-coded to recognise either ``'\r'`` or ``'\n'`` as
480 end-of-line, and ignores *lineterminator*. This behavior may change in the
481 future.
482
483
484 .. attribute:: Dialect.quotechar
485
486 A one-character string used to quote fields containing special characters,
487 such as the *delimiter* or the *quotechar*, or which contain new-line
488 characters (``'\r'``, ``'\n'`` or any of the characters in *lineterminator*).
489 It defaults to ``'"'``.
490 Can be set to ``None`` to prevent escaping ``'"'`` if *quoting* is set
491 to :const:`QUOTE_NONE`.
492
493 .. versionchanged:: 3.11
494 An empty *quotechar* is not allowed.
495
496 .. attribute:: Dialect.quoting
497
498 Controls when quotes should be generated by the writer and recognised by the
499 reader. It can take on any of the :ref:`QUOTE_\* constants <csv-constants>`
500 and defaults to :const:`QUOTE_MINIMAL` if *quotechar* is not ``None``,
501 and :const:`QUOTE_NONE` otherwise.
502
503
504 .. attribute:: Dialect.skipinitialspace
505
506 When :const:`True`, spaces immediately following the *delimiter* are ignored.
507 The default is :const:`False`. When combining ``delimiter=' '`` with
508 ``skipinitialspace=True``, unquoted empty fields are not allowed.
509
510
511 .. attribute:: Dialect.strict
512
513 When ``True``, raise exception :exc:`Error` on bad CSV input.
514 The default is ``False``.
515
516 .. _reader-objects:
517
518 Reader Objects
519 --------------
520
521 Reader objects (:class:`DictReader` instances and objects returned by the
522 :func:`reader` function) have the following public methods:
523
524 .. method:: csvreader.__next__()
525
526 Return the next row of the reader's iterable object as a list (if the object
527 was returned from :func:`reader`) or a dict (if it is a :class:`DictReader`
528 instance), parsed according to the current :class:`Dialect`. Usually you
529 should call this as ``next(reader)``.
530
531
532 Reader objects have the following public attributes:
533
534 .. attribute:: csvreader.dialect
535
536 A read-only description of the dialect in use by the parser.
537
538
539 .. attribute:: csvreader.line_num
540
541 The number of lines read from the source iterator. This is not the same as the
542 number of records returned, as records can span multiple lines.
543
544
545 DictReader objects have the following public attribute:
546
547 .. attribute:: DictReader.fieldnames
548
549 If not passed as a parameter when creating the object, this attribute is
550 initialized upon first access or when the first record is read from the
551 file.
552
553
554
555 Writer Objects
556 --------------
557
558 :class:`writer` objects (:class:`DictWriter` instances and objects returned by
559 the :func:`writer` function) have the following public methods. A *row* must be
560 an iterable of strings or numbers for :class:`writer` objects and a dictionary
561 mapping fieldnames to strings or numbers (by passing them through :func:`str`
562 first) for :class:`DictWriter` objects. Note that complex numbers are written
563 out surrounded by parens. This may cause some problems for other programs which
564 read CSV files (assuming they support complex numbers at all).
565
566
567 .. method:: csvwriter.writerow(row, /)
568
569 Write the *row* parameter to the writer's file object, formatted according
570 to the current :class:`Dialect`. Return the return value of the call to the
571 *write* method of the underlying file object.
572
573 .. versionchanged:: 3.5
574 Added support of arbitrary iterables.
575
576 .. method:: csvwriter.writerows(rows, /)
577
578 Write all elements in *rows* (an iterable of *row* objects as described
579 above) to the writer's file object, formatted according to the current
580 dialect.
581
582 Writer objects have the following public attribute:
583
584
585 .. attribute:: csvwriter.dialect
586
587 A read-only description of the dialect in use by the writer.
588
589
590 DictWriter objects have the following public method:
591
592
593 .. method:: DictWriter.writeheader()
594
595 Write a row with the field names (as specified in the constructor) to
596 the writer's file object, formatted according to the current dialect. Return
597 the return value of the :meth:`csvwriter.writerow` call used internally.
598
599 .. versionadded:: 3.2
600 .. versionchanged:: 3.8
601 :meth:`writeheader` now also returns the value returned by
602 the :meth:`csvwriter.writerow` method it uses internally.
603
604
605 .. _csv-examples:
606
607 Examples
608 --------
609
610 The simplest example of reading a CSV file::
611
612 import csv
613 with open('some.csv', newline='') as f:
614 reader = csv.reader(f)
615 for row in reader:
616 print(row)
617
618 Reading a file with an alternate format::
619
620 import csv
621 with open('passwd', newline='') as f:
622 reader = csv.reader(f, delimiter=':', quoting=csv.QUOTE_NONE)
623 for row in reader:
624 print(row)
625
626 The corresponding simplest possible writing example is::
627
628 import csv
629 with open('some.csv', 'w', newline='') as f:
630 writer = csv.writer(f)
631 writer.writerows(someiterable)
632
633 Since :func:`open` is used to open a CSV file for reading, the file
634 will by default be decoded into unicode using the system default
635 encoding (see :func:`locale.getencoding`). To decode a file
636 using a different encoding, use the ``encoding`` argument of open::
637
638 import csv
639 with open('some.csv', newline='', encoding='utf-8') as f:
640 reader = csv.reader(f)
641 for row in reader:
642 print(row)
643
644 The same applies to writing in something other than the system default
645 encoding: specify the encoding argument when opening the output file.
646
647 Registering a new dialect::
648
649 import csv
650 csv.register_dialect('unixpwd', delimiter=':', quoting=csv.QUOTE_NONE)
651 with open('passwd', newline='') as f:
652 reader = csv.reader(f, 'unixpwd')
653
654 A slightly more advanced use of the reader --- catching and reporting errors::
655
656 import csv, sys
657 filename = 'some.csv'
658 with open(filename, newline='') as f:
659 reader = csv.reader(f)
660 try:
661 for row in reader:
662 print(row)
663 except csv.Error as e:
664 sys.exit(f'file {filename}, line {reader.line_num}: {e}')
665
666 And while the module doesn't directly support parsing strings, it can easily be
667 done::
668
669 import csv
670 for row in csv.reader(['one,two,three']):
671 print(row)
672
673
674 .. rubric:: Footnotes
675
676 .. [1] If ``newline=''`` is not specified, newlines embedded inside quoted fields
677 will not be interpreted correctly, and on platforms that use ``\r\n`` line endings
678 on write an extra ``\r`` will be added. It should always be safe to specify
679 ``newline=''``, since the csv module does its own
680 (:term:`universal <universal newlines>`) newline handling.