开发者

Change the delimiter in a csv export

Okay I have my csv builder essentially working. it has 3 c开发者_Go百科olumns. 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.


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.


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

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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