Delete row in a CSV file and show in DataGridView using c#

Question

I have a problem when I want to delete a row in a CSV File, I have this code but only deletes the field that contains the line.

Example:

CSV File:

ID,Name,Lastname,Country 
1,David,tod,UK  
2,Juan,Perez,Germ
3,Pepe,Lopez,Col

---

First iteration, sending the id 1 to delete the line:

ID,Name,Lastname,Country
David,tod,UK
2,Juan,Perez,Germ
3,Pepe,Lopez,Arg

Just delete the id I want, but not the whole line

---

The expected result would be that like this:

ID,Name,Lastname,Country
2,Juan,Perez,Arg
3,Pepe,Lopez,Col

this is my code, What am I doing wrong? I have never used csv in C# :(

string searchid = "1";

string[] values = File.ReadAllText("C:\\registros.csv").Split(new char[] { ',' });
StringBuilder ObjStringBuilder = new StringBuilder();

for (int i = 0; i < values.Length; i++)
{
    if (values[i].Contains(searchid))
        continue;
    ObjStringBuilder.Append(values[i] + ",");
}
ObjStringBuilder.ToString().Remove(ObjStringBuilder.Length - 1);
File.WriteAllText("\\registros.csv", ObjStringBuilder.ToString()); 

Another question is how can I show the CSV file in a datagridview in Windows Forms. I have this logic, don't know if this is correct, but how I can show it?

public DataTable ConvertCSVtoDataTable()
{
    StreamReader sr = new StreamReader("\\registros.csv");
    string[] headers = sr.ReadLine().Split(',');
    DataTable dt = new DataTable();

    foreach (string header in headers)
    {
        dt.Columns.Add(header);
    }
    while (!sr.EndOfStream)
    {
        string[] rows = Regex.Split(sr.ReadLine(), ",(?=(?:[^\"]*\"[^\"]*\")*[^\"]*$)");

        DataRow dr = dt.NewRow();
        for (int i = 0; i < headers.Length; i++)
        {
            dr[i] = rows[i];
        }
        dt.Rows.Add(dr);
    }

    return dt;
}

Thanks!

Answer 1

You can delete row from CSV using below link

Delete rows from CSV

and

You can convert the CSV into DataTable using the below code. If your csv file uses delimiter as ,

 public DataTable ReadCSV(String FilePath, Boolean IsHeader)
    {
        string strConn = null;
        string folderpath = null;
        try
        {

            folderpath = FilePath.Substring(0, FilePath.LastIndexOf("\\") + 1);

            string FileName = Path.GetFileName(FilePath);               

            if (IsHeader == true)
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + folderpath + ";" + "Extended Properties=\"text;HDR=YES\"";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + folderpath + ";" + "Extended Properties=\"text;HDR=NO\"";
            }

            OleDbConnection Conn = new OleDbConnection();
            Conn.ConnectionString = strConn;
            Conn.Open();

            string s1 = "select * from [" + FileName + "]";
            OleDbDataAdapter da1 = new OleDbDataAdapter(s1, Conn);
            DataSet dtall = new DataSet();
            da1.Fill(dtall);
            Conn.Close();
            return dtall.Tables[0].Copy();
        }
        catch (Exception ex)
        {
            Exception excep = new Exception("CSV : " + ex.Message);
            throw excep;
        }
    }

Answer 2

The first thing that is wrong with your implementation is that you use ',' as the separator. You should either split on the new-line character '\n' or read the file line by line as follows:

var lines = new List<string>();
var file = new System.IO.StreamReader("c:\\registros.csv");
string line;
while((line = file.ReadLine()) != null)
{
   lines.Add(line);
}
file.Close();

You could then look for the line that starts with the id you are looking for. When you find it, remove the line from the list.

for(int i=0; i++; i<lines.Count)
{
    if (lines[i].StartsWith(searchid)) 
    {
        lines.RemoveAt(i);
        break;
    }
}

Next step is to write the result back to the file:

File.WriteAllLines("c:\\registros.csv", lines);

Regarding your second question, I found a similar Q/A on stackoverflow here. First step is creating the DataTable, then you'll have to bind the table to the table control that will show the data.

Answer 3

Reading and writing CSV files is not as trivial as it first seems. Cells can have embedded commas, and even new line characters. The following is one implementation of a CSV reader which can optionally be run asynchronously as a background worker. This implementation returns a standard DataTable which can easily be bound to a DataGridView:

grid.DataSource = dataTable;

The CsvReader class:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;

