Transform XML to CSV Format | Unix String Pattern Manipulation The Ugly Way

Transform XML to CSV Format | Unix String Pattern Manipulation The Ugly Way

Updated: Jun 22, 2020

 
 
Disclaimer: I do not claim the below method to be the best optimum method of transforming XML to CSV format on the Unix command prompt but it does the job well.
 

In the below example we can going to transform our XML which contains a list of UserName, Name, Email and RoleName to CSV format so that it is readable by Microsoft Excel.

  

UserList.xml

  
<?xml version="1.0"?>
<soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/">
<SOAP-ENV:Header xmlns:SOAP-ENV="http://schemas.xmlsoap.org/soap/envelope/"/>
<soap:Body>
<User_ListResponse xmlns="http://www.xyz.com">
<Success>true</Success>
<Status>
<Id>0</Id>
<ResponseId>1</ResponseId>
<ResponseMessage>OK</ResponseMessage>
<ResponseAdditionalInfo/>
</Status>
<PlatformUsers>
<PlatformUser>
<Id>101</Id>
<Username>user1</Username>
<Name>User 1</Name>
<Email>user1@xyz.com</Email>
<RoleName>Administrator</RoleName>
</PlatformUser>
<PlatformUser>
<Id>102</Id>
<Username>user2</Username>
<Name>User 2</Name>
<Email>user2@xyz.com</Email>
<RoleName>Administrator</RoleName>
</PlatformUser>
<PlatformUser>
<Id>103</Id>
<Username>user3</Username>
<Name>User 3</Name>
<Email>user3@xyz.com</Email>
<RoleName>Developer</RoleName>
</PlatformUser>
</PlatformUsers>
</User_ListResponse>
</soap:Body>
</soap:Envelope>
 

We are going to use a Unix bash script to do our string pattern manipulation and transform the XML to CSV format.

 
 

Create a file using the vi editor and paste the below script.

 

vi convert_xml_to_csv.sh

 
#!/bin/bash
cat UserList.xml | awk '/<PlatformUser>/,/<\/PlatformUser>/' >INTERMEDIATE_1
cat INTERMEDIATE_1 | tr -d ' ' | tr '\n' ' ' >INTERMEDIATE_2
sed -i 's/<PlatformUser>/\n/g' INTERMEDIATE_2
sed -i '/^$/d' INTERMEDIATE_2
sed -i -e '$a\' INTERMEDIATE_2
echo "ID,Username,Name,Email,RoleName" >UserList.csv
cat INTERMEDIATE_2 | while read LINE
do
ID=`echo $LINE | cut -d " " -f1 | cut -d '<' -f2 | cut -d '>' -f2`
USERNAME=`echo $LINE | cut -d " " -f2 | cut -d '<' -f2 | cut -d '>' -f2`
NAME=`echo $LINE | cut -d " " -f3 | cut -d '<' -f2 | cut -d '>' -f2`
EMAIL=`echo $LINE | cut -d " " -f4 | cut -d '<' -f2 | cut -d '>' -f2`
ROLENAME=`echo $LINE | cut -d " " -f5 | cut -d '<' -f2 | cut -d '>' -f2`
echo "${ID},${USERNAME},${NAME},${EMAIL},${ROLENAME}" >>UserList.csv
done
rm INTERMEDIATE*

 

Execute the shell script

 

sh convert_xml_to_csv.sh

  

The bash script converts the XML file into below CSV format readable by MS Excel.

 
 
 
 
0 (0)
Article Rating (No Votes)
Rate this article
Attachments
There are no attachments for this article.
Comments
There are no comments for this article. Be the first to post a comment.
Full Name
Email Address
Security Code Security Code
Related Articles RSS Feed
Using Kerberos security with Server for NFS
Viewed 6950 times since Wed, Jun 27, 2018
30 Examples for Awk Command in Text Processing
Viewed 12066 times since Sat, Nov 24, 2018
LVM: Reduce root PV/VG
Viewed 3526 times since Sat, Jun 2, 2018
How to Clear RAM Memory Cache, Buffer and Swap Space on Linux
Viewed 1001 times since Mon, Nov 23, 2020
Yum Update: DB_RUNRECOVERY Fatal error, run database recovery
Viewed 1953 times since Fri, Jan 17, 2020
RHEL: Adding a boot entry to GRUB/GRUB2 configuration
Viewed 2205 times since Sun, May 27, 2018
RHEL: Displaying/setting kernel parameters - ’sysctl’
Viewed 1657 times since Sat, Jun 2, 2018
How To Find Largest Top 10 Files and Directories On Linux / UNIX / BSD find
Viewed 1313 times since Mon, Oct 29, 2018
Cron YUM How to use yum-cron to automatically update RHEL/CentOS Linux
Viewed 1322 times since Fri, Oct 26, 2018
RHEL: Create a local RPM repository
Viewed 9270 times since Sun, May 27, 2018