I have an Excel sheet where one column has a date. I have C# code to take that column, convert it to a date and then insert it to a SQL database.

The conversion is done like this:

r.transactionDate = DateTime.Parse(Convert.ToString(xlRange.Cells[i, 1].Value));

The data transfer is working without an issue.

When the date in the Excel sheet is something like 25/05/2022 (25th of May), it is converted properly and the date I get in the SQL database is the 25/05/2022.

However, the problem is when the date is something like 12/05/2022 (12th May); it is converted to 05/12/2022 (05th of December).

How can I fix this issue?

CodePudding user response：

It's an excel sheet downloaded from Paypal that has all the dates of payments. There is a date column with dates.

Paypal does not offer a download in XLS or XLSX format. What you have is, most likely, a CSV file that Excel can open. It appears as an Excel file in your file system because Excel has registered the CSV file extension.

CSV (Comma-Separated Values) is a text file with all values represented as strings, separated by the , character. It has been around for a very long time, but was never formally specified. Or more precisely, formal specifications for CSV have been created several times, and nobody really knows which one is the right one. CSV files that work perfectly with one program can be unreadable to another.

Excel's CSV import is notorious for using US date formats regardless of your computer's regional settings. Each value is separated and examined individually. If the value looks like a date format then Excel attempts to parse it as a US date. If the first set of digits is in the range 1-12 then it is interpreted as the month. If the first set of digits is 13 or more then it tries the regional date strings, and may fall back to day-first if that fails. (This varies apparently.)

For this reason (among others) I strongly recommend that you never open a CSV file via Excel automation. Ever. In fact you should never open a CSV file with dates in it using Excel unless you know that the file was generated with month-first date formats only. Even then it is a needless waste of resources to open a text file with Excel.

You can do better.

There are plenty of libraries out there that will help you with CSV import. I've used a few (CsvHelper is a reasonable starting place), but usually I just write something simple to do the job. Read the file a line at a time (StreamReader.ReadLine), split each line into a collection of values (via my SplitCSV method), then write a ParseCSV method for the type that takes a collection of strings and returns a configured object. That way I have direct control over the way the input data is interpreted without having Excel mess things up for me.

Here's a simple example:

const string InputFile = @"C:\Temp\test.csv";

static void Main()
{
    var rows = ReadCSV(InputFile, RowData.ParseCSV);
    foreach (var row in rows)
    {
        Console.WriteLine($"#{row.RowNum}, Date: {row.SomeDate}, Amount: ${row.Amount:#,0.00}");
    }
}

class RowData
{
    public int RowNum { get; set; }
    public DateTime SomeDate { get; set; }
    public decimal Amount { get; set; }
    
    public static RowData ParseCSV(string[] values)
    {
        if (values is null || values.Length < 3)
            return null;
        
        if (!int.TryParse(values[0], out var rownum) || 
            !DateTime.TryParse(values[1], out var somedate) ||
            !decimal.TryParse(values[2], out var amount)
        )
            return null;
        
        return new RowData 
        { 
            RowNum = rownum, 
            SomeDate = somedate, 
            Amount = amount
        };
    }
}

static IEnumerable<T> ReadCSV<T>(string filename, Func<string[], T> parser, bool skipHeaders = true)
    where T : class
{
    bool first = true;
    foreach (var line in Lines(filename))
    {
        if (first)
        {
            first = false;
            if (skipHeaders)
                continue;
        }
        
        T curr = null;
        try
        {
            var values = SplitCSV(line);
            curr = parser(values);
        }
        catch
        {
            // Do something here if you care about bad data.
        }
        
        if (curr != null)
            yield return curr;
    }
}

static string[] SplitCSV(string line)
{
    var sb = new StringBuilder();
    return internal_split().ToArray();
    
    // The actual split, done as an enumerator.
    IEnumerable<string> internal_split()
    {
        bool inQuote = false;
        foreach (char c in line)
        {
            if (c == ',' && !inQuote)
            {
                // yield value
                yield return sb.ToString();
                sb.Clear();
            }
            else if (c == '"')
                inQuote = !inQuote;
            else
                sb.Append(c);
        }
        // yield last field
        yield return sb.ToString();
    }
}

static IEnumerable<string> Lines(string filename)
{
    string line;
    using var reader = File.OpenText(filename);
    while ((line = reader.ReadLine()) != null)
    {
        yield return line;
    }
}

(It's not the best way, but it's a way to do it.)