CSV is not `split`¶

Level: 201 · you are about to parse a CSV file with a regex

One line: split /,/ breaks the quoted "Warsaw, Mazovia" into two fields; Text::ParseWords, which ships with perl, keeps it whole but turns "the ""old"" town" into the old town, C:\temp into C:temp and an empty last field into undef — and returns nothing at all for a row with an apostrophe in it, or for a record with a quoted line break.

A CSV field can contain the separator, if it is quoted; a quote, if it is doubled; and a line break, if it is quoted. None of that is a regular language, and split sees none of it.

Measured¶

Verified output of csv_pl.pl — regenerated by tools/run_examples.py, never hand-typed.

--- plain:  1,Kraków,12.50
  split /,/, $line, -1    3: [1] [Kraków] [12.50]
  parse_line(',', 0, ...) 3: [1] [Kraków] [12.50]
--- a quoted comma:  2,"Warsaw, Mazovia",7.25
  split /,/, $line, -1    4: [2] ["Warsaw] [ Mazovia"] [7.25]
  parse_line(',', 0, ...) 3: [2] [Warsaw, Mazovia] [7.25]
--- a doubled quote:  3,"the ""old"" town",4.00
  split /,/, $line, -1    3: [3] ["the ""old"" town"] [4.00]
  parse_line(',', 0, ...) 3: [3] [the old town] [4.00]
--- an apostrophe:  4,Grandma's kitchen,1.00
  split /,/, $line, -1    3: [4] [Grandma's kitchen] [1.00]
  parse_line(',', 0, ...) 0: nothing at all
--- a backslash:  5,C:\temp,0.50
  split /,/, $line, -1    3: [5] [C:\temp] [0.50]
  parse_line(',', 0, ...) 3: [5] [C:temp] [0.50]
--- an empty last:  6,Gdańsk,
  split /,/, $line, -1    3: [6] [Gdańsk] []
  parse_line(',', 0, ...) 3: [6] [Gdańsk] undef

--- a quoted line break: one record, two lines ---
  line 1: 0: nothing at all
  line 2: 0: nothing at all
  line 3: 3: [8] [Opole] [1.00]

`split`¶

split /,/, $line, -1 is right for the rows that have no quotes, and wrong for the first one that does: the quoted comma makes four fields, with the quotes still attached to two of them. It leaves the doubled quotes doubled, too.

`Text::ParseWords`¶

parse_line(',', 0, $line) understands quotes, so Warsaw, Mazovia is one field. But its rules are a shell's, not CSV's:

Both " and ' are quotes. Grandma's kitchen opens a quote that never closes, and an unbalanced quote makes parse_line return an empty list: the whole row is gone, with no error.
A backslash escapes the next character, so C:\temp loses its backslash.
A doubled quote is two quoted strings side by side, not an escaped quote, so ""old"" loses its quotes.
An empty last field is undef, not '' — a warning waiting to happen in the next eq.

A record on two lines¶

A quoted field may contain a line break, which puts one record on two lines of the file. A line-by-line loop hands each half to the parser, both halves have an unbalanced quote, and both come back empty. Record 7 is not misread; it is not there.

What to do instead¶

Use a parser that implements CSV — Text::CSV ↗, from CPAN, which uses the compiled Text::CSV_XS when it is installed. (Not machine-checked: this library runs core modules only.)

use Text::CSV;

my $csv = Text::CSV->new({ binary => 1, auto_diag => 1 });
open my $fh, '<:encoding(UTF-8)', $path or die "$path: $!";
while (my $row = $csv->getline($fh)) {    # a whole record, across lines if it must
    ...
}

Use the same module to write CSV, so a field with a comma in it gets its quotes.

If you are coming from another language¶

Python. The csv module is in the standard library, and it handles quoted commas, doubled quotes and quoted line breaks; Perl's equivalent is on CPAN.
Java. The Java text library reaches the same verdict at the end of split has sharp edges ↗.
The encodings library. A BOM in a CSV ↗ — the three bytes Excel puts before the first header, which no field-splitting fixes.

CSV is not split¶