将CSV字符串转换为DataTable

Question

我有以下字符串，我想将其转换为DataTable

"Id,Name ,Dept\r\n1,Mike,IT\r\n2,Joe,HR\r\n3,Peter,IT\r\n"

我可以使用String.Split和通过集合进行迭代来创建它，但是我需要一种高效的方法（使用C# 4.0特性）来创建表格。如何使用LINQ或lambda创建表格。

- meetjaydeep

---

可能是一个问题的重复：如何将CSV文件读入.NET DataTable中？ - Ani

我想从字符串中读取，而不是从文件中读取，也不想使用第三方的dll或组件。 - meetjaydeep

Answer 1

我不知道那是否就是你在寻找的内容：

string s = "Id,Name ,Dept\r\n1,Mike,IT\r\n2,Joe,HR\r\n3,Peter,IT\r\n";
        DataTable dt = new DataTable();

        string[] tableData = s.Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
        var col = from cl in tableData[0].Split(",".ToCharArray())
                  select new DataColumn(cl);
        dt.Columns.AddRange(col.ToArray());

        (from st in tableData.Skip(1)
         select dt.Rows.Add(st.Split(",".ToCharArray()))).ToList();

- AlaaL

---

dt.Rows.Add(from st in tableData.Skip(1) select st.Split(",".ToCharArray()))；这段代码有问题，正确的应该是：foreach (var item in tableData.Skip(1)) { dt.Rows.Add(item.Split(",".ToCharArray())); }。 - meetjaydeep

是的，抱歉你是对的。我已经编辑了我的代码，如果你不想使用foreach，也可以不用它。 - AlaaL

Answer 2

我认为这个方法会很有用。这个方法可以用于CSV字符串或Csv文件路径。如果你想转换Csv文件路径，那么你必须先指定路径是否正确。

public DataTable ConvertCsvStringToDataTable(bool isFilePath,string CSVContent)
{
    //CSVFilePathName = @"C:\test.csv";
    string[] Lines;
    if (isFilePath)
    {
        Lines = File.ReadAllLines(CSVContent);
    }
    else
    {
        Lines = CSVContent.Split("\r\n".ToCharArray(), StringSplitOptions.RemoveEmptyEntries);
    }
    string[] Fields;
    Fields = Lines[0].Split(new char[] { ',' });
    int Cols = Fields.GetLength(0);
    DataTable dt = new DataTable();
    //1st row must be column names; force lower case to ensure matching later on.
    for (int i = 0; i < Cols; i++)
        dt.Columns.Add(Fields[i].ToLower(), typeof(string));
    DataRow Row;
    for (int i = 1; i < Lines.GetLength(0); i++)
    {
        Fields = Lines[i].Split(new char[] { ',' });
        Row = dt.NewRow();
        for (int f = 0; f < Cols; f++)
            Row[f] = Fields[f];
        dt.Rows.Add(Row);
    }
    return dt;
}

- Yuvaraj

Answer 3

using System;
using System.Xml;
using System.Data;
using System.Collections.Generic;
using System.Linq;
using System.Text;

public static string DataTableToString(DataTable dtData)
{
    string sData = null;
    StringBuilder sBuffer = null;
    string Token = null;

    int i = 0;
    int j = 0;

    sBuffer = new StringBuilder();
    sBuffer.Append(@"<TABLE>");

    sBuffer.Append(@"<TR>");
    foreach (DataColumn Col in dtData.Columns)
    {
        sBuffer.Append(@"<TH ColType='")
            .Append(Convert.ToString(Col.DataType))
            .Append(@"'>")
            .Append(Col.ColumnName.Replace("&", ""))
            .Append(@"</TH>");
    }
    sBuffer.Append(@"</TR>");

    i = 0;
    foreach (DataRow rw in dtData.Rows)
    {
        sBuffer.Append(@"<TR>");

        j = 0;
        foreach (DataColumn Col in dtData.Columns)
        {
            if (!Convert.IsDBNull(rw[Col.ColumnName]))
            {
                Token = Convert.ToString(rw[Col.ColumnName]);
            }
            else
            {
                Token = null;
            }

            sBuffer.Append(@"<TD>").Append(Token).Append(@"</TD>");

            j++;
        }

        sBuffer.Append(@"</TR>");

        i++;
    }
    sBuffer.Append(@"</TABLE>");
    sData = sBuffer.ToString();

    return sData;
}

public static DataTable StringToDataTable(string sXmlData)
{
    DataTable dtData = null;
    XmlDocument xmlDoc = null;
    XmlNode RootNode = null;
    XmlNodeList TRList = null;
    XmlNodeList THList = null;
    XmlNodeList TDList = null;

    int i = 0;
    int j = 0;

    XmlAttribute DataTypeAttrib = null;
    string sDataType = null;
    DataColumn Col = null;
    Type ColType;

    string Token = null;

    DataRow newRw = null;

    xmlDoc = new XmlDocument();
    xmlDoc.LoadXml(sXmlData);

    RootNode = xmlDoc.SelectSingleNode("/TABLE");
    if (RootNode != null)
    {
        dtData = new DataTable();

        i = 0;
        TRList = RootNode.SelectNodes("TR");
        foreach (XmlNode TRNode in TRList)
        {
            if (i == 0)
            {
                THList = TRNode.SelectNodes("TH");
                foreach (XmlNode THNode in THList)
                {
                    DataTypeAttrib = THNode.Attributes["ColType"];
                    sDataType = DataTypeAttrib.Value;
                    ColType = Type.GetType(sDataType);
                    Col = new DataColumn(THNode.InnerText, ColType);

                    if (!dtData.Columns.Contains(Col.ColumnName))
                    {
                        dtData.Columns.Add(Col);
                    }
                }
            }
            else
            {
                newRw = dtData.NewRow();

                j = 0;
                TDList = TRNode.SelectNodes("TD");
                foreach (XmlNode TDNode in TDList)
                {
                    ColType = dtData.Columns[j].DataType;

                    Token = TDNode.InnerText;
                    if (!string.IsNullOrEmpty(Token))
                    {
                        try
                        {
                            newRw[j] = Convert.ChangeType(Token, ColType);
                        }
                        catch
                        {
                            if (ColType == typeof(DateTime))
                            {
                                newRw[j] = DateTime.ParseExact(Token, "yyyyMMdd", System.Globalization.CultureInfo.InvariantCulture);
                            }
                        }
                    }
                    else
                    {
                        newRw[j] = Convert.DBNull;
                    }

                    j++;
                }

                dtData.Rows.Add(newRw);
            }

            i++;
        }
    }

    return dtData;
}

- ment0r