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
An easier way to manage disk decryption at boot with Red Hat Enterprise Linux 7.5 using NBDE
Viewed 7025 times since Mon, Aug 6, 2018
RHEL: Remove existing SAN LUNs
Viewed 13798 times since Sat, Jun 2, 2018
OpenSSL – sprawdzanie czy klucz pasuje do certyfikatu
Viewed 2472 times since Thu, May 24, 2018
Improve security with polyinstantiation
Viewed 12869 times since Fri, May 15, 2020
Secure NTP with NTS
Viewed 8093 times since Sun, Dec 6, 2020
RHEL: udev rules basics
Viewed 7937 times since Sat, Jun 2, 2018
stunnel: Authentication
Viewed 8975 times since Fri, Sep 28, 2018
Build a simple RPM that packages a single file
Viewed 8086 times since Sat, Jun 2, 2018
RHCS6: Create a new Logical Volume / Global Filesystem 2 (GFS2)
Viewed 1994 times since Sun, Jun 3, 2018
ubuntu How to Reset Forgotten Passwords in Ubuntu 16.04
Viewed 2759 times since Tue, Dec 8, 2020