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.



<?xml version="1.0"?>
<soap:Envelope xmlns:soap="">
<SOAP-ENV:Header xmlns:SOAP-ENV=""/>
<User_ListResponse xmlns="">
<Name>User 1</Name>
<Name>User 2</Name>
<Name>User 3</Name>

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.



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


Execute the shell script




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

0 (0)
Article Rating (No Votes)
Rate this article
There are no attachments for this article.
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
RHEL: Services basic management - systemd
Viewed 17220 times since Sat, Jun 2, 2018
Usuwanie spacji z zmiennych w bash
Viewed 2002 times since Tue, May 22, 2018
RHEL: Reserved space on a ext2/ext3/ext4 filesystem
Viewed 3662 times since Sun, May 27, 2018
RHEL: Displaying/setting kernel parameters - ’sysctl’
Viewed 2249 times since Sat, Jun 2, 2018
RHCS6: Show/Add GFS2/GFS journals
Viewed 12072 times since Sun, Jun 3, 2018
HOWTO: Use SSL/port 465 in smarthost stunnel
Viewed 3038 times since Fri, Sep 28, 2018
Linux - How to shutdown or reboot
Viewed 1584 times since Fri, Jun 8, 2018
RHEL: Force system to prompt for password in Single User mode
Viewed 5542 times since Sat, Jun 2, 2018
RHEL: Displaying system info (firmware, serial numbers... )
Viewed 10797 times since Sun, May 27, 2018
“Too many authentication failures” with SSH
Viewed 4604 times since Mon, May 21, 2018