cancel
Showing results for 
Search instead for 
Did you mean: 

XML to CSV convertion in groovy script in sap cpi

former_member538292
Participant
0 Kudos

Hello All,

I have converted xml to csv using groovy script because of standard pallet function is not given expected result,

I tried for one header and one line item it working fine below is the source code.

import com.sap.gateway.ip.core.customdev.util.Message;
import groovy.xml.*;
def Message processData(Message message) {
	def body = message.getBody(java.lang.String) as String;
	def ag = new xml2csv()
	message.setBody(ag.execute(body))
	return message
}

class xml2csv {
	def execute(ins) {
		//define writer 
		def writer = new StringWriter()
		//parse input
		def parsedXml = new XmlParser().parseText(ins)
		
		def content = new XmlSlurper().parseText(ins)
		def header = content.Headers.children().collect().join(',')
		def csv = content.Items.inject(header){ result, row ->
		 [result, row.children().collect().join(',')].join("\n")
		}
		println csv.toString()
		
		return csv.toString()
	}
	
}	
INPUT:

<Root>
	<Headers>
		<string>FirstName</string>
		<string>LastName</string>
		<string>Title</string>
		<string>Birthdate</string>
	</Headers>
	<Items>
		<string>Gabe</string>
		<string>Meyer</string>
		<string>Senior Robot</string>
		<string>1982-06-07Z</string>
	</Items>
</Root>
OUTPUT:

FirstName,LastName,Title,Birthdate

Marley,Paige,Senior Robot,1999-06-07Z

But my input is two headers and two line items with different names

INPUT:
<Root>
        <Headers>
		<string>FirstName</string>
		<string>LastName</string>
		<string>Title</string>
		<string>Birthdate</string>
	</Headers>
	<Items>
		<string>Gabe</string>
		<string>Meyer</string>
		<string>Senior Robot</string>
		<string>1982-06-07Z</string>
	</Items>
        <HeadersB2BUNIT>
		<string>FirstName</string>
		<string>LastName</string>
		<string>Title</string>
		<string>Birthdate</string>
	</HeadersB2BUNIT>
        <B2BUNIT>
		<string>Marley</string>
		<string>Paige</string>
		<string>Senior Robot</string>
		<string>1999-06-07Z</string>
	</B2BUNIT>

</Root>

i tried some adjustments but not went well could please help me on same script or new script .

Thanks,

Kumar.

0 Kudos

Did you find a solution? Have the same input, with two headers and n lines.

former_member538292
Participant
0 Kudos

Hi Pablo,

Yes, I write below two lines for each header and line item.

def header = content.Headers.children().collect().join(',')
		def csv = content.Items.inject(header){ result, row ->
		 [result, row.children().collect().join(',')].join("\n")
		}

Accepted Solutions (0)

Answers (3)

Answers (3)

psanjai
Discoverer
0 Kudos

If you having input is two headers and two line items with different names, you can use below script to get response: like Record_Identification_Code_1 & Record_Identification_Code_2 in one root heder.

import com.sap.gateway.ip.core.customdev.util.Message
import java.nio.charset.StandardCharsets
import java.io.OutputStreamWriter
import groovy.xml.*

def Message processData(Message message) {
def payload = message.getBody(java.lang.String.class)
def root = new XmlParser().parseText(payload)
def csv = new StringWriter()

// Write header row
root.children().first().children().each { field ->
csv.write(field.name())
if (field != root.children().first().children().last()) {
csv.write('')
}
}
csv.write('\n')

// Write data rows
root.children().each { record ->
record.children().each { field ->
csv.write(field.text())
if (field != record.children().last()) {
csv.write('')
}
csv.write('\n')
}
csv.write('\n')
}

message.setBody(csv.toString())
return message
}

 

markangelo_dihiansan
Active Contributor
0 Kudos

Hello,

I know my answer is a bit late. For the life of me, I can't make .each work. So I tried it using the old java way. Here's the code starting from content:

def content = new XmlParser().parseText(ins)
def children = content.children() //gets all the children of content
def sb = new StringBuffer("")
for(int a=0;a<children.size();a++){
def child = children.get(a).children() //gets the child nodes of each children
for(int b=0;b<child.size();b++){
sb.append(child.get(b).text())
if(b+1!=child.size()){
sb.append(",")
}
}
sb.append("\n")
}
println(sb.toString())

Output

The advantage of this code is that no hard-coding is needed to identify the header and items.

Hope this helps,

Mark

0 Kudos

Hi Kumar,

Usually business documents holds 1 header and multiple line items.

i am curious to know on which document you are working where there are two header.

- avinas