merge xml file with excel file

rendy jaya

14y3.6k.NET

Hi,

i want to ask how to merge xml file with some colums in excel file.
let say i have a xml file like this

and in my excel file i have some colums, for example

course	score
math	1
science	2

now i want to combine my xml file with this excel file, notice for the excel file i just want to take colums course

the result that i want like this.

can somebody help me? i really need help for this.

and notice i want to do it in C# .net.

thank you very much.

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Vulpes14y ago
Try this:

using System;
using System.Linq;
using System.Xml.Linq;

class Program
{
static void Main()
{
XElement create = XElement.Load("rendy.xml");
XElement create2 = XElement.Load("rendy2.xml"); // from Excel

foreach(XElement student in create2.Elements())
{
XElement[] students = student.Elements().ToArray();
XAttribute first = students[0].Attributes().First();
XAttribute second = students[0].Attributes().ElementAt(1);

if (first.Name != "name" || second.Name != "value") continue;
string name = (string)first;
string value = (string)second;

XElement foundStudent = (from s in create.Elements()
from s2 in s.Elements()
where (string)s2.Attributes().First() == name &&
(string)s2.Attributes().ElementAt(1) == value
select s).FirstOrDefault();

if (foundStudent != null)
{
for(int index = 1; index < students.Length; index++)
{
foundStudent.Add(students[index]);
}
}
}

create.Save("rendy3.xml"); // merged file
Console.WriteLine(create);
Console.ReadKey();
}
}
0
rendy jaya14y ago
hi all,,

thanks for your answer, so i cant combine directly these 2 file.

now i already convert my excel sheet into xml file.

do you know how to combine these two?
i mean like this, first xml file

and second xml file like this

so now i want to combine these two xml file based on the first xml.i mean took all value in first xml file and from the second xml take only the second node or thrid node or both,
example like this.

anyone know how to solve for this one? i really need help..thanks in advance.

0
George eric14y ago
Truly, There is no direct way to merge the two different formats. I can direct you to a article on managing Excel and Xml. you can see http://www.codeproject.com/Tips/371595/Generate-Excel-from-XML-and-Generate-XML-from-Exce.
0
rendy jaya14y ago
can you explain me how to do it? thanks before
0
Suthish Nair14y ago
There is no direct way to achive this logic. You need to write some code to load the excel file in dataset or by using sqlbulkcopy method and retrive column and append to existing xml file. :)
0