Hi,
This solution will the parse the XML, CSV file and have the data in a datatable. You can change it to any form that suits your requirement. Here are the steps:
Parsing XML
Parse the XML and keep the column names in a
List<string></string>
for selecting the required columns once CSV file is parsed. In order for this to work, you have to install HtmlAgilityPack. Run the following command in package manager console to install it:
Install-Package HtmlAgilityPack
and add
Using HtmlAgilityPack
to you class.
Here is the code for parsing the XML (I used the XML you provided for sample):
string columnXML = "<columns><column value=\"FirstName\" required=\"true\"/><column value=\"LastName\" required=\"false\"/><column value=\"Age\" required=\"true\"/></columns>";
HtmlDocument doc = new HtmlDocument();
doc.LoadHtml(columnXML);
List<string> columnNames = new List<string>();
HtmlNodeCollection nodes = doc.DocumentNode.SelectNodes("//columns");
HtmlNodeCollection childNodes = nodes[0].ChildNodes;
foreach (HtmlNode childNode in childNodes)
{
columnNames.Add(childNode.GetAttributeValue("value","NotFound"));
}
Now the column names are present in columnNames list.
Parse CSV file
Here is one of the simplest methods to parse CSV and load into datatable. Assuming CSV contains the column names as the first row.
var lines = File.ReadAllLines(
DataTable testDataTable = new DataTable();
var headers = lines.First().Split(',');
foreach (var header in headers)
{
testDataTable.Columns.Add(header);
}
foreach (var line in lines.Skip(1))
{
testDataTable.Rows.Add(line.Split(','));
}
Select required columns
DataTable finalDataTable = testDataTable.DefaultView.ToTable(false, columnNames.ToArray());
This may not be the optimal solution to your needs. But it gives an idea of how you can do what you want. Hope this helps.