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.