Go Premium for a chance to win a PS4. Enter to Win

x
?

walk thru an xml file

Posted on 2017-11-14
3
Medium Priority
?
26 Views
Last Modified: 2017-11-18
I was able to retrieve the attached file thru a soap call to a web service, now i need to convert this xml file to a .csv file, is there an easy method of walking thru the xml file to extrtact the fields i need?
Tempxml3.xml
0
Comment
Question by:durick
3 Comments
 
LVL 54

Expert Comment

by:Ryan Chong
how frequent you wish to do such transformation from xml to csv?

my first thought was loading your xml into a database (like MS SQL), and there try find a way to export it out as csv.

from your xml file, it seems that it contains Customer profile, credit requests and its details?
0
 

Author Comment

by:durick
will need to run this at least monthly, i tried importing into access, but it had syntax errors
0
 
LVL 39

Expert Comment

by:BillDL
The first thing I would do manually is rename the XML file extension to *.TXT, open the XML file in a text editor, and delete all the text and XML tags down to the start of:
<Vendor Name=
so, in this case you would be deleting:
e:\AccessData\Tempxml3.xml  <?xml version="1.0" encoding="utf-8"?><soap:Envelope xmlns:soap="http://schemas.xmlsoap.org/soap/envelope/" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"><soap:Body><CEMgetAttendenceResponse xmlns="http://www.pli.edu/public/CEM/"><CEMgetAttendenceResult><CEM xmlns="">

Open in new window

Then delete the closing tags for them at the end of the file starting from;
</CEM>
so in this case you would be deleting:
</CEM></CEMgetAttendenceResult></CEMgetAttendenceResponse></soap:Body></soap:Envelope>

Open in new window

It is probably those tags, and certainly the opening file path at the start of the file, that are causing "syntax errors".

That should leave you with what appears to be the unique course to which the XML file refers:
<Vendor Name="Practising Law Institute" VendorFirmID="152413">

Open in new window

You could use that as the new name (or a partial name) for the file that you want as a CSV file, for example: 152413_Practicing_Law_Institute.
You can then delete that complete tag and matching closing tag </Vendor>

If you save the file now, you will have a file that starts with the first <User id="xxxxxx"> tag and has all the matching end tags.  Perhaps that will open now in Access or even import into Excel using the delimiter options.  I have never looked, but it is probable that there could be an online XML to CSV converter, but you would obviously have to bear in mind the privacy of the user data and email addresses contained in the files.

You may now be able to use one of the fancy and enhanced text editors like Notepad++ (https://notepad-plus-plus.org/) that allows Macro recording and do some Find/Replace operations on each of the tag names to find each opening <User id="xxxxxx"> tag and force each instance to a new line.  The alternative would be to find the closing </User> tag and force a new line after that.  You should then have the start of header names and data fields that could be lined up below them.  Further Find and Replace operations would allow you to split the values into a usable delimited text file.  It might be laborious creating a new macro from scratch to get to this stage, but if the XML files are consistent it should only be a one-time job that can be run for every new XML file.  You could probably do the same in MS Word.

Here's an example of your file with each "User ID" starting on a new line.
Tempxml3.txt

The problem I anticipate with parsing these separate long lines of tags and values and delimiting the individual data values is that you have multiple values inside the <CreditDetail> and <Credits> tags.  Would you need ALL of the values?

I will look at some "scripted" ways of splitting the strings into data fields that might work and report back, but I suspect that other experts may suggest better and more streamlined methods in the meantime.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Join & Write a Comment

What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Q&A with Course Creator, Mark Lassoff, on the importance of HTML5 in the career of a modern-day developer.
The viewer will learn how to use the return statement in functions in C++. The video will also teach the user how to pass data to a function and have the function return data back for further processing.
The viewer will be introduced to the member functions push_back and pop_back of the vector class. The video will teach the difference between the two as well as how to use each one along with its functionality.

916 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question