You know what I love? CSV files. Now stop rolling your eyes and trying to come up with Luddite jokes. I like XML and JSON too, but you know what you can do with a CSV that you can’t do with XML and JSON? Append to a file!
You can’t append to a XML or JSON file because enumerated elements must be closed…and if you’re expecting to append more elements, you can’t close the enumeration. So if you need to log something, and you need it to be quick and dirty, and you don’t want to mess with a database…well, you can’t really beat a CSV. The other thing I like about CSV files is that you can email them to a nontechnical person, and they can read it with Excel just fine.
Which brings me to the point of this post. None of the built-in standard date formatting strings in .NET will satisfy Excel’s picky needs. (Don’t believe me? Try them all. I have.) Use one of the built-in formatting strings, and Excel will just treat the field as a string. Worse still, it would take a serious Excel enthusiast to bulk convert all of those dates into an actual Excel date field: there’s just no easy way to do it. Better to get the date into the CSV file in a format that will make Excel happy to start with.
The built in format that comes the closest is “s” (sortable date/time pattern): it’s only one character off from being readable by Excel. (The problem character is the “T” that separates the date from the time.) Which brings me to the two ways to do it.
You could just use “s” and replace the pesky “T” with a space:
d.ToString("s").Replace('T',' ')
That approach has upsides and downsides. On the upside, it’s pretty easy to remember, as you don’t have to remember all the custom format strings. On the downside, it is two operations instead of one, and therefore, slower. The other way to do it, of course, is to use a custom date format string:
d.ToString("yyyy-MM-dd HH:mm:ss")
This approach means you have to remember all the custom date formatting strings (and that “MM” is day of month and “mm” is minutes and not the other way around, etc.). However, it is a bit faster, and it won’t break if Microsoft ever changes the “s” format code (as unlikely as that may be). Of course you could squirrel this away in a library or, even better, an extension method:
[sourcecode lang=”csharp”]
public static class DateTimeExtensions {
public static string ToExcelString( this DateTime d ) {
return d.ToString( d.ToString(“yyyy-MM-dd HH:mm:ss”) );
}
}
[/sourcecode]
Excel doesn’t have any concept of time zones, so if you want to capture that information, you’ll have to use a separate field in your CSV file.