Visit The New, Modern Unix Linux Community


Need to read a csv and create xml - Perl


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Need to read a csv and create xml - Perl
# 1  
Need to read a csv and create xml - Perl

I have a csv file like below.
Code:
john,r2,testdomain1,john.r2@hex.com,DOMAINADMIN,testdomain1.dom
maxwell,b2, testdomain1,maxwell.b2@hex.com,DOMAINADMIN,testdomain1.dom

I would need the perl script to read the above csv and create an xml like below.
Code:
<Users>
        <DomainName>testdomain1</DomainName>
        <User>
            <UserName>john.r2.testdomain1.digitalsafe.net</UserName>
                <Password>
                <Value>Skyline@123</Value>
                </Password>
            <BillingGroupName>testdomain1</BillingGroupName>
            <DataSource>sa</DataSource>
            <UserProfile>
                <LastName>r2</LastName>
                <FirstName>john</FirstName>
                <ContactEmail>john.r2@hex.com</ContactEmail>
                <SendItToMe>nobody@zantaz.com</SendItToMe>
            </UserProfile>
        </User>
        <User>
            <UserName>maxwell.b2.testdomain1.digitalsafe.net</UserName>
                <Password>
                <Value>Skyline@123</Value>
                </Password>
            <BillingGroupName>testdomain1</BillingGroupName>
            <DataSource>sa</DataSource>
            <UserProfile>
                <LastName>b2</LastName>
                <FirstName>maxwell</FirstName>
                <ContactEmail>maxwell.b2@hex.com</ContactEmail>
                <SendItToMe>nobody@zantaz.com</SendItToMe>
            </UserProfile>
        </User>

</users>

<UserRoleAssignments>
<UserRoleAssignment>
            <UserName>john.r2.testdomain1.digitalsafe.net</UserName>
            <RoleName>EntityAccess</RoleName>
            <Entities>
                <Entity ns1:type="Applications">
                    <EntityName>ac</EntityName>
                </Entity>
            </Entities>
        </UserRoleAssignment>
        <UserRoleAssignment>
            <UserName>john.r2.testdomain1.digitalsafe.net</UserName>
            <RoleName>DomainAdmin</RoleName>
            <Entities>
                <Entity ns1:type="Applications">
                    <EntityName>adm</EntityName>
                </Entity>
            </Entities>
        </UserRoleAssignment>
<UserRoleAssignment>
            <UserName>john.r2.testdomain1.digitalsafe.net</UserName>
            <RoleName>DomainAdmin</RoleName>
            <Entities>
                <Entity ns1:type="Applications">
                    <EntityName>adm</EntityName>
                </Entity>
            </Entities>
        </UserRoleAssignment>
    </UserRoleAssignments>
</ZantazAccountManagement>

I wrote a script but its not capable of printing common xml tags in the output file.
Code:
use strict;

  # Open the ch2_xml_users.csv file for input
  open(CSV_FILE, "ch2_xmlusers.csv") ||
   die "Can't open file: $!";

  # Open the ch2_xmlusers.xml file for output
  open(XML_FILE, ">ch2_xmlusers.xml") ||
   die "Can't open file: $!";

 # Print the initial XML header and the root element
 print XML_FILE "<ZantazAccountManagement xmlns\=\"http\:\/\/www\.zantaz\.com\/\" xmlns\:xsi\=\"http\:\/\/www\.w3\.org\/2001\/XMLSchema\-instance\" xmlns\:ns1\=\"http\:\/\/www\.zantaz\.com\/\" xsi\:schemaLocation\=\"http\:\/\/www\.zantaz\.com\/ schema\.xsd\" ns1\:action\=\"UPDATE\">
