CSV字符串到DataTable

Question

我有以下字符串，我想把它转换成DataTable

"Id,Name ,Deptrn1,Mike,ITrn2,Joe,HRrn3,Peter,ITrn"

我可以用String创建它。对集合进行拆分和迭代。但我需要有效的方法(使用c# 4.0的特性)如何使用LINQ或lambda创建表

Answer 1

我不知道你在找什么:

string s = "Id,Name ,Deptrn1,Mike,ITrn2,Joe,HRrn3,Peter,ITrn";
        DataTable dt = new DataTable();
        string[] tableData = s.Split("rn".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();

Answer 2

我认为这个方法会很有用。此方法可用于CSVString或CsvFilePath。如果你想转换CsvFilePath，那么你必须指定第一个路径为真，否则为假。

public DataTable ConvertCsvStringToDataTable(bool isFilePath,string CSVContent)
{
    //CSVFilePathName = @"C:test.csv";
    string[] Lines;
    if (isFilePath)
    {
        Lines = File.ReadAllLines(CSVContent);
    }
    else
    {
        Lines = CSVContent.Split("rn".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;
}

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