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

In reply to Re: Re: regular expression (search and destroy) by demerphq
in thread regular expression (search and destroy) by data67

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.