Export XML data to excel

Question

0.00/5 (No votes)

See more:

XML:

XML

<?xml version="1.0" encoding="ISO-8859-1"?> 
    <IMPORT ExportDate="2016-03-02" Exercise="1" User="asa" Version="8.73" BusinessUnit="abc(1236)">
        
       <BusinessUnitAdmin>
        ...
        ...
        </BusinessUnitAdmin>
       <BusinessUnit>
        ..
        ..
        </BusinessUnit>
        <Exercise>
        ..
        ..
         </Exercise>
         <Exercise>
         ..
         ..
         </Exercise>
         <Contact>
         ..
         </Contact>
     </IMPORT>

I am trying to export this xml to excel, while also maintaining the schema of xml.
For this, i have tried converting the xml to Dataset and then exporting it to excel.
It works correctly if i remove the root element - IMPORT from the XML. However since it has '**BusinessUnit** as attribute, its preventing to read "BusinessUnit" as element.
For this, i am trying to read XML from second element `- <businessunitadmin>` ; but unable to do.

C# Code :

DataSet ds = new DataSet();
string path = "C:\\abc\\xyz.xml";
ds.ReadXml(path);                       //throwing error
ExportDataSetToExcel(ds);

Error : "A column named 'BusinessUnit' already belongs to this DataTable: cannot set a nested table name to the same name."

What I have tried:

I have tried using LINQ :

XDocument xdoc = XDocument.Load(path);
XElement import = xdoc.Element("IMPORT");
XElement contact = import.Element("Contact");
StringReader theReader = new StringReader(contact.ToString());
ds.ReadXml(theReader);

It works correctly,but this way, i have to manually read all elements.

I also tried reading elements using - `var allElements = xdoc.Descendants()`;
but unable to store specfic elements in dataset.

Please help.

Posted 4-Apr-16 23:44pm

Member 12108821

Updated 5-Apr-16 0:14am

v2

Add a Solution

Comments

Andy Lanng 5-Apr-16 6:04am

What's the error it throws?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Andy Lanng · Answer 1 · 2016-04-05T00:14:00

Solution 1

Oh wait - I see!

You cannot have an attribute with the same name as a child element. "BusinessUnit" and "Exercise" are used as both.

To solve this, consider using camelCase for your attributes and TitleCase for your elements:

HTML

<import exportdate="2016-03-02" exercise="1" user="asa" version="8.73" businessunit="abc(1236)">

   <businessunitadmin>
    ...
    ...
    </businessunitadmin>
   <businessunit>
    ..
    ..
    </businessunit>
    <exercise>
    ..
    ..
     </exercise>
     <exercise>
     ..
     ..
     </exercise>
     <contact>
     ..
     </contact>
 </import>

Hope that helps ^_^
Andy

Posted 5-Apr-16 0:14am

Andy Lanng

Comments

Member 12108821 5-Apr-16 6:18am

I know that. But i dont have control over the xml. I have to read it and export to excel.

I can read xml from next Element - BusinessUnitAdmin and discard IMPORT altogether. But even that i am unable to achieve. (filtering out particular element).

Andy Lanng 5-Apr-16 6:22am

DataSet.ReadXml can only perform its task the way it does. There are many other ways to ready in xml. I would usually use a deserializer, but node navigation is probably the best best. Try XmlReader instead.

Joan Magnet 5-Apr-16 10:33am

Have you tried generating a DataSet from XML content?

I'm not sure it works with a non well-formated document.

DataSet.ReadXml

Andy Lanng 5-Apr-16 10:35am

tee hee. Read the OP :P

Member 12108821 6-Apr-16 2:12am

I tried using XMLReader. However, the dataset reads only the first element.

Code:
using (XmlReader reader = XmlReader.Create(path))
{
reader.MoveToContent();
while (reader.Read())
{
if (reader.NodeType == XmlNodeType.Element)
{
if (reader.Name != "IMPORT")
{
XElement el = XNode.ReadFrom(reader) as XElement;
StringReader sr = new StringReader(el.ToString());
ds.ReadXml(sr);

}
}
}
}

So, for every iteration, it reads each element in xml but in the dataset, it only stores - BusinessUnitAdmin table.
What am i doing wrong?