asked on

First I have to parse XML file and then i have covert the XML file to CSV with ~ separtor

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>
<StandingSettlementInstruction xmlns="http://www.service.cdds.cs.com/ssiData_2">
<StableChangeVersion>2</StableChangeVersion>
<Core>
<LocalId>0171375670</LocalId>
<EffectiveDate>2018-06-24T06:38:47.093-04:00</EffectiveDate>
<DataStatus>Active</DataStatus>
<InternalIdentifier>00000000-0000-0000-0000-000008180d98</InternalIdentifier>
</Core>
<Change>
<LastApprovedVersion>2</LastApprovedVersion>
<LatestReviewTime>2018-06-24T06:38:47.013-04:00</LatestReviewTime>
</Change>
<SystemCodes>
<SystemCodeType>
<Id>NTPA</Id>
</SystemCodeType>
<Value>Y37F40</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>
<LatestReviewers>
<userName>msiepra</userName>
</LatestReviewers>
<TradingAccount>
<Core>
<LocalId>0170485038</LocalId>
</Core>
<SystemCodes>
<SystemCodeType>
<Id>TradingAccountGSID</Id>
</SystemCodeType>
<Value>4973662</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>
<SystemCodes>
<SystemCodeType>
<Id>NTPA</Id>
</SystemCodeType>
<Value>Y37F40</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>
<SystemCodes>
<SystemCodeType>
<Id>FXOPs</Id>
</SystemCodeType>
<Value>L61774</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>
<SystemCodes>
<SystemCodeType>
<Id>SDSFB</Id>
</SystemCodeType>
<Value>MARSH38LONGB</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>
</TradingAccount>
<Country>
<IsoCode>DE</IsoCode>
</Country>
<Security>
<Id>COB</Id>
</Security>
<SettlementMethod>
<Id>EURO_CLEAR</Id>
</SettlementMethod>
<Currency>
<IsoCode>EUR</IsoCode>
</Currency>
<DefaultSsi>true</DefaultSsi>
<TermsOfDelivery>
<Id>FOP</Id>
</TermsOfDelivery>
<SettlementType>NON_CASH</SettlementType>
<ClassOfAccount>BOTH</ClassOfAccount>
<SettlementChainEntries>
<StepType>NonCashDeagReag</StepType>
<IdentifierType>
<Id>SWIFT_BIC</Id>
</IdentifierType>
</SettlementChainEntries>
<SettlementChainEntries>
<StepType>NonCashDecuRecu</StepType>
<IdentifierType>
<Id>SWIFT_BIC</Id>
</IdentifierType>
</SettlementChainEntries>
<SettlementChainEntries>
<StepType>NonCashPset</StepType>
<Bic>MGTCBEBEECL</Bic>
<AccountNumber>47950</AccountNumber>
<IdentifierType>
<Id>SWIFT_BIC</Id>
</IdentifierType>
</SettlementChainEntries>
<RecDel>BOTH</RecDel>
<CptyFirm>CPTY</CptyFirm>
<CountryOfSettlement/>
<SettleTypeOverride>
<Id>EE</Id>
</SettleTypeOverride>
</StandingSettlementInstruction>

First I have to parse XML file and then
I have an XML file I need convert this into csv file with '~' seperator
Could you please help with the Code.

example output CSV.
StableChangeVersion ~ Core~Local id~trandingaccount~systemcode
2 ~ NA~124~8764~hari

Many Thanks in Advance.
Hari

Flabio Gates

What have you tried so far? For XML, I usually use ElementTree and for CSV I use the csv module.

>>> import sys
>>> from xml.etree import ElementTree
>>> import csv
>>> f = open(r"C:\Users\flabio\Desktop\StandingSettlementInstruction.xml")
>>> tree = ElementTree.parse(f)
>>> f.close()
>>> w = csv.writer(sys.stdout, delimiter='~')
>>> w.writerow(range(10))
0~1~2~3~4~5~6~7~8~9
>>> LocalId = tree.find('{http://www.service.cdds.cs.com/ssiData_2}Core/{http://www.service.cdds.cs.com/ssiData_2}LocalId')
>>> LocalId.text
'0171375670'
>>> LastApprovedVersion = tree.find('{http://www.service.cdds.cs.com/ssiData_2}Change/{http://www.service.cdds.cs.com/ssiData_2}LastApprovedVersion')
>>> LastApprovedVersion.text
'2'
>>> SystemCodes = tree.find('{http://www.service.cdds.cs.com/ssiData_2}SystemCodes/{http://www.service.cdds.cs.com/ssiData_2}Value')
>>> SystemCodes.text
'Y37F40'
>>> w.writerow(['SystemCodes', 'LastApprovedVersion', 'LocalId']) # headers
SystemCodes~LastApprovedVersion~LocalId
>>> w.writerow([SystemCodes.text, LastApprovedVersion.text, LocalId.text]) # values
Y37F40~2~0171375670
>>>

Open in new window

hari krishna

ASKER

I have tried using the below scripts;
import os, sys
import csv
import xml.etree.ElementTree as ET
file_name='SSI_sample.txt'
cd = os.path.dirname(os.path.abspath(file_name))
#data_from_file = open(etree, 'r')
NAMESPACE = 'http://www.service.cdds.cs.com/ssiData_2'

#def remove_name_space(value):
# cleaned_key = re.sub(NAMESPACE, '', value)
# return cleaned_key

#def add_name_space(value):
# add_key = (NAMESPACE + value)
# return add_key

