How to format datetime to date only with ExcelDataReader nuget while excel to csv conversion

Question

I am using ExcelDateReader nuget package for converting excel file to csv but while conversion getting datetime instead of date. i want the date in dd/MM/yyyy format or yyyy-MM-dd format. but getting it in yyyy-MM-dd hh:mm:ss t format. Is there any way i can do that or after conversion i using csvhelper to parse the csv file to a list of object there i am getting error while parsing the date value. So any of the solution will work either while converting it from .xls to csv change the date format or while parsing the csv file with csv helper change the date format.

Here is my code where i have tried to check if any column value have AM then replace the value with empty string but not the ideal way to solve the issue

private static bool SaveAsCsv(string excelFilePath, string destinationCsvFilePath, string password)
{
    System.Text.Encoding.RegisterProvider(System.Text.CodePagesEncodingProvider.Instance);
    using (var stream = new FileStream(excelFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
    {
        IExcelDataReader reader = null;
        //var conf = new ExcelReaderConfiguration { Password = password};
        if (excelFilePath.EndsWith(".xls"))
        {
            reader = ExcelReaderFactory.CreateBinaryReader(stream);
        }
        else if (excelFilePath.EndsWith(".xlsx"))
        {
            reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
        }

        if (reader == null)
            return false;

        var ds = reader.AsDataSet(new ExcelDataSetConfiguration()
        {
            ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
            {
                UseHeaderRow = false,
            }
        });

        var csvContent = string.Empty;
        int row_no = 0;
        while (row_no < ds.Tables[0].Rows.Count)
        {
            var arr = new List<string>();
            for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
            {
                if (ds.Tables[0].Rows[row_no][i].ToString().Contains("AM"))
                {
                    ds.Tables[0].Rows[row_no][i].ToString().Replace("12:00:00 AM","");
                }
                arr.Add(ds.Tables[0].Rows[row_no][i].ToString()!);
            }
            row_no++;
            csvContent += string.Join(",", arr) + "\n";
        }
        StreamWriter csv = new StreamWriter(destinationCsvFilePath, false);
        csv.Write(csvContent);
        csv.Close();
        return true;
    }
}

Thanks for the help in advance

I would have assumed that `AsDataSet()` already does type conversion for you, so if the value is properly stored as a datetime in excel it should therefore be a datetime in `AsDataSet()` output. So give this a try`if (ds.Tables[0].Rows[row_no][i] is DateTime d) arr.Add(d.ToString("d");` — Rand Random, Mar 23 '23 at 06:20
@shreyas35 you are replacing the value but not assigning it back to the cell. Try replacing the following statement ds.Tables[0].Rows[row_no][i].ToString().Replace("12:00:00 AM",""); with ds.Tables[0].Rows[row_no][i] = ds.Tables[0].Rows[row_no][i].ToString().Replace("12:00:00 AM",""); — Maahi, Mar 23 '23 at 06:49
added this code but converted csv file has wrong values if (ds.Tables[0].Rows[row_no][i] is DateTime) { arr.Add(Convert.ToDateTime(ds.Tables[0].Rows[row_no][i]).Date.ToString()); } — shreyas35, Mar 23 '23 at 07:02
well tostring would accept an argument if you actually used the code provided, as you can see from the documentation the `ToString()` of a dateTime accepts a string format argument: https://learn.microsoft.com/en-us/dotnet/api/system.datetime.tostring?view=net-7.0#system-datetime-tostring(system-string) -- you seem to not have used the declared variable `d` storing the dateTime `if (ds.Tables[0].Rows[row_no][i] is DateTime ***d***)` — Rand Random, Mar 23 '23 at 07:58
if you wonder what `.ToString("d")` does with a datetime you can look it up here: https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings#table-of-format-specifiers — Rand Random, Mar 23 '23 at 07:58
your attempt in the comments doesnt work because `.Date` of a `DateTime` object returns a `DateTime` object just with the time being at 0, so calling `.ToString()` will behave identically meaning it will print the date and time of the `DateTime` object - therefor you have to provide the `format` to the `.ToString` method to get the desired output — Rand Random, Mar 23 '23 at 08:00
you can also use `.ToShortDateString()` on your `DateTime` object, see here: https://learn.microsoft.com/en-us/dotnet/api/system.datetime.toshortdatestring?view=net-7.0 - which is identical to calling `.ToString("d")` — Rand Random, Mar 23 '23 at 08:02
You may want to look at this example: https://dotnetfiddle.net/5EkQbb — Rand Random, Mar 23 '23 at 08:10
don't have to use `"d"` as the format you can use a custom format as well eg. `dd.MM yy` - https://dotnetfiddle.net/ylruBP — Rand Random, Mar 23 '23 at 08:13

MarkPflug · Accepted Answer · 2023-03-23T16:55:22.890

If you are willing to consider an alternative to ExcelDataReader, I maintain some libraries that should make this conversion pretty easy: Sylvan.Data.Csv, Sylvan.Data.Excel, and Sylvan.Data.

Here is a complete example:

using Sylvan.Data;
using Sylvan.Data.Csv;
using Sylvan.Data.Excel;
using System.Text;

// .xls requires encodings that need this provider.
Encoding.RegisterProvider(CodePagesEncodingProvider.Instance);

// The data file contains an Id and Date column
// define a schema to represent it.
// The schema is used by the CsvDataWriter to know
// how to process the data when writing.
var s = 
    new Schema.Builder()
    .Add<int>("Id")
    // could also use DateOnly here
    .Add<DateTime>("Date")
    .Build();

var dro = new ExcelDataReaderOptions { Schema = new ExcelSchema(hasHeaders: true, s) };
// can be use to read .xls, .xlsx, or .xlsb
using var dr = ExcelDataReader.Create("data.xls", dro);

// configure the writer to write dates using the desired format.
var dwo = new CsvDataWriterOptions { DateTimeFormat = "yyyy-MM-dd" };
using var dw = CsvDataWriter.Create("data.csv", dwo);
// write the excel data to the csv file
dw.Write(dr);

This will process the data in a "streaming" manner, so if the excel file is large it won't have to load the entire file into memory like a DataTable would require. These libraries are extremely fast and memory efficient, so even if your files are large they should perform well.

score 0 · Answer 2 · answered Mar 24 '23 at 00:47

I would consider converting the value I have to a DateTime object and then to a string with my desired format, as in . . .

DateTime dt = new DateTime(2000,1,1); // You might need DateTime.Parse() for your example.
string newValue = dt.ToString("MM/dd/yyyy HH:mm:ss.fff"); // or whatever you want

see also: https://learn.microsoft.com/en-us/dotnet/standard/base-types/standard-date-and-time-format-strings

How to format datetime to date only with ExcelDataReader nuget while excel to csv conversion

2 Answers2