DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: using perl to generate sql statement from csv file

  1. #1
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75

    using perl to generate sql statement from csv file

    Hi, dear gurus,
    I am oracle dba trying to learn Perl to help with my oracle. But I am not good enough to write a code to do what i want to do. Could anybody help me with a simple code to generate a insert statement from say a csv file.
    example,the csv file:
    20,smith,200,03-01-02,developer,500,3000,20
    I need a perl script to read this file and generate output of:
    insert into emp values(20,'smith',200,'03-01-02','developer',500,3000,200;

    Any help would be appreciated. Thanks.

    Deltafun
    Still learning

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Are you using the DBI? Do you have code that already reads the csv and puts the fields into variables?
    Jeff Hunter

  3. #3
    Join Date
    Feb 2003
    Location
    New York
    Posts
    75
    Thanks, Jeff, for answering back.
    No, I just want to find a piece of code to massage the data into an insert statement and run against the database. Any help would appreciated.

    Deltafun
    Still learning

  4. #4
    Join Date
    Aug 2002
    Location
    Bangalore
    Posts
    52
    Hi..

    using perl DBI.U can directly interact with database.No need to generate DML statements with csv file.

    Regards

    Joe

  5. #5
    Join Date
    Feb 2003
    Location
    Leeds, UK
    Posts
    367
    using perl DBI.U can directly interact with database.No need to generate DML statements with csv file.
    The DBI does allow you to interact with the database directly, but I think you still need to create a scalar that contains an INSERT statement that the DBI will use the execute method on. I get the impression you're assuming that each field in the csv file will map directly to the columns in the table:

    e.g staff.csv

    123456,Austin,Hackett,02/08/1976

    STAFF table:

    emp_no number,
    forename varchar2(50)
    surname varchar2(50)
    dob date

    So:

    use strict;
    use DBI;

    open( FILE, "
    while ( ) {

    chomp;
    my @fields = split /,/;
    my $insert = 'INSERT INTO staff' .
    'VALUES (' .
    $fields[0] . "," .
    "'$fields[1]'" . "," .
    "'$fields[2]'" . "," .
    "TO_DATE ('$fields[3]', 'DD/MM/YYYY')" .
    ")";
    }

    close( FILE );

    I've not got any perl docs. or an interpreter to hand, so I've missed out the DBI stuff as I can't remember the syntax etc. Outside the loop you would use the connect method to connect to the database and inside the loop you'd use the execute method and pass it the $insert scalar to perform the INSERT. Finally you'd use the disconnect method to log off after you've left the loop. This would work but is bad from a performance point of view as each insert will have to be parsed by Oracle. Really you should use bind variables when prepare()-ing the SQL statement and then execute it. Again no docs. means I can't give you the exact syntax. If you going to be using Perl and the DBI a lot I can't recommend Programming the Perl DBI by Tim Bunce and Alligator Descartes (O'Reilly) enough.

    Hope all this provides some kind of starting point.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width