Change the delimiter in a csv export

Question

Okay I have my csv builder essentially working. it has 3 columns. Name, Position, Date. Unfortunately right now the export has 4 columns. Last name, First name, position, date. This is because the data I get out of my data base for the name goes Last, First. Is there anyway I can easily change this delimiter? This would be very convenient haha.

Answer 1

Most CSV parsers will recognize quotes to compensate for commas in the data.

"Last, First",Admin,2010/01/01

Answer 2

The problem isn't changing the delimiter (you just change the bit in your code with a comma to be whatever character you want to use) but that it won't be as interoperable afterwards.

Your problem is that you are not correctly escapeing your fields. Do something like:

private void WriteItem<T>(StreamWriter sr, T item)
{
    string itemString = item.ToString();
    if(itemString.IndexOfAny('"', ',', '\n', '\r') != -1)//skip test and always escape for different speed/filesize optimisation
    {
        sr.Write('"');
        sr.Write(itemString.Replace("\"", "\"\""));
        sr.Write('"');
    }
    else
        sr.Write(itemString);
}
private void WriteLine<T>(StreamWriter sr, IEnumerable<T> line)
{
    bool first = true;
    foreach(T item in line)
    {
        if(!first)
            sr.Write(',');
        first = false;
        WriteItem(sr, item);
    }
}
private void WriteCSV<T>(StreamWriter sr, IEnumerable<IEnumerable<T>> allLines)
{
    bool first = true;
    foreach(IEnumerable<T> line in allLines)
    {
        if(!first)
            sr.Write('\n');
        first = false;
        WriteLine(sr, line);
    }
}

and the bit in WriteItem that quotes the item when there is a ", or newline present will deal with your "Last, First" format.

Answer 3

I've had to do this several times, and your best bet is to wrap your name. This does mean you'll have to handle it separately (sort of).

Based on what I'm reading in your question, you're pulling values from a DB that has three columns: Name (LName, FName), Position, and Date. So your SQL Statement looks something like: SELECT Name, Position, [Date] FROM Table WHERE ... And you probably have a data reader somewhere.

Based on those assumptions, I'd do this:

//SQL Connection and SQL Command have been created separately as _conn and _cmd
using(SqlDataReader _read = _cmd.ExecuteReader())
{
    string name = "";
    string position = "";
    string date = "";

    while(_read.Read()) //don't really do this, make sure you're checking for nulls and such
    {
       name = _read.GetString(0);
       position = _read.GetString(1);
       date = _read.GetString(2);

       AddLineToLines(string.Format("{0}|{1}|{2}", name, position, date));
          //AddLineToLines is a call to add to your list of lines so you can 
          // write your file.
    }
}

This will allow you to create a Pipe Delimited file (instead of CSV) and avoid having to escape commas.

If you must have csv, change that last string.Format to

string.Format("\"{0}\",{1},{2}", name, position, date)

which will escape the commas between LastName and FirstName.