in reply to Re^2: Convert CSV file to XML file using Perl?
in thread Convert CSV file to XML file using Perl?

Ok, I think I understand you.

I wrote some code to process the CSV and the results were encouraging, 3 million records in about 5 minutes. However, I think a DBI connection to the database might be cleaner so this is what I have so far. You need to take A_ off the table names in the sql

#!perl
use strict;
use DBD::Oracle;
use XML::Writer;
use Data::Dump 'pp';

my $outfile = 'output.xml';
my $RLTP_ID = 10;

# connect to database
my $dbh = dbh();

# RLTP
my $sql = 'SELECT rltp_id,rltp_name from A_RLTP_MNGR
           WHERE rltp_id=?';
my $sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
my $RLTP = $sth->fetchall_hashref(1);

# PRODUCT
$sql = 'SELECT prod_id,prod_name from A_PRODUCT
        WHERE rltp_id=?';
$sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
my $PRODUCT = $sth->fetchall_hashref(1);

# CUSTOMER
$sql = 'SELECT cust_id,cust_name from A_CUST
        WHERE rltp_id=?';
$sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
my $CUST = $sth->fetchall_hashref(1);

# ACCOUNT
$sql = 'SELECT acc_id,acc_name,acc_balance from A_ACCOUNT
        WHERE rltp_id=?';
$sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
my $ACCOUNT = $sth->fetchall_hashref(1);

# TRANS
my %TRANS=();
$sql = 'SELECT prod_id,cust_id,acc_id,txn_id,txn_amt from A_TRANSACTIO
+N
        WHERE rltp_id=?';
$sth = $dbh->prepare($sql);
$sth->execute($RLTP_ID);
while (my @f = $sth->fetchrow_array){
  $TRANS{$f[0]}{$f[1]}{$f[2]}{$f[3]}{'amt'}=$f[4];
}
$dbh->disconnect;

# CREATE XML
my $t0 = time();
my $output = IO::File->new(">$outfile");
my $w = XML::Writer->new(
  OUTPUT => $output,
  DATA_MODE => 1,
  DATA_INDENT=>2 );
$w->startTag('transactiondetails');   
$w->dataElement('rltp_id'   => $RLTP_ID);
$w->dataElement('rltp_name' => $RLTP->{$RLTP_ID}{'RLTP_NAME'});

for my $prod (sort keys %TRANS){

  $w->startTag('product');   
  $w->dataElement('product_id'   => $prod);
  $w->dataElement('product_name' => $PRODUCT->{$prod}{'PROD_NAME'});
  
  for my $cust (sort keys %{$TRANS{$prod}}){

    $w->startTag('customer');   
    $w->dataElement('cust_id'   => $cust);
    $w->dataElement('cust_name' => $CUST->{$cust}{'CUST_NAME'});

    for my $acc (sort keys %{$TRANS{$prod}{$cust}}){
    
      $w->startTag('account');  
      $w->dataElement('acc_id'      => $acc);
      $w->dataElement('acc_name'    => $ACCOUNT->{$acc}{'ACC_NAME'});
      $w->dataElement('acc_balance' => $ACCOUNT->{$acc}{'ACC_BALANCE'}
+);
      #$w->dataElement('acc_type'    => 0);
      $w->endTag('account');    
    }  
    $w->endTag('customer');
  }
  $w->endTag('product');   
}

$w->endTag('transactiondetails');   
$w->end();
$output->close();
my $dur  = time() - $t0;
print "XML created as $outfile in $dur seconds\n";

# connect
sub dbh {
  my $host = "localhost";
  my $sid  = 'xe';
  my $user = 'user';
  my $pwd  = 'password';
  my $dsn = "dbi:Oracle:host=$host;sid=$sid";
  my $dbh = DBI->connect($dsn, $user, $pwd, 
            { AutoCommit => 0,
            })  or die "$!";
  return $dbh
}

[download]

Let me know if I am on the right track and I will finish it off.

poj

Replies are listed 'Best First'.
Re^4: Convert CSV file to XML file using Perl?
by laknarc (Initiate) on Jul 05, 2015 at 22:10 UTC

    Awesome. excellent. thank you so much. 3 million records in 5 minutes is really encouraging. yes. this code looks clean and we are on the right track. I noticed that the acc_type and acc_code fields are missing in account XML and txn_code is missing in transaction XML. these fields are coming from different tables too. a account will have an acc_type but multiple acc_codes. if there is no acc_types and acc_codes, we need to have empty tags. a transaction can have multiple txn_code. if there are no txn_codes, we need to have empty tag. here is the DDL and DML for these tables. Also, could you please post the code to process the CSV file? I will go with the DBI connection code. 

    drop table rltp_mngr;  
drop table product;  
drop table cust;  
drop table account;  
drop table transaction;  
drop table acctype;  
drop table txntype;  
  
create table rltp_mngr(rltp_id number,rltp_name varchar2(50));  
Insert into rltp_mngr values(10, 'Phil');  
Insert into rltp_mngr values(20, 'Jack');  
  
create table product(rltp_id number,prod_id number,prod_name varchar2(
+50));  
Insert into product values(10,1, 'Personal');  
Insert into product values(20,1, 'Business');  
 
create table cust(rltp_id number,prod_id number,cust_id number,cust_na
+me varchar2(50));  
insert into cust values(10,1,2,'Fixed');  
insert into cust values(20,1,2,'Fixed');  
 
Create table account(rltp_id number,prod_id number,cust_id number,acc_
+id number,acc_name varchar2(50),acc_balance number(18,2));  
insert into account values(10,1,2,3,'Savings',3000);  
insert into account values(10,1,2,7,'Savings',3000);  
insert into account values(20,1,2,3,'Savings',3000);  
 
create table transaction(rltp_id number,prod_id number,cust_id number,
+acc_id number,txn_id number,txn_amt number(18,2));  
insert into transaction values(10,1,2,3,4,500);  
insert into transaction values(10,1,2,3,5,500);  
insert into transaction values(10,1,2,3,6,500);  
insert into transaction values(10,1,2,7,8,500);  
insert into transaction values(20,1,2,3,4,500);  
 
create table acctype(rltp_id number,prod_id number,cust_id number,acc_
+id number,acc_type varchar2(5),acc_code varchar2(10));  
insert into acctype values(10,1,2,3,'X','ZZ');  
insert into acctype values(10,1,2,3,'X','YY');  
insert into acctype values(10,1,2,7,'X','AA');  
insert into acctype values(10,1,2,7,'X','BB');  
  
create table txntype(rltp_id number,prod_id number,cust_id number,acc_
+id number,txn_id number,txn_code varchar2(10));  
insert into txntype values(10,1,2,3,4,'11');  
insert into txntype values(10,1,2,3,4,'12');  
insert into txntype values(10,1,2,7,8,'11');  
insert into txntype values(10,1,2,7,8,'12');  
insert into txntype values(10,1,2,7,8,'13');

    [download]
[reply]
[d/l]

In Section Seekers of Perl Wisdom