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
ZPOOL: Create a new zpool for zfs filesystems
Viewed 1713 times since Sun, Jun 3, 2018
Linux – How to check the exit status of several piped commands
Viewed 2576 times since Wed, Jul 25, 2018
Linux 20 Netstat Commands for Linux Network Management
Viewed 9047 times since Mon, Sep 21, 2020
bash mistakes This page is a compilation of common mistakes made by bash users. Each example is flawed in some way.
Viewed 8587 times since Sun, Dec 6, 2020
Fedora 32: Simple Local File-Sharing with Samba CIFS Linux
Viewed 8255 times since Sun, Dec 6, 2020
12 Tcpdump Commands – A Network Sniffer Tool
Viewed 8459 times since Fri, Jul 27, 2018
List usernames instead of uids with the ps command for long usernames
Viewed 1879 times since Wed, Jul 25, 2018
java Simple Java JDBC Program To Execute SQL Select Query
Viewed 2652 times since Sun, Jan 9, 2022
stunnel bacula
Viewed 1693 times since Fri, Sep 28, 2018
Using IOzone for Linux disk performance analysis
Viewed 6901 times since Wed, Jul 25, 2018