question

KumarThrainderMIND-0302 avatar image
0 Votes"
KumarThrainderMIND-0302 asked Viorel-1 edited

How can I update my existing CSV file

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.

84187-image.png

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.









dotnet-csharp
image.png (7.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.


Maybe it is perceived as an incorrect way because CSV was not designed to be updateable. However, in contrast with simple approaches, your code seems to have an advantage: it gives valid CSV even in special cases like multi-line strings, fields that contain comma, etc.

Consider other formats or design your own, updateable one. For example, consider a simple database that does not require a server.


0 Votes 0 ·
Castorix31 avatar image
2 Votes"
Castorix31 answered henrihan-8261 commented

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



· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

karenpayneoregon avatar image
1 Vote"
karenpayneoregon answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.