How to generate SQL statements using Perl?

[NOTSomebody]

Hi people,

Suppose, my data is as below: (CSV format)

   'asd,asd,asd','123','123'
   'asd','456','123'

Suppose my code is this:

$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 ',';
       print "$_\n" foreach(@new);   
}

The output result is:

asd,asd,asd
123
123
 
asd
456
123

How do i actually modify the code to allow my output to become:

" Insert into person_info VALUES ("asd,asd,asd",123,123) "
" Insert into person_info VALUES ("asd",456,123) "

Please help...

[eWish]

This can be fine tuned to work exactly as you want, but it will at least give you a start.

my @new = qw(asd,asd,asd 123 234 asd asd 345 456);
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);    
}

Output:

Insert into person_info VALUES ("asd,asd,asd",123,234,"asd","asd",345,456) 

--Kevin

[NOTSomebody]

Thank you Kevin. I finally able to finish my application. Thanks a million!!!

[NOTSomebody]

Hi all, please help a Perl newbie here...

Supposed my data (CSV format) is:

a,123,123
b,234,234
c,345,345

Supposed my code is:

$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); 
    }

My result will be:

Insert into person_info VALUES ("a",123,"123")
Insert into person_info VALUES ("b",234,"234")
Insert into person_info VALUES ("c",345,"345")

This is exactly how i 1 it. BUT

if my data is:

a,1"2"3,123
b,234,234
c,345,3"4"5

my result will be:(which is disaster)

Insert into person_info VALUES ("a",1,2,3,"123")
Insert into person_info VALUES ("b",234,"234")
Insert into person_info VALUES ("c",345,3,4,5,"")

How can I modify to allow a result:

Insert into person_info VALUES ("a",1"2"3,"123")
Insert into person_info VALUES ("b",234,"234")
Insert into person_info VALUES ("c",345,"3"45"")

Please help me!!!

[eWish]

NOTSomebody,

I have merged your two threads are on the same subject. Please do not create new threads when you already have a thread that covers the same issues.

Also, please use the [CODE][/CODE] tags when posting code and sample data.

I have edited your posts for your and please do use the code tags in the future.

Thank You!

Kevin

[eWish]

You will just need to modify the code I gave you to do what you need. Give it a try and if you can't then post the sample code that you have tried and we will help you. What I gave you is a good foundation and would not need to be modified that much to achieve your desired result.

--Kevin

[NOTSomebody]

okay...i will try... thanks alot... sry coz im quite new to this forum so i dunno hw 2 use the code tag etc. sry, i will try 2 use all that in future. thanks alot.