Is it possible to update part of a CSV file without needing knowledge of the rest?

Question

In a project I'm working on, I need to read from a CSV file, update a field on each row, and then save the results back to the CSV file. I'm looking for a library that will help me with this.

My first attempt was to use ADO. This worked like a charm for reading, but when I attempted to update the file I received the error "Updating data in a linked table is not supported by this ISAM."

So now I'm looking for a replacement (or workaround). These are my requirements:

1. I would rather not define every column in the file. I only need two columns, and am concerned that additional columns may be added at a future date.

2. I need to be able to preserve (or, at the very least, replicate) the column heading information.

3. I would prefer to have as little knowledge of the underlying format/file as possible (i.e. I don't want to write a CSV writer from scratch).

I've run across a number of alternative readers, and a couple of writers... But the writers all involve reading the CSV file into a pre-defined set of fields, and then writing only those fields back to the new file. I want to minimize the amount of information about the column structure hard coded into my program.

Answer 1

The way modern file systems work you can only update any file in place if the new data is the exact same size as the original. Otherwise you must re-write the entire file from scratch. If you can meet this constraint, you can do it with low-level file streams. I don't know of a csv package that supports this off the top of my head, but the reason for this is that csv is simple enough you can do it on your own.

That said, if you are updating every row anyway then re-writing the file probably isn't that big of a deal. Writing a csv record is dead simple. Observe the following C# code:

public WriteRecord(IEnumerable items, TextWriter outputStream))
{
    string delimiter = "";
    foreach(var item in items)
    {
        outputStream.Write(delimiter);
        outputStream.Write("\"");
        outputStream.Write(item.ToString().Replace("\"", "\"\""));
        outputStream.Write("\"");
        delimiter = ",";
    }
    outputStream.Write(Environment.Newline);
}

Of course, if you have complex types that you want to be more picky about, that's fine, but since you don't want to constrain yourself to specific future column arrangements this code should be just fine. Additionally, it will complement my own CSV parser listed here on Stack Overflow, which does not require advance knowledge of the columns in the file. You could do something like this:

var tempPath = @"Some-temp-file-path.csv";
var srcPath = @"input-file-path.csv";
using (var outFile = new StreamWriter(tempPath))
{
    foreach (var items in CSV.FromFile(srcPath))
    {
        items[someInt] = "new value";
        items[otherInt] = "other value";
        WriteRecord(items, outFile);
    }

}
File.Copy(tempPath, srcPath);

Answer 2

A .csv file is a flat file, and as far as I know you cannot update a file in place unless you have something way out of my past, like an indexed file system.

Suggest reading your .csv file into your program.

Store it into a database like SQL Lite or enough heap memory to hold the length of the file and some additional space to hold changes.

Make changes.

Write file out.

I've left out lots of details as to how you would do the update. Hopefully someone with lots of file experience can correct my notion of what you should do. It may be that some Microsoft database object libraries can do this, but I am not familiar with them.

Answer 3

If you are using C#4.0 there are some nice extensions in the File class that can help you rewrite your csv file even if you couldn't lock a single row. You should take a look at File.ReadLines and File.WriteAllLines. They both take an IEnumerable as a parameter, so you could perform your conversion on a per-line basis. Even though this doesn't mean that you are not locking your file, is certainly less memory intensive than having the whole file in memory.

var lines = File.ReadLines(oldFile);
File.WriteAllLines(newFile, ChangeCSV(lines));

public IEnumerable<string> ChangeCSV(IEnumerable<string> csvLines)
{
  foreach(var line in csvLines)
  {    
    var convertedLine = ... // Do your conversion here for a single line
    yield return convertedLine;
  }
}

EDIT: if you are looking for a quick way of parsing your csv line there is this regex expression that can do the trick for you.

Answer 4

CSV files are untyped streams of characters so it is possible to replace individual characters, but you cannot add or remove characters without rewriting the entire file.

I strongly suggest, from personal experience, to instead create a simple CSV parser as @Joel suggests and recreate the entire file for each update. Trying to update data in-place can easily corrupt your entire file irrecoverably if there is an error.

Follow this procedure:

1. Read the file field-by-field.
2. If it's a field you don't care about, add it to a temp file.
3. If it's a field you do care about, write the new value to the temp file.
4. If you got an error, delete the temp file.
5. If there were no errors, exchange the temp and original files and delete the old file.