Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


walk thru an xml file

Posted on 2017-11-14
Medium Priority
Last Modified: 2017-11-29
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?
Question by:durick
LVL 55

Expert Comment

by:Ryan Chong
ID: 42368317
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?

Author Comment

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

Expert Comment

ID: 42369511
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;
so in this case you would be deleting:

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.

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.

Expert Comment

by:Mohamed iliyas
ID: 42382812
Open with excel and save as .csv

Note: Remove the first line in your file (i.e., the path where the xml is available)

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Join & Write a Comment

If you are a mobile app developer and especially develop hybrid mobile apps then these 4 mistakes you must avoid for hybrid app development to be the more genuine app developer.
When you discover the power of the R programming language, you are going to wonder how you ever lived without it! Learn why the language merits a place in your programming arsenal.
Six Sigma Control Plans
Suggested Courses

580 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