Help with CSV file parsing

NOTSomebody has asked for the wisdom of the Perl Monks concerning the following question:

Hi all, please help me

I have a csv file and I need to create an application using Perl to read and generate SQL statements but I faced a problem.

Lets say my data is as follow:
a, asd, 123
b, asd, 234
c, asd, 345

My result will be:
Insert into person_info VALUES("a","asd","123");
Insert into person_info VALUES("b","asd","234");
Insert into person_info VALUES("c","asd","345");

But if my data is:
a, a"s"d, 123
b, asd, 234
c, as"d", 345

My result will become:
Insert into person_info VALUES("a","a","s","d","123");
Insert into person_info VALUES("b","asd","234");
Insert into person_info VALUES("c","as","d","345");

How do i make it such a way that result will be:
Insert into person_info VALUES("a","a"s"d","123");
Insert into person_info VALUES("b","asd","234");
Insert into person_info VALUES("c","as"d"","345");

My code currently is as follow:

$csv = "test.csv";
open(DAT, $csv) || die("Cannot Open File");

while (<DAT>) {
    my @new  = ();

    push(@new, $+) while $_ =~ m{
        "([^\"\\]*(?:\\.[^\"\\]*)*)",?
           |  ([^,]+),?
           | ,
        }gx;

    push(@new, undef) if substr($_, -1,1) eq ',';
       
    my $string = format_string(@new);
    my $query = qq{Insert into person_info VALUES ($string)};
    print $query, "\n"; 
}  

 sub format_string {  
        my $string;
        foreach (@_) {
            if  (/[^\d]/) {
                $string .= qq{"$_",};
            } else {
                $string .= qq{$_,};
            }
        }
        return substr($string, 0, -1); 
    }
[download]

Comment on Help with CSV file parsing Download Code

Replies are listed 'Best First'.
Re: Help with CSV file parsing by ikegami (Patriarch) on Aug 05, 2008 at 07:46 UTC
To contruct your output: If you're using DBI, use placeholders, or if you must, `$dbh->quote`. If you're not, you'll have to write a quoting routine appropriate for the database your queries are targeting, or adapt one from the relevant DBD module.	[reply] [d/l]
Re: Help with CSV file parsing by chrism01 (Friar) on Aug 05, 2008 at 07:46 UTC
Here's an easier way: `$var1 = 'a, a"s"d, 123'; @arr = split(/,/, $var1); print "@arr\n";` [download] You'll want the dbh->quote() method: http://search.cpan.org/~timb/DBI-1.607/DBI.pm#quote Cheers Chris	[reply] [d/l]
Re^2: Help with CSV file parsing by NOTSomebody (Initiate) on Aug 05, 2008 at 08:15 UTC
Hi Chris, thanks for your reply. I understand your point but just that I am trying to read only the double quote. If the text contains other special characters, I will need to read also... Like $var1 = 'a, a"s,d,f,g", 123' Then the result I want will be: Insert into person_info VALUES (a, a"s,d,f,g", 123) Currently I can read every character without taking it out from the part of the text but if there is double quotes within the data, my generated SQL statements will be sort of screwed.	[reply]
Re: Help with CSV file parsing by davorg (Chancellor) on Aug 05, 2008 at 09:03 UTC
DBI::quote is probably overkill. Use placeholders. `my $sql = 'INSERT INTO person VALUES (?, ?, ?)'; my $dbh = DBI->connect(YOUR CONNECTION DETAILS) or die DBI::errstr; my $sth = $dbh->prepare($sql); my $csv = 'test.csv'; open my $data, '<', $csv or die "Cannot Open File: $!\n"; while (<$data>) { chomp; $sth->execute(split /,/); }` [download] Looks far simpler than you were trying make it. Am I missing something? And, yes, it does deal with strings with embedded quotation marks. Oh, wait. I see what I'm missing. You don't need the quotation marks from the input data. I think you should look at Text::ParseWords. -- See the Copyright notice on my home node. "The first rule of Perl club is you do not talk about Perl club." -- Chip Salzenberg	[reply] [d/l]
Re: Help with CSV file parsing by starX (Chaplain) on Aug 05, 2008 at 08:32 UTC
If the number of values that you need to be inserting in your query is fixed, and yu're using DBI, placeholders are the way to go. `# Code that connects to database my $sth = $dbh->prepare(' Insert into person_info VALUES(?,?,?)'); # Code that opens your file. while (<FILE>) { my @row = split /,/; $sth->bind_param(1, $row[0]); $sth->bind_param(2, $row[1]); $sth->bind_param(3, $row[2]); $sth->execute() or warn "Couldn't execute INSERT: $DBI::errstr\n"; }` [download] Of course, this doesn't take into account the manipulation on the second field of your csv that it sounds like you need to do. Hope that helps, --starX www.axisoftime.com	[reply] [d/l]
Re: Help with CSV file parsing by Bloodnok (Vicar) on Aug 05, 2008 at 10:05 UTC
In the light of previous good suggestions, why not give Text::xSV a try - it does have some limitations, but I suspect they won't affect your situation... HTH , At last, a user level that overstates my experience :-))	[reply]