Convert XML to Excel/Table Grid (VS 2005, Framework 2.0)

Topic

New Reply

I have written a system for automated production of simple web-based surveys (.NET). The system uses XML to produce the web survey UI and answers are stored in an auto-generated XML file. I need to devise a way to take the XML file generated by the survey and make it useful for reports and analysis. So far I have had remarkably little luck doing this. There is a website that does something useful: http://www.aridolan.com/xml2table/x2t.aspx%5B/url%5D will load an xml file and allow you to export the details displayed in a Grid to Excel. I am trying to duplicate this functionality but my limited understanding is, as is often the case, getting in the way. So, I wonder if you .NET loungers might already have some solutions or advise to help me with this…

TIA

Reply | Quote

Replies

Hi Steve,

I’ve used several techniques in the past for converting data between multiple formats with code and/or tools.

The easiest conversion would be to generate a CSV (comma-separated) format that can be easily opened with Excel. Granted, the user would want to immediately save it into some other format in order to use it for anything meaningful. You can set the HTTP Header (MIME) to the XLS signature so that the user automatically sees the Excel icon when they attempt to download/open the output from this method. Users often appreciate that little touch.

I’m not sure if you have an instance of SQL Server available, but you can easily dump the results into a SQL table, which can be accessed by Excel or Access via ODBC/OLEDB (using the built-in tools in each respective application).

I haven’t looked into the office 2007 formats yet, but I’m sure you can find the schema out there somewhere so that you could convert the XML into an XLSX format, although the total effort involved here might be significantly higher than either of the other two.

Hope this helps!

Reply | Quote

umm – ok – so how do I make an XML file into a CSV file? I have had little luck with doing anything interesting with the XML output…

Reply | Quote

Why convert it to a csv file? The XML is already in a table format so you should merely have to use an XMLReader to get at it.

Reply | Quote

Charlotte,

I was imagining converting the data into CSV as an intermediate means of getting it from a browser into Excel. Short of having a SQL Server instance to leverage, this seems like the quickest (cheapest) and easiest route to go… Just my

Reply | Quote

I don’t understand what you mean by ‘XML is already in table format.’ XML is, AFAIK, ‘tree’-based and the issue with converting it to a table format involves going thru the nodes and reading either the node name or a value assigned to it. For example, this XML file:

steve
skelton
details

steve
skelton
details
Somewhat satisfied
Disagree
details

has the root node Answers; 2 actual survey responses (AnswerSet) and the actual responses to questions (Answer and text of Answer). In order to make this into a table, you’d have to read the number of AnswerSet nodes (corresponding to the number of rows in a table), the Question Name (corresponding to a Column name) and the actual response (the data). I have not been able to find an easy method for doing this except thru the website “sample” application referenced earlier. I can, with some effort, read all of these values using VBA and MSXML v. 6.0 but I was trying to get the information out in a way that doesn’t require so many steps. What keeps me going on this is the idea that there is some technique that I am missing that should make this easier than it appears to be. Perhaps XMLReader is that “promised land”. I will investigate…

Reply | Quote

Did I understand correctly that you wrote the entire system? If that’s the case, then why did you choose to output XML if you have trouble doing anything interesting with it?

You’ll want to study the classes within the System.XML namespace in the .NET Framework. WIth the objects there, you’ll be able to parse the XML file into an object, iterate through the nodes (and any child nodes) and write this output into some meaningful format.

CSV is VERY easy to write. Simply determine which columns you want to include, create some sort of string variable to hold your values (I’d recommend a StringBuilder), append the value followed by the column. Advance a row, rinse and repeat. When you’ve captured all of the data, dump the output into a text file and set the extension to .CSV.

You’ll find plenty of articles and tutorials with a very minimal amount of searching…

Reply | Quote

Why did I choose XML as an output format? Because I am writing a system to allow a non-technical person to create a web-based survey and the best solution I was able to find involved the use of XML and a .NET parser to setup the survey system on a website. The solution not only uses XML to describe the survey system but stores results in XML. The advantage is ease of deployment (no database to setup or maintain); the problem — as I have encountered it — is to get the XML data from a survey transformed into a db-useable format.

I have had some success with reading the the XML DOM but keep thinking there must be an easier way to do something that strikes me as an utterly common need for XML. Since this project is intended for non-technical people to use, I want to minimize steps for the user. Basically, provide a ‘manage Surveys’ screen with a button that says ‘Tabulate Results’ – no need to show any XML…

Reply | Quote

Your design goals make perfect sense – ease of deployment, portability, etc. However, I think the tradeoff is that you’re going to have to do more reading/writing of XML for input and output.

One “automated” XML-reading method you may find helpful is DataSet.ReadXml(). You can load XML content from almost any source (string, textreader, XmlReader, etc) then manipulate it within the dataset. There’s also a corresponding WriteXml() method.

Otherwise, there is really no way around having to manipulate the XML DOM at some level. A typical approach would be to encapsulate all of your XML reading/writing methods into a separate class that allows you to pass some other object (custom class, dataset, etc) back and forth from the front-end pages. This should get you to the place where you can easily present tabulated results to end users.

You may also consider looking into XSLT as a way to transform the XML for display purposes. I’ve used that several times and it’s pretty capable, depending on the XML format and your display needs.

Reply | Quote

weeell if that’s the way it is that’s the way it is…

FWIW, here’s my DOM-ey solution:

Sub ExploreXMLDom()
Dim xmlDoc As New DOMDocument
Dim oNodeList As IXMLDOMNodeList

