By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,660 Members | 1,267 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,660 IT Pros & Developers. It's quick & easy.

How to generate SQL statements using Perl?

P: 13
Hi people,

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

Expand|Select|Wrap|Line Numbers
  1.    'asd,asd,asd','123','123'
  2.    'asd','456','123'
Suppose my code is this:

Expand|Select|Wrap|Line Numbers
  1. $csv = "test.csv";
  2. open(DAT, $csv) || die("Cannot Open File");
  3.  
  4. while (<DAT>) {
  5.  
  6.     my @new  = ();
  7.  
  8.     push(@new, $+) while $_ =~ m{
  9.         "([^\"\\]*(?:\\.[^\"\\]*)*)",?
  10.  
  11.            |  ([^,]+),?
  12.  
  13.            | ,
  14.         }gx;
  15.  
  16.        push(@new, undef) if substr($_, -1,1) eq ',';
  17.        print "$_\n" foreach(@new);   
  18. }
The output result is:

Expand|Select|Wrap|Line Numbers
  1. asd,asd,asd
  2. 123
  3. 123
  4.  
  5. asd
  6. 456
  7. 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...
Aug 4 '08 #1
Share this Question
Share on Google+
6 Replies


eWish
Expert 100+
P: 971
This can be fine tuned to work exactly as you want, but it will at least give you a start.

Expand|Select|Wrap|Line Numbers
  1. my @new = qw(asd,asd,asd 123 234 asd asd 345 456);
  2. my $string = format_string(@new);
  3. my $query = qq{Insert into person_info VALUES ($string)};
  4.  
  5. print $query, "\n";
  6.  
  7. sub format_string {
  8.  
  9.     my $string;
  10.  
  11.     foreach (@_) {
  12.         if  (/[^\d]/) {
  13.             $string .= qq{"$_",};
  14.         } else {
  15.             $string .= qq{$_,};
  16.         }
  17.     }
  18.     return substr($string, 0, -1);    
  19. }
Output:
Expand|Select|Wrap|Line Numbers
  1. Insert into person_info VALUES ("asd,asd,asd",123,234,"asd","asd",345,456) 
--Kevin
Aug 4 '08 #2

P: 13
Thank you Kevin. I finally able to finish my application. Thanks a million!!!
Aug 4 '08 #3

P: 13
Hi all, please help a Perl newbie here...

Supposed my data (CSV format) is:
Expand|Select|Wrap|Line Numbers
  1. a,123,123
  2. b,234,234
  3. c,345,345
Supposed my code is:

Expand|Select|Wrap|Line Numbers
  1. $csv = "test.csv";
  2. open(DAT, $csv) || die("Cannot Open File");
  3.  
  4. while (<DAT>) {
  5.     my @new  = ();
  6.  
  7.     push(@new, $+) while $_ =~ m{
  8.         "([^\"\\]*(?:\\.[^\"\\]*)*)",?
  9.            |  ([^,]+),?
  10.            | ,
  11.         }gx;
  12.  
  13.     push(@new, undef) if substr($_, -1,1) eq ',';
  14.  
  15.     my $string = format_string(@new);
  16.     my $query = qq{Insert into person_info VALUES ($string)};
  17.     print $query, "\n"; 
  18. }  
  19.  
  20.  sub format_string {  
  21.         my $string;
  22.         foreach (@_) {
  23.             if  (/[^\d]/) {
  24.                 $string .= qq{"$_",};
  25.             } else {
  26.                 $string .= qq{$_,};
  27.             }
  28.         }
  29.         return substr($string, 0, -1); 
  30.     }
My result will be:
Expand|Select|Wrap|Line Numbers
  1. Insert into person_info VALUES ("a",123,"123")
  2. Insert into person_info VALUES ("b",234,"234")
  3. Insert into person_info VALUES ("c",345,"345")
This is exactly how i 1 it. BUT

if my data is:
Expand|Select|Wrap|Line Numbers
  1. a,1"2"3,123
  2. b,234,234
  3. c,345,3"4"5
my result will be:(which is disaster)
Expand|Select|Wrap|Line Numbers
  1. Insert into person_info VALUES ("a",1,2,3,"123")
  2. Insert into person_info VALUES ("b",234,"234")
  3. Insert into person_info VALUES ("c",345,3,4,5,"")
How can I modify to allow a result:
Expand|Select|Wrap|Line Numbers
  1. Insert into person_info VALUES ("a",1"2"3,"123")
  2. Insert into person_info VALUES ("b",234,"234")
  3. Insert into person_info VALUES ("c",345,"3"45"")
Please help me!!!
Aug 4 '08 #4

eWish
Expert 100+
P: 971
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
Aug 4 '08 #5

eWish
Expert 100+
P: 971
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
Aug 4 '08 #6

P: 13
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.
Aug 4 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.