CODEWITHSUNDEEP
c#csv
CM99
CM99

Reputation: 313

c# Add a Column to end of CSV file

Trying to add a column to the end of a cvs file, which will just count up the number of lines (or can all be the same number it doesn't really mater) as the main aim is to add a new column with some data in it.

The sample CSV file I have has 3 columns with some random data in each of them.

What I have at the moment is

    List<string> newColumnData = new List<string>() { "D" };
    List<string> lines = File.ReadAllLines(@"C:/CSV/test.csv").ToList();

    //add new column to the header row
    lines[0] += ",Column 4";
    int index = 1;

    //add new column value for each row.
    lines.Skip(1).ToList().ForEach(line =>
    {
        //-1 for header
        lines[index] += "," + newColumnData[index - 1];
        index++;
    });
    //write the new content
    File.WriteAllLines(@"C:/CSV/test.csv", lines);

This however throws up an exception "index was out of range must be nonnegative and less than the size of the collection"

Any advice would be most welcome as always.

Upvotes: 5

Views: 13575

Answers (4)

user4843530
user4843530

Reputation:

You should not index inside the foreach. The foreach gives you a line at a time.

lines.Skip(1).ToList().ForEach(line =>
{
    //-1 for header
    line += "," + newColumnData[index - 1];
    index++;
});

The lambda expression means: take each element in the list, and call it "line", and do what is inside the curly brackets to it.

Also, as I see it here, your newColumnData only seems to have one item in it, the string "D". Yet you are indexing it as if there was one item in this list for each line in the csv file you read. That too will cause an index out of range if your csv file has more than one line in it but... never mind, the more I think about it, the more you should just go with Dmitry Bychenko's answer.

Upvotes: 7

Dmitrii Bychenko
Dmitrii Bychenko

Reputation: 186843

Why so many materializations ReadAllLines(), .ToList()? Why not just

String filePath = @"C:/CSV/test.csv";

var csv = File.ReadLines(filePath) // not AllLines
  .Select((line, index) => index == 0 
     ? line + ",Column 4"
     : line + "," + index.ToString())
  .ToList(); // we should write into the same file, that´s why we materialize

File.WriteAllLines(filePath, csv);

Upvotes: 5

Martin Brown
Martin Brown

Reputation: 25329

I suspect that newColumnData does not have enough data in it. Try this which will give you an appropriate error message when this situation occurrs. Also you are going to get an error if the file is empty. Finally the solution you have is inefficient as it has to copy the string array returned from File.ReadAllLines into a list when there is no need to.

        List<string> newColumnData = new List<string>() { "D" };
        string[] lines = File.ReadAllLines(@"C:/CSV/test.csv");

        if (lines.Length == 0)
        {
            throw new InvalidOperationException("The file is empty");
        }

        //add new column to the header row
        lines[0] += ",Column 4";

        //add new column value for each row.
        for (int i = 1; i < lines.Length; i++)
        {
            int newColumnDataIndex = i - 1;
            if (newColumnDataIndex > newColumnData.Count)
            {
                throw new InvalidOperationException("Not enough data in newColumnData");
            }

            lines[i] += "," + newColumnData[newColumnDataIndex];
        }

        //write the new content
        File.WriteAllLines(@"C:/CSV/test.csv", lines);

Upvotes: 1

John Bustos
John Bustos

Reputation: 19574

Without examining much more, I can tell you that you definitely have a challenge with the lambda you're using for the update:

    //add new column value for each row.
    lines.Skip(1).ToList().ForEach(line =>
    {
        //-1 for header
        lines[index] += "," + newColumnData[index - 1];
        index++;
    });

This would make more sense to me:

    //add new column value for each row.
    lines.Skip(1).ToList().ForEach(line =>
    {
        line += "," + newColumnData[++index - 2];
    });

The lines[index] part doesn't make sense in a ForEach since you're looping through each line separately.

Upvotes: 0

Related Questions