CODEWITHSUNDEEP
c#excelcsvdatetimeexceldatareader
shreyas35
shreyas35

Reputation: 175

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

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

Upvotes: 2

Views: 923

Answers (2)

Vic F
Vic F

Reputation: 1459

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

Upvotes: 0

MarkPflug
MarkPflug

Reputation: 29548

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.

Upvotes: 1

Related Questions