xmlDoc.async = False
xmlDoc.Load (“C:InetpubwwwrootOIGSurveyPortalCASO_Survey_Test_answers.xml”)
‘
‘ Get a list of the nodes and display their text.
‘
Set oNodeList = xmlDoc.getElementsByTagName(“AnswerSet”)
NumResponses = oNodeList.length
‘For i = 0 To (oNodeList.length – 1)
Debug.Print “There are ” & NumResponses & ” responses to this survey.”
‘Next
Debug.Print “——————————”

Debug.Print “These are the survey questions:”
Debug.Print “——————————”
Set Node = xmlDoc.getElementsByTagName(“Answer”)
For lngIndex = 0 To (Node.length – 1) / NumResponses <– Note: This will truncate the loop after one pass…
Set NodeContent = Node.Item(lngIndex)
Debug.Print NodeContent.getAttribute("questionId")
Next lngIndex
Debug.Print "——————————"

Debug.Print "These are the survey responses"
Debug.Print "——————————"
For lngIndex = 0 To Node.length – 1
Set NodeContent = Node.Item(lngIndex)
'Debug.Print "Response number " & lngIndex
Debug.Print NodeContent.getAttribute("questionId") & "” & NodeContent.Text
Next lngIndex
End Sub

This code reads the XML file, counts the number of rows (or total responses), outputs a list of the column names and then a set of questions and the response across the entire survey (in this case, two sets). From here one can setup a table to update/write to; the problems at this point are field types for expected data. If you go to the website referenced above, there’s a .NET solution — *somewhere* that can transform some random Joe Blow XML file into a table and thence to Excel, which is better, IMHO, than all this DOM sorting. I just don’t know how to do that. I am fairly satisfied with my solution as outlined above in terms of getting the basic data out of an XML file but wanted to avoid the pain I see ahead…

Reply | Quote

That’s basically the type of approach you’ll want to take. FWIW – I would recommend using the .NET XML objects rather than the external ones, but that’s totally up to you. The method names may differ a little, but the concept is essentially identical.

Now, if you want to take this to the next level, here is a general outline of what you’ll want to do:
– Decide on a structure for storing and presenting the data involved with both the configuration and output of your application. Developers often build custom classes that mirror the data structure (a simple public class with public proerties that match the data type). If you use this approcah, you’ll need to create a second object that’s collection based to store these other items (the signle-item is like a data row and the collection is like a table). You can also use a DataSet – either “virtual” (aka created on the fly) or tied to an XSD that’s created through the designer.
– Take the logic you’re currently using to traverse the XML data and instead, use it to populate the data structure mentioned above. For instance, if you’re using custom classes, iterate each XML Node, instantiate a new class and populate its values, then add it to the collection (rinse and repeat). If you’re using a dataset, iterate the XML nodes, create a new data row and popualte the values, then add to the data table. It’s essentially the same process no matter what sort of objects you choose to use.
– Update the references in your web pages to read from the data object rather than directly from the XML file. This may take a while, but shouldn’t be too bad.

The benefits of this approach are many. This de-couples the front-end from the data source. If you happen to change your data source from XML to SQL Server one day, the only piece that you will need to update is the part that maps the data source to the data object. The front-end could care less where the data comes from as long as it is passed in using the expected object type. This is just one example of the beauty of Object-Oriented development.

Post back if you’d like more detail or direction on any of the above items…

Reply | Quote

thanks for the input – just to make sure we’re on the ‘same page’ here, I am trying to limit user experience to an Access front-end in terms of creating a web survey and tabulating the results. No SQL Server and no .NET web application, if at all possible.

So my intention has been to parse the XML into an INSERT INTO statement that will create a new table with the data loaded. And, yes, every time the user clicks on the ‘tabulate results’ button any existing table data in Access *for that survey file* is deleted and a new replacement is built. I think this will be easier than having to test for and deploy UPDATEs.

The issue for this plan is how to structure the table in terms of data types from a new XML answer file. It helps, in a sense, that the web survey project is limited and doesn’t support any validation – all datatypes will be text(255) OR memo (and this needs to be determined during the parsing); the user can ‘clean up’ a result set once it’s imported. In terms of what I have so far, I think I will have to build a loop to execute successive INSERT INTO data column names and respective values into a table. I want to create a new table for each unique aswer.xml file so the user can tabulate as many surveys as their little management heart desires…. So there’s some ‘meta-database’ operations going on here as well.

If I was able to magically create a table from nested XML, perhaps it’d be easier, but perhaps not much more so than continuing with what I’ve already cobbled together…sorry I seem to be avoiding .NET/OO approaches! Just a ‘stick in the mud’ — but, since I’m stuck with Access 2000 I have to play within certain boundaries.

Reply | Quote

So is your import code written in Access? If you’re using Access to store and manage the configuration and results, I would recommend that you do the importing there too. You should be able to implement a very similar XML DOM traversing logic using an Access module.

Reply | Quote

yes, I agree. I finally got the code to work. At now parses any XML file produced by the web survey and creates an UPDATE SQL statement. now to setup the creation of a table with the correct data types per column…since the code auto-names the answer file based on the user survey name each specific file is different and the name will be used to setup a new table.

The code employs MSXML v 6.0, looping, a select statement and one of my favorite operations, Mod. Took some time and thought to get it to work — and was, as often happens, a humbling experience for yours truly. Programming tends to point out one’s lamentable quality of thought! And the value of ‘sleeping on’ a problem. I’ve attached the XMLDOM walkthru code for your enjoyment and/or heckling…

Reply | Quote