使用XML中定义的标头解析CSV [英] Parse CSV with Headers in defined in XML

问题描述

我有一个CSV文件，其标题在单独的XML中定义，如下所示。

I have a CSV file, whose headers are defined in separate XML as shown below.

<columns>
      <column value="FirstName" required="true"/>
      <column value="LastName" required="false"/>
      <column value="Age" required="true"/>
    </columns>

我需要解析CSV，检查规则并保存。实际上，我需要找到第一列是FirstName，第二列是LastName，基于XML中的位置而不是固定位置。我还有其他规则，比如FirstName必须只是字母，年龄只能是基于列名而不是位置的数字。



所以，我需要按照XML中的列名获取数据，然后应用规则。



任何指针/想法都会有所帮助。

谢谢，

I need to parse the CSV, check rules and save it. Effectively, I need to find that first column is FirstName, second column is LastName based on positions in XML rather than fixed position. I also have other rules which is like FirstName must be alphabets only, age can be number only which are based on column name and not position.

So, I need to get the data as per column name in XML and then apply rules.

Any pointer/idea will be helpful.
Thanks,

推荐答案

这个解决方案将解析XML，CSV文件并将数据放在数据表中。您可以将其更改为适合您要求的任何形式。以下是步骤：

解析XML

解析XML并将列名保存在

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>

用于在解析CSV文件后选择所需的列。为了使其工作，您必须安装HtmlAgilityPack。在包管理器控制台中运行以下命令进行安装：

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

并添加使用HtmlAgilityPack 给你上课。

这是解析XML的代码（我使用你提供的XML样本）：

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"));
            }

现在列名列在列名列表中。



Parse CSV文件

这是解析CSV并加载到datatable中的最简单方法之一。假设CSV包含列名作为第一行。

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(//filepath);
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.

这篇关于使用XML中定义的标头解析CSV的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！