Convert CSV file to XML file using Perl?

[laknarc]

I have already posted this question as Anonymous monk. Please delete my previous thread. I have registered and created this question again. we have a CSV file which need be to converted to XML using Perl or Unix shell scripting. I was able to build this XML using SQL and Korn shell script. However, SQL/shell script is running for long time. Hence, I'm considering to write a Perl to generate this XML file. Basically need to build this XML hierarchically by avoiding the duplicate values. Please take a look into the CSV and XML file below. But the both SQL and Korn shell script takes 15 minutes to create the XML for just 6000 records. Please let me know if there is any other efficient way to process this CSV file. this CSV file contains total 20 million records. Need to build this XML file within 60 to 120 minutes. Appreciate your help. Input data below:-

 
RLTP_ID,RLTP_NAME,PROD_ID,PROD_NAME,CUST_ID,CUST_NAME,ACC_ID,ACC_NAME,
+ACC_BALANCE,TXN_ID,TXN_AMT,ACC_TYPE,ACC_CODE,TXN_CODE
10,Phil,1,Personal,2,Fixed,3,Savings,3000,4,500,X,YY,11
10,Phil,1,Personal,2,Fixed,3,Savings,3000,4,500,X,YY,12
10,Phil,1,Personal,2,Fixed,3,Savings,3000,4,500,X,ZZ,11
10,Phil,1,Personal,2,Fixed,3,Savings,3000,4,500,X,ZZ,12
10,Phil,1,Personal,2,Fixed,7,Savings,3000,8,500,X,AA,11
10,Phil,1,Personal,2,Fixed,7,Savings,3000,8,500,X,AA,12
10,Phil,1,Personal,2,Fixed,7,Savings,3000,8,500,X,AA,13
10,Phil,1,Personal,2,Fixed,7,Savings,3000,8,500,X,BB,11
10,Phil,1,Personal,2,Fixed,7,Savings,3000,8,500,X,BB,12
10,Phil,1,Personal,2,Fixed,7,Savings,3000,8,500,X,BB,13

Output XML

<transactiondetails>
  <rltp_id>10</rltp_id>
  <rltp_name>Phil</rltp_name>
  <product>
    <prod_id>1</prod_id>
    <prod_name>Personal</prod_name>
    <customer>
      <cust_id>2</cust_id>
      <cust_name>Fixed</cust_name>
      <account>
        <acc_id>3</acc_id>
        <acc_name>Savings</acc_name>
        <acc_balance>3000</acc_balance>
        <acc_type>X</acc_type>
        <acc_code>ZZ</acc_code>
        <acc_code>YY</acc_code>
        <transaction>
          <txntrack>
            <txn_id>4</txn_id>
            <txn_amt>500</txn_amt>
            <txn_code>11</txn_code>
            <txn_code>12</txn_code>
          </txntrack>
        </transaction>
        <transaction>
          <txntrack>
            <txn_id>5</txn_id>
            <txn_amt>500</txn_amt>
            <txn_code/>
          </txntrack>
        </transaction>
        <transaction>
          <txntrack>
            <txn_id>6</txn_id>
            <txn_amt>500</txn_amt>
            <txn_code/>
          </txntrack>
        </transaction>
      </account>
      <account>
        <acc_id>7</acc_id>
        <acc_name>Savings</acc_name>
        <acc_balance>3000</acc_balance>
        <acc_type>X</acc_type>
        <acc_code>AA</acc_code>
        <acc_code>BB</acc_code>
        <transaction>
          <txntrack>
            <txn_id>8</txn_id>
            <txn_amt>500</txn_amt>
            <txn_code>11</txn_code>
            <txn_code>12</txn_code>
            <txn_code>13</txn_code>
          </txntrack>
        </transaction>
      </account>
    </customer>
  </product>
</transactiondetails>

[bitingduck]

I'm too lazy right now to sort through your XML to figure out exactly how you want to do the structure-- rather than just turning each line into an XML record it looks like you want a bunch of nested loops following the columns almost from left to right, and terminating each loop when there's a change in the value. That should be pretty straightforward.

