How can I update my existing CSV file

Question

Hi,

I am trying to update a CSV file.

Currently, I am using an open-source library named CSVHelper to Read and Write data in the CSV file but there is no feasible option available to update an existing cell in the CSV file.

As in the given image I trying to update the selected cell to a new value.

Somthing like this:

public UpdateCSV_KEY_VAL(string NewValue, DataTable ThirdRowDT)  
{  
    using (var streamWriter = new StreamWriter(filePath, append: true))  
    {  
        using (var csvStreamWriter = new CsvWriter(streamWriter, CultureInfo.InvariantCulture))  
        {  
            //3rd row selected on the basis of Passed DataTable  
            //Update KEY_VAL column's 3rd row with the passed value  
      
            if(ThirdRowDT combination not exists in CSV)  
            {  
                foreach (DataRow row in ThirdRowDT.Rows)  
                {  
                    for (var i = 0; i < ThirdRowDT.Columns.Count; i++)  
                    {  
                        csvStreamWriter.WriteField(row[i]);  
                    }  
                    csvStreamWriter.NextRecord();  
             }  
         }  
    }  
}  
      
UpdateCSV_KEY_VAL("1", DT);  
  

Currently, I have only one solution for this is to read all the data and using LINQ, modify the data as required in the code, and rewrite in CSV. But this is not a correct way to do this (Memory and CPU usage will be affected).

Please help with this.

Answer 1

A way with DataTable =>

// Add  at beginning : using System.Data.OleDb;  
  
string sFileName = @"E:\test_csv.csv";  
string sConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.IO.Path.GetDirectoryName(sFileName) + ";Extended Properties=\"Text;HDR=Yes;FMT=Delimited\"";  
OleDbConnection oleConn = new OleDbConnection(sConnectionString);  
string sSQLQuery = "Select * From " + System.IO.Path.GetFileName(sFileName);  
OleDbCommand oleCommand = new OleDbCommand(sSQLQuery, oleConn);  
OleDbDataAdapter oleAdapt = new OleDbDataAdapter(oleCommand);  
DataTable dt = new DataTable();  
oleAdapt.Fill(dt);  
  
// Update cell  
dt.Rows[2][4] = "2";  
  
System.Text.StringBuilder sb = new System.Text.StringBuilder();  
foreach (DataColumn dc in dt.Columns)  
{  
    sb.Append(dc.ColumnName);  
    sb.Append(",");  
}  
sb.Remove(sb.Length - 1, 1);  
sb.AppendLine();  
foreach (DataRow r in dt.Rows)  
{  
    for (int i = 0; i < dt.Columns.Count; i++)  
    {  
        sb.Append(r.ItemArray[i].ToString());  
        sb.Append(",");  
    }  
    sb.Remove(sb.Length - 1, 1);  
    sb.AppendLine();  
}  
System.IO.File.WriteAllText(sFileName, sb.ToString());  

Tested with this .CSV :

WH_CODE,LINE_CODE,STATION_CD,KEY_NAME,KEY_VAL  
HJ,1,1,check1,1  
HJ,1,1,check2,1  
HJ,2,1,check3,0  
HJ,1,1,check4,1  

Answer 2

Consider simply overwriting the entire file using the code below which has been tested with a DataTable, list of T, Entity Framework and Entity Framework Core. The code actually does not carry how data is loaded as the code work against the DataGridView.

One might say jess the code is complex but if it works than that does not matter. Bonus points, place the code below into a class project so the code can be used in other project, think of it like adding a reference to your project the conventional way.

I have a pre-done class project.

Export data only

File.WriteAllLines("Exported.csv", dataGridView1.ToDelimited().ToList());  

Export data and include column headers

File.WriteAllLines("Exported.csv", dataGridView1.ToDelimitedWithHeaders().ToList());  

Source code

Make sure to change the namespace to your namespace

using System;  
using System.Linq;  
using System.Windows.Forms;  
  
namespace CodeSamples  
{  
    public static class DataGridViewExtensionMethods  
    {  
  
        /// <summary>  
        /// Convert column Header text to a delimited string  
        /// </summary>  
        /// <param name="sender"></param>  
        /// <returns></returns>  
        public static string DelimitedHeaders(this DataGridView sender) =>  
            string.Join(",", sender.Columns.OfType<DataGridViewColumn>()  
                .Select(column => column.HeaderText).ToArray());  
          
        /// <summary>  
        /// Create a string array of data in the DataGridView, does not include header  
        /// </summary>  
        /// <param name="sender"></param>  
        /// <param name="delimiter">Delimiter which defaults to a comma</param>  
        /// <returns>array comprised of rows/cell data without header</returns>  
        public static string[] ToDelimited(this DataGridView sender, string delimiter = ",") =>  
            (sender.Rows.Cast<DataGridViewRow>()  
                .Where(row => !row.IsNewRow)  
                .Select(row => new {row, rowItem = string.Join(delimiter,   
                    Array.ConvertAll(row.Cells.Cast<DataGridViewCell>().ToArray(), c =>   
                        ((c.Value == null) ? "" : c.Value.ToString())))})  
                .Select(@t => @t.rowItem)).ToArray();  
  
        /// <summary>  
        ///  Create a string array of data in the DataGridView, includes header  
        /// </summary>  
        /// <param name="sender"></param>  
        /// <param name="delimiter">Delimiter which defaults to a comma</param>  
        /// <returns>array comprised of rows/cell data with header</returns>  
        public static string[] ToDelimitedWithHeaders(this DataGridView sender, string delimiter = ",")  
        {  
            var headers = sender.DelimitedHeaders();  
            var data = sender.ToDelimited().ToList();  
            data.Insert(0, headers);  
            return data.ToArray();  
  
        }  
    }  
}