Re: Parsing a complex csv, cleaning it up, and exporting it

Hello scotttromley, and welcome to the Monastery!

I decided the best way to handle this would be by learning perl and using it, and I've made decent progress thus far.

Bravo! Perl is an excellent tool for this type of job, and your code shows you are making excellent progress. A word of warning, though: Perl is good for a lot else besides text munging — so good, it easily becomes addictive! Just be prepared for the inevitable lifestyle changes as you find yourself spending more and more time hanging around the Monastery. ;-)

However, it doesn't match anything in my Perl program.

I don’t know why you say that — your regex works fine for me:

#! perl
use strict;
use warnings;
use Text::CSV;

my $csv = Text::CSV->new
          (
              {
                  sep_char           => ',',
                  binary             =>  1,
                  auto_diag          =>  1,
                  allow_loose_quotes =>  1
              }
          );

while (my $row = $csv->getline(*DATA))
{
    my $notesField = $row->[25];

    if (defined $notesField) 
    {
        print "\nNotes field: |$notesField|\n\n";

        if ($notesField =~ /First Name:(.*?)(?=Last)/)
        {
            print "MATCHED!: '$1'\n";
        }
    }
    else
    {
        print "No Notes Field\n\n";
    }
}
    
$csv->error_diag() unless $csv->eof;

__DATA__
,,,,,,,,,,,,,,,,,,,,,,,,,"First Name: Dobbin Last Name: David L. Addre
+ss: david@adamsonanddobbin.com City: PO Box 1326407 Pido Road State: 
+Peterborough ZIP Code: ON Country: K9J 7H5
First Name: Dobbin Last Name: David L. E-mail: david@adamsonanddobbin.
+com Address: PO Box 1326407 Pido Road City: Peterborough State: ON ZI
+P Code: K9J 7H5",,,,,,Home,743 7790,Other,742 4524,Work,745 5751,,,,,
+,,,,,,Adamson And Dobbin Ltd. Mechanical Contractors,,General Manager
+,,,,,,,,,,,
,,,,,,,,,,,,,,,,,,,,,,,,,"First Name: Chapleau Last Name: Kathy, Ken A
+ddress: 666 FrankFirst Name: Chapleau Last Name: Kathy, Ken City: 666
+ Frank
",,,,,,Home,876-9863,,,,,,,,,,,,,,,Admiralty Hall,,Accountant,,,,,,,,,
+,,
[download]

Output:

12:36 >perl 985_SoPW.pl

Notes field: |First Name: Dobbin Last Name: David L. Address: david@ad
+amsonanddobbin.com City: PO Box 1326407 Pido Road State: Peterborough
+ ZIP Code: ON Country
: K9J 7H5
First Name: Dobbin Last Name: David L. E-mail: david@adamsonanddobbin.
+com Address: PO Box 1326407 Pido Road City: Peterborough State: ON ZI
+P Code: K9J 7H5|

MATCHED!: ' Dobbin '

Notes field: |First Name: Chapleau Last Name: Kathy, Ken Address: 666 
+FrankFirst Name: Chapleau Last Name: Kathy, Ken City: 666 Frank
|

MATCHED!: ' Chapleau '

12:36 >
[download]

But note that this code:

if ($notesField =~ /First Name:(.*?)(?=Last)/)
{
    print "MATCHED!: '$1'\n";
}
[download]

Matches only the first “First Name” in any given row. To get all the matches, change the if to:

while ($notesField =~ /First Name:(.*?)(?=Last)/g)
[download]

See “Global matching” in perlretut#Using-regular-expressions-in-Perl.

Hope that helps,

Athanasius <°(((>< contra mundum Iustus alius egestas vitae, eros Piratica,

Comment on Re: Parsing a complex csv, cleaning it up, and exporting it Select or Download Code