Delete column from CSV where title = "Balanced"

Question

I have a CSV where some of my data has commas (the balance column).

I need to be able to just delete this column.. So something like this

delete column where title = "balance"

But in code.

Is there an easy way to do this or would I be better off firing it up in excel each time..

score 2 · Answer 1 · answered Nov 07 '13 at 10:16

In general you should use an available CSV parser, otherwise you are reinventing the wheel. Consider that the data could also contain the separator for instance.

However, a simple approach is loading all but this column:

string[] csvLines = File.ReadAllLines("csvFile.txt");
string header = csvLines.FirstOrDefault(l => !String.IsNullOrWhiteSpace(l));
if(header != null)
{
    int balanceIndex =Array.FindIndex<string>(header.Split(new[]{','}, StringSplitOptions.RemoveEmptyEntries)
        , col => col.Equals("balance", StringComparison.OrdinalIgnoreCase));
    if(balanceIndex >= 0)
    {
        var allButBalanceCols = csvLines
            .Select(l => new {Columns = l.Split(new[]{','}, StringSplitOptions.None) })
            .Where(x => x.Columns.Length > balanceIndex)
            .Select(x => string.Join(",", x.Columns
                .Where((col, index) => index != balanceIndex)
                .Select(col => col.Trim())));
        // rewrite the file with all columns but balance:
        File.WriteAllLines("csvFile.txt", allButBalanceCols);
    }
}

score 0 · Answer 2 · answered Nov 07 '13 at 10:04

0

Well first you'll need to read the CSV using something like this

using System;
using System.IO;

class Program
{
    static void Main()
    {
        String[] values = File.ReadAllText(@"d:\test.csv").Split(',');
    }
}

And then you'll need to remove the nth column and have to use the String.Join() method to push it back as a CSV File.

answered Nov 07 '13 at 10:04

Sandeep Bansal

6,280
17
84
126

The problem is, my data contains commas for every cell except the first (top). Is that OK? – Andy Nov 07 '13 at 10:06
All headings, such as "Time","Date","Item","Balance","URL" And then the data is underneath each heading. – Andy Nov 07 '13 at 10:15

score 0 · Answer 3 · edited May 23 '17 at 11:44

0

You have few options depending on how complex the problem is. Listed from simplest to more powerful:

Read file line by line, use String.Split() to get fields, filter them out and save back.
Use TextFieldParser.
Parsing CSV is not a new problem. Search for existing LINQ to CSV libraries, like this one.

edited May 23 '17 at 11:44

Community

1
1

answered Nov 07 '13 at 10:12

ya23

14,226
9
46
43

Delete column from CSV where title = "Balanced"

3 Answers3