namespace CsvReaderExample
{
    public class CsvReader
        : BackgroundWorker
    {
        string[] m_lines;

        public DataTable DataTable { get; private set; }

        public CsvReader(string[] lines)
        {
            m_lines = lines;

            WorkerReportsProgress      = true;
            WorkerSupportsCancellation = true;
        }

        public DataTable RunWorker()
        {
            return DataTable = ParseCsvLines();
        }

        protected override void OnDoWork(DoWorkEventArgs e)
        {
            base.OnDoWork(e);

            e.Result = DataTable = ParseCsvLines();
        }

        private DataTable ParseCsvLines()
        {
            if (m_lines.Length == 0)
                return null;

            var table       = new DataTable();
            var columns     = table.Columns;
            var columnNames = GetRowValues(m_lines[0]);

            foreach (var columnName in columnNames)
            {
                var name   = columnName;
                int number = 2;

                while (columns.Contains(name))
                    name += " " + number++;

                columns.Add(name);
            }

            var rows = table.Rows;

            for (int index = 1, linesCount = m_lines.Length; index < linesCount; index++)
            {
                if (CancellationPending)
                    return null;

                var line       = m_lines[index];
                var values     = GetRowValues(line);
                int valueCount = values.Count;

                if (valueCount > columns.Count)
                {
                    int columnNumber = columns.Count;

                    while (columns.Contains(columnNumber.ToString()))
                        columnNumber++;

                    columns.Add(columnNumber.ToString());
                }

                rows.Add(values.ToArray());

                if (WorkerReportsProgress)
                    ReportProgress(100 * index / linesCount);
            }

            return table;
        }

        const char COMMA        = ',',
                   DOUBLE_QUOTE = '"',
                   VERTICAL_BAR = '|';

        private List<string> GetRowValues(string line)
        {
            var builder        = new StringBuilder();
            var values         = new List<string>();
            var inDoubleQuotes = false;
            var maxIndex       = line.Length - 1;

            for (int index = 0; index <= maxIndex; index++)
            {
                char c = line[index];

                if (c == DOUBLE_QUOTE)
                {
                    if (index == 0)
                    {
                        inDoubleQuotes = true;

                        continue;
                    }

                    if (index < maxIndex)
                    {
                        var nextIndex = index + 1;

                        if (nextIndex < maxIndex)
                        {
                            if (line[nextIndex] == DOUBLE_QUOTE)
                            {
                                index++;

                                if (inDoubleQuotes)
                                    builder.Append(DOUBLE_QUOTE);

                                continue;
                            }
                        }
                    }

                    inDoubleQuotes = !inDoubleQuotes;

                    continue;
                }

                if (c == COMMA)
                {
                    if (inDoubleQuotes)
                    {
                        builder.Append(c);

                        continue;
                    }

                    values.Add(builder.ToString());

                    builder = new StringBuilder();

                    continue;
                }

                builder.Append(c);
            }

            values.Add(builder.ToString());

            return values;
        }

        #region Sanitise cells with new line characters

        public static void SanitiseCellsWithNewLineCharacters(string fileName)
        {
            var text = File.ReadAllText(fileName, Encoding.Default);

            text     = text.Replace("\r\n", "\n");
            text     = text.Replace("\r",   "\n");

            using (var writer = File.CreateText(fileName))
            {
                var inDoubleQuotes = false;

                foreach (char c in text)
                {
                    if (c == '\n' && inDoubleQuotes)
                    {
                        writer.Write(VERTICAL_BAR);

                        continue;
                    }

                    if (c == DOUBLE_QUOTE)
                    {
                        if (inDoubleQuotes)
                            inDoubleQuotes = false;
                        else
                            inDoubleQuotes = true;
                    }

                    writer.Write(c);
                }
            }
        }

        #endregion
    }
}

You can read the DataTable synchronously as follows:

var lines     = File.ReadAllLines("C:\\registros.csv");
var csvReader = new CsvReader(lines);
var dataTable = csvReader.RunWorker();

You could then remove row(s) from the DataTable with a method such as:

private static void RemoveById(DataTable dataTable, int id)
{
    var column = dataTable.Columns["ID"];

    if (column == null)
        return;

    var rows = dataTable.Rows;

    for (int index = rows.Count - 1; index >= 0; index--)
    {
        var row   = rows[index];
        var value = row ["ID"];

        if (value == null)
            continue;

        if (value.Equals(id))
        {
            rows.RemoveAt(index);

            return;
        }
    }
}

Call it:

RemoveById(dataTable, 1);

Answer 4

SIMPLE & UNDERSTANDABLE!`

Solution For your First Problem is:

****Reading & Writing back to CSV File!****

 string searchid = "1";

        string[] values = File.ReadAllText(@"Complete Path Of File").Split(new char[] { '\n' });
        StringBuilder ObjStringBuilder = new StringBuilder();
        for (int i = 0; i < values.Length - 1; i++)
        {

            if (values[i].StartsWith(searchid) == false)
            {
                ObjStringBuilder.Append(values[i]+"\n");
            }
        }
        File.WriteAllText(@"Complete Path Of File", ObjStringBuilder.ToString());

    }

Answer to your Second Doubt:

****Populating DataGridView dynamically from CSV File!****

Comma(,) Problem SOLVED:

 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++)
                {

                    //After splitting the line with commas. The cells containing commas will also be splitted.
                    //Fact: if a cell contains special symbol in excel that cell will be saved in .csv contained in quotes E.g A+B will be saved "A+B" or A,B will be saved as "A,B"
                    //Our code splits everything where comma is found. So solution is:
                    //Logic: After splitting if a string contains even number of DoubleQuote  then its perfect cell otherwise, it is splitted in multiple cells of array.

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

                dataGrid1.ItemsSource = dtDataSource.DefaultView;
            }
        }

Add This Method Too:

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