";
print XML_FILE " <BillingGroups>
        <BillingGroup>
            <Name>testdomain1</Name>
            <Type>-</Type>
            <CustomerName>testdomain1</CustomerName>
        </BillingGroup>
    </BillingGroups>";

 # The while loop to traverse through each line in users.csv
 while(<CSV_FILE>) {
   chomp; # Delete the new line char for each line

   # Split each field, on the comma delimiter, into an array
   my @fields = split(/,/, $_);

 print XML_FILE<<"EOF";
 <DomainName>testdomain1</DomainName>
        <User>
            <UserName>$fields[0].$fields[1].$fields[3].$fields[4]</UserName>
                <Password>
                <Value>Skyline\@123</Value>
                </Password>
            <BillingGroupName>$fields[4]</BillingGroupName>
            <DataSource>sa</DataSource>
            <UserProfile>
                <LastName>$fields[1]</LastName>
                <FirstName>$fields[0]</FirstName>
                <ContactEmail>$fields[2]</ContactEmail>
                <SendItToMe>nobody\@zantaz.com</SendItToMe>
            </UserProfile>
        </User>
    <UserRoleAssignment>
            <UserName>$fields[0].$fields[1].$fields[3].$fields[4]</UserName>
            <RoleName>DomainAdmin</RoleName>
            <Entities>
                <Entity ns1:type="Applications">
                    <EntityName>adm</EntityName>
                </Entity>
            </Entities>
        </UserRoleAssignment>
        <UserRoleAssignment>
            <UserName>$fields[0].$fields[1].$fields[3].$fields[4]</UserName>
            <RoleName>Researcher</RoleName>
            <Entities>
                <Entity ns1:type="Applications">
                    <EntityName>ds</EntityName>
                </Entity>
            </Entities>
        </UserRoleAssignment>


EOF
 }

 # Close the root element

print XML_FILE "</ZantazAccountManagement>";

 # Close all open files
close CSV_FILE;
 close XML_FILE;

items marked in red are common tags in this xml. I am not sure how I can print them while working on while loop. Can any one help me with this please?
# 2  
Quote:
Originally Posted by Tuxidow
I have a csv file like below.
Code:
john,r2,testdomain1,john.r2@hex.com,DOMAINADMIN,testdomain1.dom
maxwell,b2, testdomain1,maxwell.b2@hex.com,DOMAINADMIN,testdomain1.dom

I would need the perl script to read the above csv and create an xml like below.
Code:
<Users>
        <DomainName>testdomain1</DomainName>
        <User>
            <UserName>john.r2.testdomain1.digitalsafe.net</UserName>
                <Password>
                <Value>Skyline@123</Value>
                </Password>
            <BillingGroupName>testdomain1</BillingGroupName>
            <DataSource>sa</DataSource>
            <UserProfile>
                <LastName>r2</LastName>
                <FirstName>john</FirstName>
                <ContactEmail>john.r2@hex.com</ContactEmail>
                <SendItToMe>nobody@zantaz.com</SendItToMe>
            </UserProfile>
        </User>
        <User>
            <UserName>maxwell.b2.testdomain1.digitalsafe.net</UserName>
                <Password>
                <Value>Skyline@123</Value>
                </Password>
            <BillingGroupName>testdomain1</BillingGroupName>
            <DataSource>sa</DataSource>
            <UserProfile>
                <LastName>b2</LastName>
                <FirstName>maxwell</FirstName>
                <ContactEmail>maxwell.b2@hex.com</ContactEmail>
                <SendItToMe>nobody@zantaz.com</SendItToMe>
            </UserProfile>
        </User>

</users>

<UserRoleAssignments>
<UserRoleAssignment>
            <UserName>john.r2.testdomain1.digitalsafe.net</UserName>
            <RoleName>EntityAccess</RoleName>
            <Entities>
                <Entity ns1:type="Applications">
                    <EntityName>ac</EntityName>
                </Entity>
            </Entities>
        </UserRoleAssignment>
        <UserRoleAssignment>
            <UserName>john.r2.testdomain1.digitalsafe.net</UserName>
            <RoleName>DomainAdmin</RoleName>
            <Entities>
                <Entity ns1:type="Applications">
                    <EntityName>adm</EntityName>
                </Entity>
            </Entities>
        </UserRoleAssignment>
