C# delete each row in a big CSV file that contains a specific value in a specific column

Question

I'm working on a Unity application and I have a csv file with 6 columns and 5000+ rows. The files might grow significantly. The second column contains either "0" or "1". All rows with a "0" are not needed.

I want to read the CSV file and write every row that has no "0" at the 2nd column into a new csv file, so that I have a new CSV file with only rows that where 1. (The "1" also doesn't need to be in the new CSV file then.)

I tried something like this, but I removed the parts that didn't work. Maybe someone has a idea. Mainly the index x and i where somehow off. Sometimes it didn't read the first column but I might just have confused myself with the incrementation.

    public void UpdateCSV()
    {
        string[] values = File.ReadAllText(datapath).Split(new char[] { ',' });
        StringBuilder ObjStringBuilder = new StringBuilder();
        
        // x as index for second column
        int x = 1;
        for (int i = 0; i < values.Length; i++)
        {

            // if column 2 is not 0 it needs to be printed in the new file
            if (values[x] != "0")
            {
                //using ObjStringBuilder.Append() to pass the values of the row that I want to keep

            }

        }

        ObjStringBuilder.ToString().Remove(ObjStringBuilder.Length - 1);
        File.WriteAllText("Assets/Datasets/UpdatedCSV.csv", ObjStringBuilder.ToString());
    }

Where in the posted code is it “adding” the lines with 1 to the `ObjStringBuilder`? That code appears commented out, however, even uncommented, it is adding nothing. — JohnG, Jan 28 '22 at 04:34
Yes, that's what I wrote in the text. I couldn't find a solution that was working so I commented it out here. The best solution was to use ReadAllLines() as Poul Bak suggested. — Denis, Jan 28 '22 at 05:09

score 1 · Accepted Answer · edited Jan 28 '22 at 05:26

1

Try: ReadAllLines - that will give you an array of lines. Then iterate through all using the Split function like you have and check values[1].

using System;
using System.Collections.Generic;
using System.IO;
using System.Text;

public void UpdateCSV()
{
    string[] lines = File.ReadAllLines(datapath);
    StringBuilder sb = new StringBuilder();
    foreach (string line in lines)
    {
        List<string> values = new(line.Split(','));
        if (values[1] != "0")
        {
            values.Remove(1);  // or values[1] = string.empty;
            string newLine = string.Join(",", values);
            sb.Append(newLine + Environment.NewLine);
        }
    }
    File.WriteAllText("Assets/Datasets/UpdatedCSV.csv", sb.ToString());
}

edited Jan 28 '22 at 05:26

Jeremy Thompson

61,933
36
195
321

answered Jan 28 '22 at 04:39

Poul Bak

10,450
5
32
57

Just a heads up, have you checked your posted answer? It has compiler errors. – JohnG Jan 28 '22 at 05:03
Oops, wrote without testing. Now it should work (have also added the necessary 'using') – Poul Bak Jan 28 '22 at 05:09
Thank you, that was exactly what I needed. – Denis Jan 28 '22 at 05:09
The error was: StringBuilder ObjStringBuilder = new StringBuilder(); – Denis Jan 28 '22 at 05:11
and values.Remove("1"); – Denis Jan 28 '22 at 05:11
Note though that this loads the entire file into memory. The duplicate link shows two alternatives that are way shorter to write and use streams to not load the entire content into memory first, instead it uses a temporary file on the drive. Still using `File.WriteLines` after filtering a list is way easier then your approach with `StringBuilder` and `WriteAllText` ;) – derHugo Jan 28 '22 at 05:37
Meaning it could e.g. simply be `File.WriteLines(datapath, File.ReadAllLines(datapath).Where(l => !l.Contains(",") || !int.TryParse(l.Split(",")[1].Trim(), out var intValue) || intValue != 0).ToList());` which would also cover more and safer checks I guess ;) – derHugo Jan 28 '22 at 05:43
@derHugo: He also wants to remove the second column. – Poul Bak Jan 28 '22 at 05:50
Oh fair enough! Still, due to `The files might grow significantly.` you most probably rather want to go for a streamed solution anyway to not have the entire file in memory ;) – derHugo Jan 28 '22 at 05:53

C# delete each row in a big CSV file that contains a specific value in a specific column

1 Answers1

Linked