Re: regular expression (search and destroy)

Quoting from the excellent "Perl Cookbook" -

Comma-seperated input is a deceptive and complex format. It sounds simple but involves a fairly complex escaping systembecause the fields themselves can contain commas. This makes pattern matching a solution complex and rules out the simple split /,/ .

This sums up the situation pretty well. There is no definition of a CSV format and something that works with one data format may not work with another. The book goes on to provide the following example code which I have modified to trim whitespace. This should work with Excel generated CSV files.

#! /usr/bin/perl
#
use strict;

while (<DATA>)
{
    chomp; # avoid matching against newline
    my $words = join "=>", parse_csv($_);
    print "$words\n";
}

sub parse_csv
{
    my $text = shift;      # record containing comma-separated values
    my @new = ( );
    
    push @new, $+ while $text =~ m{
        \s*"([^\"\\]*(?:\\.[^\"\\]*)*)"\s*,?    # Matches a phrase tha
+t may contain commas
        | \s*([^,]+)\s*,?    # Something that is not a comma
        | \s*,    # Just a comma - no data
        }gx;

    push (@new, undef) if substr($text, -1,1)eq ',';
    return @new;
}

__DATA__
1,the,simple,case
"with","quoted" , "strings that contain spaces"
"with","quoted" , "comma, internally"
"with","quoted" , "comma, internally, with null data",,,
[download]

inman

Comment on Re: regular expression (search and destroy) Download Code

Replies are listed 'Best First'.

Re: Re: regular expression (search and destroy)
by demerphq (Chancellor) on Nov 13, 2003 at 15:21 UTC

This should work with Excel generated CSV files.

Nope. That code doesn't do Excel CSV files at all. Excel CSV files are composed of records terminated by an unquoted newline, which are composed of fields that are either quoted with double quotes, with embedded quotes being doubled as the only form of escaping, or unquoted sections composed of non comma (actually seperator) and non quote data.

This means that Excel CSV files cannot be correctly parsed by a simple "read a line and seperate out the fields" approach. You need to know about the state of the previous line to determine if the current line is in fact part of a multiline record, or if it is not. The following code, as far as I can tell will handle CSV files correctly. However I personally would use a module, or if I really couldnt install a module (as anybody experienced knows, this is almost never really true for pure perl code) I would steal tillys code from Text::xSV and cut and paste it. But anyway, this was a fun exercise while I waited for something else to complete.

use strict;
use warnings;
use Data::Dumper;

my $text="";
while (<DATA>)
{
    $text.=$_;
    my @new;
    pos($text)=0;
    my $last=0;
    while ($text=~m{
        \G
        (?:
            "((?:[^"]+|"")*)"    # quoted section
        |
            ([^",]+)           # unquoted
        |
            ((?=\s*,))
        )
        (?:\s*,\s*|\s*\z)
    }gmx) {
        push (@new, $+);
        $last=$+[0];
        $new[-1]=~s/""/"/g;

    }
    if ($last==length($text)) {
        push (@new, "") if $text=~/,$/;
        print "Rec:".Data::Dumper->new([\@new])->Terse(1)
                                 ->Useqq(1)->Indent(0)->Dump()."\n";
        $text="";
    }
}
print "Error: $text\n" if $text;
__DATA__
1,the,simple,"case
"
"with","quoted" , "strings that contain spaces"
"with","quoted" , "comma,
internally"
"with","quoted" , "comma, internally, with ""null"" data",,,
""""""""
[download]

Incidentally it outputs:

Rec:[1,"the","simple","case\n"]
Rec:["with","quoted","strings that contain spaces"]
Rec:["with","quoted","comma,\ninternally"]
Rec:["with","quoted","comma, internally, with \"null\" data","","",""]
Rec:["\"\"\""]
[download]

---
demerphq

_{First they ignore you, then they laugh at you, then they fight you, then you win.

-- Gandhi}

[reply]
[d/l]
[select]


Just another Perl shrine
	PerlMonks