CSV string to DataTable

Question

I have following string, I want to convert it to DataTable

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

I can create it using String.Split and iterating through collection. But I need efficient way (using C# 4.0 features) How to create table using LINQ or lambda.

Answer 1

I don't know if that what are you looking for :

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();

Answer 2

I Think this method will be useful. This method can be used for both CSVString or CsvFilePath. If you want to convert CsvFilePath then you have to specify first path has true else false.

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

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