This link, CSV, will search CPAN and find a pile of good modules for working with csv files. DBD::CSV has the advantage that DBI works with it. That will ease the transition to other kinds of databases.

As others have already pointed out the "correct" way to deal with CSV files is to use one of the available modules, however this is a FAQ and a way of doing this by hand is discussed there.

/J\

Wow! What a timely tidbit of information. I was just pondering the same problem and your link was most helpful. But as I discovered from the FAQ link, the example doesn't handle extra spacing (around commas) very well.

Here's my 'space fixed' version, with checking for single quotes as well:

use strict;
use warnings;

# crazy mix of quoting
my $test1 = q/,   "test with space  ",,,   mary had , a,, 'cake, with 
+cheese' , and, "a, \"little"  , lamb chop , /;
# original FAQ example
my $test2 = q/SAR001,"","Cimetrix, Inc","Bob Smith","CAM",N,8,1,0,7,"E
+rror, Core Dumped"/;
# throw some wild spaces in there
my $test3 = q/SAR001, "", "Cimetrix, Inc", "Bob Smith", "CAM",N, 8 ,1,
+0    ,7, "Error, Core Dumped"/;
# finally a nearly empty string
my $test4 = q/,/;

split_string($test1);
split_string($test2);
split_string($test3);
split_string($test4);

sub split_string {
    my $text = shift;
    my @new = ();
    push(@new, $+) while $text =~ m{ \s*(
        # groups the phrase inside double quotes
        "([^\"\\]*(?:\\.[^\"\\]*)*)"\s*,?
        # groups the phrase inside single quotes
        | '([^\'\\]*(?:\\.[^\'\\]*)*)'\s*,?
        # trims leading/trailing space from phrase
        | ([^,\s]+(?:\s+[^,\s]+)*)\s*,?
        # just to grab empty phrases
        | (),
        )\s*}gx;
    push(@new, undef) if $text =~ m/,\s*$/;

    # just to prove it's working
    print "string: >>$text<<\n";
    foreach (@new) {
        print " part: >>" . (defined($_) ? $_ : '') . "<<\n";
    }
}
[download]

Recently the vendor started using single quoted strings in the data like 'IBM machine' or worse 'IBM,16576523'

Although not an official standard, you may want to point your vendor to RFC 4180 or the How To or even Wikipedia. They should really be using double quotes, not single. I know most of the CSV modules support double quotes ... I wouldn't bet on them supporting single quotes.

    @inline=();
    push(@inline, $+) while $pre_inline =~ m{'([^\'\\]*(?:\\.[^\'\\]*)
+*)',? | ([^,]+),?| , }gx;
    push(@inline, undef) if substr($pre_inline,-1,1) eq ',';
    $semi_thirty_char_node_key = $inline[0];
    $mem_size                  = $inline[4];
    $spec                      = $inline[5];
    $num_proc                  = $inline[6];
    $model                     = $inline[10];
    $os                        = $inline[11];
    $alias                     = $inline[14];
    $perf_rate_type            = $inline[15];
    $node_mode                 = $inline[18];
    $num_logical_processors    = $inline[19];
[download]

;pdbdevd1       Olx8jQCKrnQvrA0,'IBM eServer p5 595 1900MH',pdbdevd1,I
+BM_P5_595_1900,4096.000000,394.848511,27,,,,IBM_p5_595_1900,AIX,,,pdb
+devd1,SPECINTRATE2000,EST,'IBM,02028C8AD',LOGICAL,2,DLPAR,SMT
[download]

When you split the row by comma, you must check the count of quotes in every array item, from 0th to Nth. When this count is odd, you must join that item with the next item and test the count again. If the item with odd count of quote is the last, you must add split items from the next row (it occurs when \n in strings).