Populating a dataset from a CSV file

Question

I would like to read the contents of a CSV file and create a dataset. I am trying like this:

var lines = File.ReadAllLines("test.csv").Select(a => a.Split(';'));
DataSet ds = new DataSet();
ds.load(lines);

but apparently this is not correct.

Answer 1

Comma (,) Problem Solved in This Code

Works Even If you add Commas(,) in between a cell

Reading CSV file CODE:

public MainWindow()
                {
                    InitializeComponent();

                    DataTable dtDataSource = new DataTable();

                    string[] fileContent = File.ReadAllLines(@"..\\Book1.csv");

                    if (fileContent.Count() > 0)
                    {
                        //Create data table columns dynamically
                        string[] columns = fileContent[0].Split(',');

                        for (int i = 0; i < columns.Count(); i++)
                        {
                            dtDataSource.Columns.Add(columns[i]);
                        }

                        //Add row data dynamically
                        for (int i = 1; i < fileContent.Count(); i++)
                        {
                            string[] rowData = fileContent[i].Split(',');
                            string[] realRowData = new string[columns.Count()];
                            StringBuilder collaboration = new StringBuilder();
                            int v = 0;

                            //this region solves the problem of a cell containing ",".
                            #region CommaSepProblem
                            for (int j = 0, K = 0; j < rowData.Count(); j++, K++)
                            {
                                if ((rowData[j].Count(x => x == '"') % 2 == 0))//checks if the string contains even number of DoubleQuotes
                                {
                                    realRowData[K] = quotesLogic((rowData[j]));

                                }
                                else if ((rowData[j].Count(x => x == '"') % 2 != 0))//If Number of DoubleQuotes  are ODD
                                {
                                    int c = rowData[j].Count(x => x == '"');
                                    v = j;

                                    while (c % 2 != 0)//Go through all the next array cell till it makes EVEN Number of DoubleQuotes.
                                    {
                                        collaboration.Append(rowData[j] + ",");
                                        j++;
                                        c += rowData[j].Count(x => x == '"');

                                    }

                                    collaboration.Append(rowData[j]);
                                    realRowData[K] = quotesLogic(collaboration.ToString());
                                }
                                else { continue; }
                            }
                            #endregion
                            dtDataSource.Rows.Add(realRowData);
                        }
                        if (dtDataSource != null)
                        {
                            //dataGridView1 = new DataGridView();
                            dataGrid1.ItemsSource = dtDataSource.DefaultView;
                        }
                    }
                }

Method Need to be added:

 string quotesLogic(string collaboration)
    {
        StringBuilder after = new StringBuilder(collaboration);

        if (after.ToString().StartsWith("\"") && after.ToString().EndsWith("\""))//removes 1st and last quotes as those are system generated
        {
            after.Remove(0, 1);
            after.Remove(after.Length - 1, 1);
            int count = after.Length - 1;
            //FACT: if you try to add DoubleQuote in a cell in excel. It'll save that quote as 2 times DoubleQuote(Like "")  which means first DoubleQuote is to give instruction to CPU that the next DoubleQuote  is not system generated.
            while (count > 0)//This loop find twice insertion of 2 DoubleQuotes and neutralise them to One DoubleQuote. 
            {
                if (after[count] == '"' && after[count - 1] == '"')
                {
                    after.Remove(count, 1);
                }
                count--;
            }
        }

        return after.ToString();
    }

Answer 2

You need to add the reference Microsoft.VisualBasic.dll to use TextFieldParser Class.

 private static DataTable GetDataTabletFromCSVFile(string csv_file_path)
        {
            DataTable csvData = new DataTable();
            try
            {
              using(TextFieldParser csvReader = new TextFieldParser(csv_file_path))
                 {
                    csvReader.SetDelimiters(new string[] { "," });
                    csvReader.HasFieldsEnclosedInQuotes = true;
                    string[] colFields = csvReader.ReadFields();
                    foreach (string column in colFields)
                    {
                        DataColumn datecolumn = new DataColumn(column);
                        datecolumn.AllowDBNull = true;
                        csvData.Columns.Add(datecolumn);
                    }
                    while (!csvReader.EndOfData)
                    {
                        string[] fieldData = csvReader.ReadFields();
                        //Making empty value as null
                        for (int i = 0; i < fieldData.Length; i++)
                        {
                            if (fieldData[i] == "")
                            {
                                fieldData[i] = null;
                            }
                        }
                        csvData.Rows.Add(fieldData);
                    }
                }
            }
            catch (Exception ex)
            {
            }
            return csvData;
        }
      }

See this article for more info : http://www.morgantechspace.com/2013/08/how-to-read-data-from-csv-file-in-c.html

Answer 3

I have written five methods below that will turn a Csv file into a DataTable.

