Convert CSV file to nested XML file using UNIX/PERL?


 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Convert CSV file to nested XML file using UNIX/PERL?
# 1  
Old 06-30-2015
Convert CSV file to nested XML file using UNIX/PERL?

we have a CSV which i need to convert to XML using Perl or Unix shell scripting.

I was able to build this XML in oracle database. However, SQL/XML query is running for long time. Hence, I'm considering to write a Perl or shell script to generate this XML file. Basically need to build this XML hierarchically by avoiding the duplicate values. Please take a look into the XML file below.

Input data below:-

Code:
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

XML File:-

Code:
<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>


Any idea would be great. Thank you.

---------- Post updated at 06:56 AM ---------- Previous update was at 04:14 AM ----------

im writing a shell script with nested for loop or nested while loop to generate this XML file. Please let me know if there is any other efficient way to process this CSV file. this CSV file contains total 20 million records. As you know looping through the 20 million records will take much time.

Appreciate your help.
# 2  
Old 07-02-2015
If the data can be sorted hierarchically first (man sort) on the key starting with rltp_id, then prod_id, then cust_id, then tnx_id... then you can write script in a single pass to convert the data (trying to give hints rather than doing your homework).

20 million records... well... takes more space than most, but very doable...

You could use perl or awk to do this (or python or ruby or whatever)... You could use shell (bourne shell script variants), but might not be as easy especially since you want it to be relatively fast and also avoid duplicates.

I'm not trying to be mean, just trying to encourage your own work.

If you get too frustrated I might whip up a bourne (pure) example just as a challenge....
# 3  
Old 07-03-2015
Thanks cjcox!

Yes data is sorted and written into a CSV file. Finally i have built a korn shell script to generate this XML file.

This is how i'm doing. Please correct me if i'm wrong.

Loop through each and every record
compare the current id columns(rltp_id,prod_id,cust_id,acc_id,txn_id) shell variable with previous variable id column value within the loop to avoid the duplicate XML elements. If there is any other simpler method in Korn shell. Please provide your ideas. We have lot of space and RAM in our server.
1TB
16GB RAM

However, my question is how much time would take to process 20 million records. if it completes in 20 minutes that is really great. Smilie
# 4  
Old 07-21-2015
Yes, we can build XML file by using Perl module XML::Writer,
here is the link for more details with script
XML - Convert CSV file into XML file in Perl
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Read csv file, convert the data and make one text file in UNIX shell scripting

I have input data looks like this which is a part of a csv file 7,1265,76548,"0102:04" 8,1266,76545,"0112:04" I need to make the output data should look like this and the output data will be part of text file: 7|1265000 |7654899 |A| 8|12660000 |76545999 |B| The logic behind the... (6 Replies)
Discussion started by: RJG
6 Replies

2. Shell Programming and Scripting

UNIX/PERL script to convert XML file to pipe delimited format

Hello, I need to get few values from a XML file and output needs to be written in another file with pipe delimited format. The Header & Footer of the Pipe Delimited file will be constant. The below is my sample XML file. I need to pull the values in between the XML tags <Operator_info to... (15 Replies)
Discussion started by: karthi1305561
15 Replies

3. Shell Programming and Scripting

UNIX or Perl script to convert JSON to CSV

Is there a Unix or Perl script that converts JSON files to CSV or tab delimited format? We are running AIX 6.1. Thanks in advance! (1 Reply)
Discussion started by: warpmail
1 Replies

4. Shell Programming and Scripting

Perl script to Convert XLSX or XLS files to CSV file

Hi All, I've got in a situation where I need to convert .xlsx or .xls formatted files into simple text file or .csv file. I've found many options but doing this using PERL script is the best way I believe.I'm in AIX box. Perl code should have 2 params while running. i.e perl... (1 Reply)
Discussion started by: manab86
1 Replies

5. Shell Programming and Scripting

Convert XML file to CSV file

Hi Guys, I am new to Shell scripting and need to convert an XML files to a CSV file. My actual problem is that XML file loading is taking hours and I have decided to convert the XML structure to row based data in a CSV file. My XML file: Message846 can repeat within main loop and... (1 Reply)
Discussion started by: qamar.shahbaz
1 Replies

6. Shell Programming and Scripting

convert huge .xml file in .csv with specific column.

I have huge xml file in server and i want to convert it to .csv with specific column ... i have search in blog but i didn't get any usefully command. Thanks in advance (1 Reply)
Discussion started by: pareshkp
1 Replies

7. Shell Programming and Scripting

How to convert a excel file to a .csv file from unix script

Hi I have a excel file in unix machine and have to convert it into a .csv file.I have to do this from a unix script.How do we do this? Thanks Abhinav (3 Replies)
Discussion started by: akashtcs
3 Replies

8. UNIX for Dummies Questions & Answers

Unix script to convert .csv file to.xls format

I have a .csv file in Unix box i need a UNIX script to convert the.csv files to.xls format. Its very urgent please help me. (1 Reply)
Discussion started by: moon_friend
1 Replies

9. Shell Programming and Scripting

Convert XML file into TEXT file using PERL seript

Dear Yogesh..."Convert XML file into TEXT file using PERL seript"... can u help me regarding this issue...plz Thanks Rudro (0 Replies)
Discussion started by: Rudro
0 Replies

10. Shell Programming and Scripting

Sample Unix script file to convert .xml to .csv

Dear all, Can you send me a script file the changes .xml to .csv file. Thanks, Srinivasa (4 Replies)
Discussion started by: srinivasaphani
4 Replies
Login or Register to Ask a Question

Featured Tech Videos