You should be able to use Text::CSV and XML::LibXML to do something that runs pretty fast. You can probably get by with focusing on the XML::LibXML::Node and XML::LibXML::Element docs.

And before you get too far- have you profiled your code at all to see where the hangups are? Is it the SQL being super slow because you don't have it indexed on the right columns and while the perl might turn the CSV into XML in a second or two, you're going to spend 3 days exporting the SQL to CSV in the sorted format you show here?

[marinersk]

So, what have you tried? Showing code, with a good description of what you're trying to do (nicely done, above) and what you're getting that's different gets pretty fast answers around here, most of the time.

Or are you at a point where you haven't really used Perl yet for much of anything, need a nudge in the right direction, and don't really have any code to show?

[Anonymous Monk]

Sure https://metacpan.org/pod/DBD::AnyData#CSV-Tab-Pipe-Ini-Paragraph, https://metacpan.org/pod/DBD::AnyData#XML,

  $dbh->func( $source_format, $source_data,
              $target_format, $target_file,
              $source_flags,  $target_flags,
  'ad_convert');

Sure, DBD::AnyData / DBD::CSV, Text::CSV/Text::CSV_XS , Text::xSV. Examples at

• join - join two files according to a common key
• part - split up files according to column value
• Merge two files with similar column entries
• Opening multiple files ( csvpaste.pl )
• Open multiple file handles?
• X, Y Table structure
• How to extract the particular residues from PDB files(text csv split hash bioperl)
• Select only desired features from a text (text csv split hash bioperl)
• Sort on Table headers (text csv split hash bioperl)
• I need help joining tab-delimited files/tables!
• split then join based on common value in field
• Smart way to read a file vertically?
• reformatting tab delimited file
• edit a CSV and "in-place" replacement
• Re: Reversing a mysql table, transpose-tsv -- invert a tab-delimited table

• Text::CSV

  examples/csv2xls	Script to onvert CSV files to M$Excel
  examples/csv-check	Script to check a CSV file/stream
  examples/csvdiff	Script to shoff diff between sorted CSV files
  examples/parser-xs.pl	Parse CSV stream, be forgiving on bad lines

 [CSV hash ] CSV hash

Best way to match a hash with large CSV file

perl hash to CSV using Text::CSV_XS

Issue parsing CSV into hashes?

Veriable Length Array/Hash derived from CSV to populate an XML

extracting data from CSV files and making hash of hashes

Re^2: build hash from csv file

Encoding a hash in perl before saving it as a CSV file

hash from CSV-like structure

Read the csv file to a hash....

Parsing CSV into a hash

build hash from csv file

Converting a CSV list to a list of hashrefs naming the fields

[poj]

Do the 20 million record all have the same rltp_id ?
The csv seems to be missing txn_ids 5 and 6 ?
poj

[laknarc]

Yes. Txn I'd's missing in the file. Because it does not have txn codes. Sorry I forget to mention these. This is the reason we have empty tags in XML. Txn codes are coming from different table. While exporting the data from database to csv I have used inner join. Please consider we have 4 and 5 txn I'd,s in the CSV file and txn codes as nulls. I mean I will use the outer join to export the data to csv.

[poj]

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

Read more... (3 kB)

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

poj

[sundialsvc4]

Also, consider all of your possibilities ... including, “writing no(!) computer program at all.”

A CSV to XML Converter in XSLT 2.0.

It can go the other way, too:   XML to CSV Using XSLT.

In both cases, as you can see, the transformation is being accomplished using a declarative feature of industry-standard XML libraries such as libxslt, which of course are fully supported by Perl libraries such as XML::LibXML, but also by command-line tools such as Saxon.   You specify what transformations you want, using an XML-based format to do so.   It is the responsibility of the XSLT processor (and there are several of these) to obey those instructions.