#def add_name_space_to_xpath(value):
# path_list = value.split('/')
# result_xpath = []
# for path_keyword in path_list:
# result_xpath.append(add_name_space(path_keyword))

#return '/'.join(result_xpath)

#def find_attribute_value_from_xpath(xpath_value):
# result = dom.findall(add_name_space_to_xpath(xpath_value))
# for result_iter in result:
# return result_iter.text

# ITERATE THROUGH ALL XML FILES
for item in os.listdir(cd):
if item.endswith(".xml"):
tree = ET.parse(os.path.join(cd,item))

subroot = tree.xpath("//StableChangeVersion")

with open(os.path.join(cd,'MultipleXPaths.csv'), 'ab') as m:
writer = csv.writer(m)

for i in range(1,len(subroot)+1):
nodes = tree.xpath('//StableChangeVersion[{0}]//*'.format(i))

cols = []
rows = []
for elem in nodes:
cols.append(elem.tag)
rows.append(elem.text.replace('\n','').strip())

if i == 1:
print (', '.join(cols)+"\n")
writer.writerow(cols)

print (', '.join(rows)+"\n")
writer.writerow(rows)

But it is not working.

The script is given by you is working fine but the thing . How can I use Xpath for the same.

hari krishna

ASKER

Hi flabio,

I have facing the issue in the code you have sent .
There are Local id in the XML file . But I am able the get for one local id for the Core tag.
there is another local Id in trading account tag.
<Core>
<LocalId>0171375670</LocalId>
<EffectiveDate>2018-06-24T06:38:47.093-04:00</EffectiveDate>
<DataStatus>Active</DataStatus>

<TradingAccount>
<Core>
<LocalId>0170485038</LocalId>
</Core>

How we can get both Local id in the output

Flabio Gates

How we can get both Local id in the output

You start from TradingAccount to get the 2nd LocalId.

TradingAccountLocalId = tree.find('{http://www.service.cdds.cs.com/ssiData_2}TradingAccount/{http://www.service.cdds.cs.com/ssiData_2}Core/{http://www.service.cdds.cs.com/ssiData_2}LocalId')

Open in new window

hari krishna

ASKER

Many thanks Gates.

As per your code i able to see data . But i want to generate the CSV file in the server with Same ouput.

Could you please help in this

hari krishna

ASKER

Hi Gates,

I have written the code to covert XML file with CSV ~ separator. But I am unable to get CSV output file .
My code:
import xml.etree.ElementTree as ET
import csv

tree = ET.parse("SSI_sample.txt")
root = tree.getroot()
xml_data_to_csv=open('SSIdata.csv','w')
list_head = []
count=0
for element in root .findall('{http://www.service.cdds.cs.com/ssiData_2}StandingSettlementInstruction'):
list_nodes=[]
if count==0:
StableChangeVersion = element.find('{http://www.service.cdds.cs.com/ssiData_2}StableChangeVersion').tag
list_head.append(StableChangeVersion)
LocalId = element.find('{http://www.service.cdds.cs.com/ssiData_2}Core/{http://www.service.cdds.cs.com/ssiData_2}LocalId').tag
list_head.append(LocalId)
TradingAccountLocalId = element.find('{http://www.service.cdds.cs.com/ssiData_2}TradingAccount/{http://www.service.cdds.cs.com/ssiData_2}Core/{http://www.service.cdds.cs.com/ssiData_2}LocalId').tag
list_head.append(TradingAccountLocalId)
csv_writer.writer(list_head)
count=+1
StableChangeVersion=element.find('{http://www.service.cdds.cs.com/ssiData_2}StableChangeVersion').text
list_nodes.append(StableChangeVersion)
LocalId = element.find('{http://www.service.cdds.cs.com/ssiData_2}Core/{http://www.service.cdds.cs.com/ssiData_2}LocalId').text
list_nodes.append(LocalId)
TradingAccountLocalId = element.find('{http://www.service.cdds.cs.com/ssiData_2}TradingAccount/{http://www.service.cdds.cs.com/ssiData_2}Core/{http://www.service.cdds.cs.com/ssiData_2}LocalId').text
list_nodes.append(TradingAccountLocalId)
csv_writer.writer(list_nodes)
xml_data_to_csv.close()
##etree.dump(item)
could you pls help in this.

Many Thanks,
Hari.

hari krishna

ASKER

Hi Gates,

My Apologies for asking so more help in writing the Code . First of all many thanks for the code you have given. could you please help . I am facing one more issue.
SystemCodes>
<SystemCodeType>
<Id>TradingAccountGSID</Id>
</SystemCodeType>
<Value>4973662</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>
<SystemCodes>
<SystemCodeType>
<Id>NTPA</Id>
</SystemCodeType>
<Value>Y37F40</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>
<SystemCodes>
<SystemCodeType>
<Id>FXOPs</Id>
</SystemCodeType>
<Value>L61774</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>
<SystemCodes>
<SystemCodeType>
<Id>SDSFB</Id>
</SystemCodeType>
<Value>MARSH38LONGB</Value>
<ValueStatus>Active</ValueStatus>
</SystemCodes>

How can I get the output of all systemcodes value and valuelist and systemcodetype id

Ex SystemCodesTypeId|Value|ValueStatus
NTPA|Y37F40|Active
SDSFB|MARSH38LONGB|Active.

Could you pls ASAP.

Many Thanks,
Hari.

This question needs an answer!

Become an EE member today

7 DAY FREE TRIAL

Members can start a 7-Day Free trial then enjoy unlimited access to the platform.

View membership options

Learn why we charge membership fees

We get it - no one likes a content blocker. Take one extra minute and find out why we block content.