How to reformat DateTime values in CSV file?-CodePudding

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...

CodePudding user response：

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.