There are widespread applications for this idea.   For example, many documentation files are written in XML-based “semantic markup” languages such as DocBook, and then translated ... using XSLT ... into several different output formats.   (All of the O’Reilly books ... the ones with animals on the cover ... are done that way.)   No custom programming is involved.

I’ll leave it to others ... and to you ... to decide what is the overall best way to accomplish this goal for your particular project.   With Perl, you have many, many choices.

[Anonymous Monk]

All of the O’Reilly books ... the ones with animals on the cover ... are done that way.

Nope.

[laknarc]

Thanks all. Here is the SQL which generates the XML file. However it takes days to generate the XML file for 20 million records. I do have proper indexes on the tables. Could you please provide your ideas to generate this XML. Which one is the fast method to build this XML?. 1. Extracting the data to CSV file takes about 15 minutes using ETL and generate the XML using Perl. 2. Otherwise writing a SQL by joining all the normalized tables and generate the XML file by calling the SQL within the perl.

select xmlserialize(document
       XMLElement("transactiondetails", 
                 XMLElement("rltp_id", rltp_id), 
                 XMLElement("rltp_name", rltp_name), 
                    (SELECT XMLAGG(XMLElement("product", 
                                  XMLElement("prod_id", p.prod_id), 
                                  XMLElement("prod_name", p.prod_name)
+, 
                        (SELECT XMLAGG(XMLElement("customer", 
                                      XMLElement("cust_id", cust_id), 
                                      XMLElement("cust_name", cust_nam
+e), 
                                 (SELECT XMLAGG(XMLElement("account", 
                                            XMLElement("acc_id", acc_i
+d), 
                                            XMLElement("acc_name", acc
+_name), 
                                            XMLElement("acc_balance", 
+acc_balance),
                                            nvl(
                                              (
                                               select xmlconcat(
                                                        xmlelement("ac
+c_type", at.acc_type),
                                                        xmlagg(xmlelem
+ent("acc_code", at.acc_code))
                                                      )
                                               from acctype at
                                               where at.rltp_id = a.rl
+tp_id
                                               and at.prod_id = a.prod
+_id
                                               and at.cust_id = a.cust
+_id
                                               and at.acc_id = a.acc_i
+d
                                               group by at.acc_type
                                               )
                                             , xmlconcat(xmlelement("a
+cc_type"),xmlelement("acc_code"))   
                                            ) ,
                                        (SELECT XMLAGG(XMLElement("tra
+nsaction", 
                                               XMLElement("txntrack", 
                                               XMLElement("txn_id", t.
+txn_id), 
                                               XMLElement("txn_amt", t
+.txn_amt),
                                               (
                                                select nvl(
                                                         xmlagg(xmlele
+ment("txn_code",tt.txn_code))
                                                       , xmlelement("t
+xn_code")
                                                       )
                                                from txntype tt
                                                where tt.rltp_id = t.r
+ltp_id
                                                and tt.prod_id = t.pro
+d_id
                                                and tt.cust_id = t.cus
+t_id
                                                and tt.acc_id = t.acc_
+id
                                                and tt.txn_id = t.txn_
+id
                                               )
                                               ))
                                               ORDER BY t.txn_id
                                               ) 
                                        FROM transaction t 
                                        WHERE t.rltp_id  =a.rltp_id 
                                        AND t.prod_id   =a.prod_id 
                                        AND t.cust_id   =a.cust_id 
                                        AND t.acc_id=a.acc_id))
                                        ORDER BY a.acc_id
                                        ) 
                            FROM account a 
                            WHERE c.rltp_id=a.rltp_id 
                            AND c.prod_id=a.prod_id 
                            AND c.cust_id=a.cust_id))
                            ORDER BY c.cust_id
                            ) 
                        FROM cust c 
                        WHERE p.rltp_id = c.rltp_id 
                        AND p.prod_id = c.prod_id))
                        ORDER BY p.prod_id
                        ) 
                     FROM product p 
                     WHERE p.rltp_id = r.rltp_id )        
                    ) indent) AS xml 
FROM 
  rltp_mngr r 
  WHERE rltp_id='10';