<UserRoleAssignment>
            <UserName>john.r2.testdomain1.digitalsafe.net</UserName>
            <RoleName>DomainAdmin</RoleName>
            <Entities>
                <Entity ns1:type="Applications">
                    <EntityName>adm</EntityName>
                </Entity>
            </Entities>
        </UserRoleAssignment>
   </UserRoleAssignments>
</ZantazAccountManagement>

...
...
...
items marked in red are common tags in this xml. I am not sure how I can print them while working on while loop. Can any one help me with this please?
One way to do it would be to keep appending and "building" two different strings - one for all users and the other for all user role assignments inside the while loop.
Once you're done with the loop, append them and print to XML file.

Something like the following (updated code is in red):

Code:
$ 
$ cat csv2xml_v1.pl
#!perl
use strict;
# Open the ch2_xml_users.csv file for input
open(CSV_FILE, "ch2_xmlusers.csv") || die "Can't open file: $!";
# Open the ch2_xmlusers.xml file for output
open(XML_FILE, ">ch2_xmlusers.xml") || die "Can't open file: $!";
# Print the initial XML header and the root element
print XML_FILE "<ZantazAccountManagement xmlns\=\"http\:\/\/www\.zantaz\.com\/\" xmlns\:xsi\=\"http\:\/\/www\.w3\.org\/2001\/XMLSchema\-instance\" xmlns\:ns1\=\"http\:\/\/www\.zantaz\.com\/\" xsi\:schemaLocation\=\"http\:\/\/www\.zantaz\.com\/ schema\.xsd\" ns1\:action\=\"UPDATE\">";
print XML_FILE " <BillingGroups>
        <BillingGroup>
            <Name>testdomain1</Name>
            <Type>-</Type>
            <CustomerName>testdomain1</CustomerName>
        </BillingGroup>
    </BillingGroups>";
my $all_users = "";
my $all_user_role_assignments = "";
# The while loop to traverse through each line in users.csv
while(<CSV_FILE>) {
    chomp; # Delete the new line char for each line
    # Split each field, on the comma delimiter, into an array
    my @fields = split(/,/, $_);
    $all_users .= <<"EOF";
       <User>
           <UserName>$fields[0].$fields[1].$fields[3].$fields[4]</UserName>
               <Password>
               <Value>Skyline\@123</Value>
               </Password>
           <BillingGroupName>$fields[4]</BillingGroupName>
           <DataSource>sa</DataSource>
           <UserProfile>
               <LastName>$fields[1]</LastName>
               <FirstName>$fields[0]</FirstName>
               <ContactEmail>$fields[2]</ContactEmail>
               <SendItToMe>nobody\@zantaz.com</SendItToMe>
           </UserProfile>
       </User>
EOF
    $all_user_role_assignments .= <<"EOF";
    <UserRoleAssignment>
           <UserName>$fields[0].$fields[1].$fields[3].$fields[4]</UserName>
           <RoleName>DomainAdmin</RoleName>
           <Entities>
               <Entity ns1:type="Applications">
                   <EntityName>adm</EntityName>
               </Entity>
           </Entities>
       </UserRoleAssignment>
       <UserRoleAssignment>
           <UserName>$fields[0].$fields[1].$fields[3].$fields[4]</UserName>
           <RoleName>Researcher</RoleName>
           <Entities>
               <Entity ns1:type="Applications">
                   <EntityName>ds</EntityName>
               </Entity>
           </Entities>
       </UserRoleAssignment>
EOF
}
print XML_FILE "\n<DomainName>testdomain1</DomainName>\n<Users>\n".$all_users."\n</Users>\n";
print XML_FILE "<UserRoleAssignments>\n".$all_user_role_assignments."\n</UserRoleAssignments>\n";
# Close the root element
print XML_FILE "</ZantazAccountManagement>";
# Close all open files
close CSV_FILE;
close XML_FILE;
 