They have been designed to take into account optional quote marks (e.g. " symbols) and to be as versatile as possible without using other libraries:

    public static DataTable GetDataTabletFromCSVFile(string filePath, bool isHeadings)
    {
        DataTable MethodResult = null;
        try
        {
            using (TextFieldParser TextFieldParser = new TextFieldParser(filePath))
            {
                if (isHeadings)
                {
                    MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);

                }
                else
                {
                    MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);

                }

            }

        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }

    public static DataTable GetDataTableFromCsvString(string csvBody, bool isHeadings)
    {
        DataTable MethodResult = null;
        try
        {
            MemoryStream MemoryStream = new MemoryStream();


            StreamWriter StreamWriter = new StreamWriter(MemoryStream);

            StreamWriter.Write(csvBody);

            StreamWriter.Flush();


            MemoryStream.Position = 0;


            using (TextFieldParser TextFieldParser = new TextFieldParser(MemoryStream))
            {
                if (isHeadings)
                {
                    MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);

                }
                else
                {
                    MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);

                }

            }

        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }

    public static DataTable GetDataTableFromRemoteCsv(string url, bool isHeadings)
    {
        DataTable MethodResult = null;
        try
        {
            HttpWebRequest httpWebRequest = (HttpWebRequest)WebRequest.Create(url);
            HttpWebResponse httpWebResponse = (HttpWebResponse)httpWebRequest.GetResponse();

            StreamReader StreamReader = new StreamReader(httpWebResponse.GetResponseStream());

            using (TextFieldParser TextFieldParser = new TextFieldParser(StreamReader))
            {
                if (isHeadings)
                {
                    MethodResult = GetDataTableFromTextFieldParser(TextFieldParser);

                }
                else
                {
                    MethodResult = GetDataTableFromTextFieldParserNoHeadings(TextFieldParser);

                }

            }

        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }


    private static DataTable GetDataTableFromTextFieldParser(TextFieldParser textFieldParser)
    {
        DataTable MethodResult = null;
        try
        {
            textFieldParser.SetDelimiters(new string[] { "," });

            textFieldParser.HasFieldsEnclosedInQuotes = true;


            string[] ColumnFields = textFieldParser.ReadFields();

            DataTable dt = new DataTable();

            foreach (string ColumnField in ColumnFields)
            {
                DataColumn DataColumn = new DataColumn(ColumnField);

                DataColumn.AllowDBNull = true;

                dt.Columns.Add(DataColumn);

            }


            while (!textFieldParser.EndOfData)
            {
                string[] Fields = textFieldParser.ReadFields();


                for (int i = 0; i < Fields.Length; i++)
                {
                    if (Fields[i] == "")
                    {
                        Fields[i] = null;

                    }

                }

                dt.Rows.Add(Fields);

            }

            MethodResult = dt;

        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }

    private static DataTable GetDataTableFromTextFieldParserNoHeadings(TextFieldParser textFieldParser)
    {
        DataTable MethodResult = null;
        try
        {
            textFieldParser.SetDelimiters(new string[] { "," });

            textFieldParser.HasFieldsEnclosedInQuotes = true;

            bool FirstPass = true;

            DataTable dt = new DataTable();

            while (!textFieldParser.EndOfData)
            {
                string[] Fields = textFieldParser.ReadFields();

                if(FirstPass)
                {
                    for (int i = 0; i < Fields.Length; i++)
                    {
                        DataColumn DataColumn = new DataColumn("Column " + i);

                        DataColumn.AllowDBNull = true;

                        dt.Columns.Add(DataColumn);

                    }

                    FirstPass = false;

                }

                for (int i = 0; i < Fields.Length; i++)
                {
                    if (Fields[i] == "")
                    {
                        Fields[i] = null;

                    }

                }

                dt.Rows.Add(Fields);

            }

            MethodResult = dt;

        }
        catch (Exception ex)
        {
            ex.HandleException();
        }
        return MethodResult;
    }

If, like me, you're saving from reporting services then you should use it like this:

    Warning[] warnings;
    string[] streamids;
    string mimeType;
    string encoding;
    string filenameExtension;

    byte[] bytes = rvMain.ServerReport.Render("csv", null, out mimeType, out encoding, out filenameExtension, out streamids, out warnings);

    string CsvBody = System.Text.Encoding.UTF8.GetString(bytes);

    DataTable dt = GetDataTableFromCsvString(CsvBody,true);

Otherwise, all you need do is:

    bool IsHeadings = true; //Does the data include a heading row?

    DataTable dt = GetDataTableFromCsvString(CsvBody, IsHeadings);

Or to use directly from a csv file

    bool IsHeadings = true; //Does the data include a heading row?

    DataTable dt = GetDataTabletFromCsvFile(FilePath, IsHeadings)

Or to use a csv file that is stored remotely

    bool IsHeadings = true; //Does the data include a heading row?

    DataTable dt = GetDataTabletFromRemoteCsv(Url, IsHeadings)

A Dataset is a collection of DataTables, so create one like so:

    DataSet ds = new DataSet();

    ds.Tables.Add(dt);

Answer 4

If you just want to quickly create a DataTable filled with sample data from a CSV file (or pasted directly from Excel) to play around or prototype, then you can use my fork of Shan Carter's Mr. Data Converter -- I recently added the ability to output comma- and tab-delimited data to a C# DataTable.

http://thdoan.github.io/mr-data-converter/

Answer 5

You need to run a SELECT statement against the CSV file to fill the dataset:

Edit: here's some sample code from http://carllbrown.blogspot.co.uk/2007/09/populate-dataset-from-csv-delimited_18.html

string FileName = ...
OleDbConnection conn = new OleDbConnection
       ("Provider=Microsoft.Jet.OleDb.4.0; Data Source = " + 
         Path.GetDirectoryName(FileName) + 
         "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\"");

conn.Open();

OleDbDataAdapter adapter = new OleDbDataAdapter
       ("SELECT * FROM " + Path.GetFileName(FileName), conn);

DataSet ds = new DataSet("Temp");
adapter.Fill(ds);

conn.Close();

Answer 6

You can use Library like Fast CSV Reader then

using System.IO;
using LumenWorks.Framework.IO.Csv;
void ReadCsv()
{
    // open the file "data.csv" which is a CSV file with headers
    using (CsvReader csv = new CsvReader(
                           new StreamReader("data.csv"), true))
    {
        myDataRepeater.DataSource = csv;
        myDataRepeater.DataBind();
    }
}