How to reformat DateTime values in CSV file?

Question

I have a CSV file that contains (among other fields) (nullable) DateTime values that are formatted like "2021-11-20 14:16:52.255421" I want to get rid of the milliseconds or whatever that is.

I have tried like this, but somehow the date format remains the same:

    var csvConfiguration = new CsvConfiguration(CultureInfo.InvariantCulture);
    csvConfiguration.HasHeaderRecord = true;
    csvConfiguration.Delimiter = ";";
    csvConfiguration.TrimOptions = TrimOptions.Trim;
    
    Record[] records;

    using (var reader = new StreamReader(CsvPath, Encoding.UTF8))
    using (var csv = new CsvReader(reader, csvConfiguration))
    {
        records = csv.GetRecords<Record>().ToArray();
    }

    using (var writer = new StreamWriter($@"reformatted.csv"))
    using (var csv = new CsvWriter(writer, CultureInfo.CurrentCulture))
    {
        //I have also tried with these options, but it doesn't help either
        //var options = new TypeConverterOptions { Formats = new[] { "yyyy-MM-dd HH:mm:ss" } };
        //csv.Context.TypeConverterOptionsCache.AddOptions<DateTime>(options);
        csv.WriteRecords(records.Where(x => x.Memo != null));
    }

// "mbox_id";"email_address";"created";"updated";"created_by";"memo";
class Record
{
    [Name("mbox_id")]
    public string MailboxId { get; set; }
    
    [Name("email_address")]
    public string EmailAddress { get; set;}
    
    [Name("created")]
    public DateTime? Created { get; set; }
    
    [Name("updated")]
    public DateTime? Updated { get; set; }
    
    [Name("created_by")]
    public string CreatedBy { get; set;}
    
    [Name("memo")]
    public string Memo { get; set; }
}

What am I doing wrong? I really don't understand how the CsvWriter would even know about the input format...

Just to clarify, you tried [this answer](https://stackoverflow.com/a/39577423/3744182) to [CsvHelper changing how dates and times are output](https://stackoverflow.com/q/39564585/3744182) but it didn't work, right? Did you try any of the other answers like [this one](https://stackoverflow.com/a/69070912/3744182) which recommends `[Format("yyyy-MM-dd HH:mm:ss")]`? — dbc, Apr 12 '22 at 14:07
@dbc Yes, exactly. I don't think I can use an attribute on the same class, since it wouldn't match the input format? — TravelingFox, Apr 12 '22 at 14:22
Does this answer your question? [CsvHelper C# datetime format dd/mm/yyyy](https://stackoverflow.com/questions/66138020/csvhelper-c-sharp-datetime-format-dd-mm-yyyy) — julealgon, Apr 12 '22 at 14:35

score 2 · Accepted Answer · answered Apr 12 '22 at 15:20

2

Your problem is that, since your properties are of type DateTime?, you must explicitly register a type converter for DateTime? as well as DateTime:

csv.Context.TypeConverterOptionsCache.AddOptions<DateTime?>(options);
csv.Context.TypeConverterOptionsCache.AddOptions<DateTime>(options);

Demo fiddle #1 here.

If this situation arises often, you could create an extension method for TypeConverterOptionsCache like so:

public static class TypeConverterOptionsCacheExtensions
{
    public static void AddOptionsForTypeAndNullable(this TypeConverterOptionsCache cache, Type type, TypeConverterOptions options)
    {
        if (type == null || cache == null)
            throw new ArgumentNullException();

        if (!type.IsValueType)
        {
            cache.AddOptions(type, options);
        }
        else
        {
            var underlying = Nullable.GetUnderlyingType(type) ?? type;
            var nullable = typeof(Nullable<>).MakeGenericType(underlying);
            cache.AddOptions(underlying, options);
            cache.AddOptions(nullable, options);
        }
    }


    public static void AddOptionsForTypeAndNullable<T>(this TypeConverterOptionsCache cache, TypeConverterOptions options) where T : struct
        => cache.AddOptionsForTypeAndNullable(typeof(T), options);
}

And then do:

csv.Context.TypeConverterOptionsCache.AddOptionsForTypeAndNullable<DateTime>(options);

Demo fiddle #2 here.

answered Apr 12 '22 at 15:20

dbc

104,963
20
228
340

I see that @TravelingFox is using `CultureInfo.CurrentCulture`. What is strange, though, is I looped through all cultures and there is not a single one that outputs the milliseconds, even without setting the `DateTime?` option. – David Specht Apr 12 '22 at 16:05
@dbc Thanks. That almost worked. The milliseconds (or what it is) are gone, but DateTime values were still formatted like this: "2021-02-10 18.16.54" - so with a '.' instead of ':'. I had to change to InvariantCulture instead of CurrentCulture to fix that. weird. Thank you! – TravelingFox Apr 12 '22 at 16:10
@DavidSpecht - the question didn't provide a [mcve] so I made one myself: https://dotnetfiddle.net/ypL1Nj. Absent the `DateTime?` registration the converter registration was ignored and the dates would be output like so, with slashes instead of hyphens: `04/12/2022 15:43:12`. After the `DateTime?` registration, that was fixed and I got the desired format of `2022-04-12 12:06:07`, see https://dotnetfiddle.net/hrR3j6. But apparently OP's local culture differs from the one on https://dotnetfiddle.net/ which is why it didn't quite work for them. Switching to invariant fixes that second issue. – dbc Apr 12 '22 at 17:01
@TravelingFox - Your question lacks a [mcve] (no input or required output CSVs were shown) so I made one here https://dotnetfiddle.net/ypL1Nj. Based on that the fixed fiddle https://dotnetfiddle.net/hrR3j6 does in fact show the required output, apparently the local culture on https://dotnetfiddle.net differs from yours. – dbc Apr 12 '22 at 17:02

How to reformat DateTime values in CSV file?

1 Answers1