$
$

This User Gave Thanks to durden_tyler For This Post:

Previous Thread | Next Thread
Thread Tools Search this Thread
Search this Thread:
Advanced Search

Test Your Knowledge in Computers #855
Difficulty: Medium
JSX (JavaScript XML) is an extension to the JavaScript language syntax.
True or False?

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Create a XML file for each row from the csv file

I have a CSV file that looks like this: File,Name,birthdate,Amount File1.xml,Name1,01.02.19,1000 File2.xml,Name2 01.02.20,1000 File3.xml,Name3,01.02.21,1000 I need it to turn it into an XML file for each row, My ultimate goal is for the File1.xml look like this: <?xml version="1.0"... (5 Replies)
Discussion started by: lxdorney
5 Replies

2. UNIX for Advanced & Expert Users

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... (3 Replies)
Discussion started by: laknar
3 Replies

3. Shell Programming and Scripting

Extract strings from XML files and create a new XML

Hello everybody, I have a double mission with some XML files, which is pretty challenging for my actual beginner UNIX knowledge. I need to extract some strings from multiple XML files and create a new XML file with the searched strings.. The original XML files contain the source code for... (12 Replies)
Discussion started by: milano.churchil
12 Replies

4. Shell Programming and Scripting

awk read column csv and search in other csv

hi, someone to know how can i read a specific column of csv file and search the value in other csv columns if exist the value in the second csv copy entire row with all field in a new csv file. i suppose that its possible using awk but i m not expertise thanks in advance (8 Replies)
Discussion started by: giankan
8 Replies

5. Programming

help need in the perl script that create one xml file form multiple files.

Hi every one, Please excuse me if any grammatical mistakes is there. I have multiple xml files in one directory, I need to create multiple XML files into one XML file.example files like this</p> file1:bvr.xml ... (0 Replies)
Discussion started by: veerubiji
0 Replies

6. Programming

Extract xml data and create word document using perl.

Hi, I have large xml data file.I need to extract node and some tags in the node and after I need to create word document. my XMl data is look like as below -<student> <number>24</number> <education>bachelor</bachelor> <specialization>computers</specialization> ... (3 Replies)
Discussion started by: veerubiji
3 Replies

7. Programming

extract xml data and create word document using perl.

hi, i have large xml file which contains students information, i need to extract student number and some address tags and create a word document for the extracted data. my data looking llike this <student> <number>24</number> <education>bachelors</education> ... (1 Reply)
Discussion started by: veerubiji
1 Replies

8. Shell Programming and Scripting

Create an XML tree using perl

Hi, I am having an xml file which looks like this: <Nodes> <Node> <Nodename>Student</Nodename> <Filename>1.txt</filename> <Node> <Nodename>Dummy</Nodename> <Filename>22.txt</filename> </Node> </Node> </Nodes> The text files will have data like this: #1.txt... (8 Replies)
Discussion started by: vanitham
8 Replies

9. Shell Programming and Scripting

How to create a xml file using Perl Script

Hi All, I have some data which needs to be saved in the xml file format.Can you guys please let me know how to do this using perl script. NOTE: the template of the xml file shall be depending on validation of the data done for some requirements. Basically to summarise, the fields in the xml... (5 Replies)
Discussion started by: girish.raos
5 Replies

10. Shell Programming and Scripting

read mp3 filename and create one XML for each file

Hi: I have a collection of mp3s and I need to create 1 xml file per mp3. I have: recording1.mp3 recording2.mp3 etc and I want to generate this kind of files. recording1.xml recording2.xml and inside each xml file I need to add a url prefix and then the filename at the end. ... (4 Replies)
Discussion started by: jason7
4 Replies

Featured Tech Videos