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.

Why you think it is not correct? – Damith May 17 '13 at 10:31 — Damith, May 17 '13 at 10:31

Kevin M · Answer 1 · 2016-09-28T06:08:54.633

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

Hi @RobSedgwick , you need to add the reference Microsoft.VisualBasic.dll to use TextFieldParser Class. — Kevin M, Sep 28 '16 at 06:09

stuartd · Accepted Answer · 2013-05-17T10:40:12.737

19

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

edited May 17 '13 at 10:40

answered May 17 '13 at 10:29

stuartd

70,509
14
132
163

1+ for the approach but you better add sample code, rather than adding link to a Blog post – Damith May 17 '13 at 10:37
Well the ds is created but I cannot access it/ see the data from the csv file – jayt.dev May 17 '13 at 11:28
how would I create a new table from the dataset, and how would I know the tale name – aggie Nov 15 '18 at 05:01

score 4 · Answer 3 · answered May 17 '13 at 10:34

4

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

answered May 17 '13 at 10:34

Damith

62,401
13
102
153

1

I dont understand why this is not upvoted. This is a great helpter. For example, image your CSV files which is comma delimited also had a comma in a field like ["hello", "hi", "hello, how are you"]. If you use String.Split(','), the last entry will be split into two but it should be one. CsvReader will take care of that for you in addition to other things. So, highly recommended. – pixel Jun 02 '17 at 17:27

score 1 · Answer 4 · answered Sep 29 '16 at 12:39

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

score 0 · Answer 5 · answered Oct 31 '15 at 06:44

0

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/

answered Oct 31 '15 at 06:44

thdoan

18,421
1
62
57

how can I output a Class/ entity and write a File to disk. `ExportToClass(String FileNameToWrite)` I want get the C# class/model from the headers – aggie Nov 15 '18 at 02:46

WonderWorker · Answer 6 · 2016-01-22T11:05:10.003

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

Populating a dataset from a CSV file

6 Answers6

Linked