• Status: Open
  • Priority: Medium
  • Security: Public
  • Views: 69
  • Last Modified:

walk thru an xml file

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?
Ryan ChongCommented:
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?
durickAuthor Commented:
will need to run this at least monthly, i tried importing into access, but it had syntax errors
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.
Mohamed iliyasCommented:
Open with excel and save as .csv

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

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now