How can we know the culture of a string in c#-CodePudding

Lets suppose I am reading a excel in which I have a date column. This date is having the data in mmm-yy format. and the data shown is Janv.-20, Fév.-20 and so on. Meaning this column has french culture set to it. Now I am trying to read it and save in my DB in format dd/mm/yyyy. But while reading this data the

DateTime.TryParseExact(
  string s, 
  string format, 
  IFormatProvider provider, 
  DateTimeStyles style, 
  out DateTime result);

is not recognizing this string.

Is there any way to check the culture of incoming string, here Date, so that I can set the provider based on incoming string format?

CodePudding user response：

In excel, when entering the current date ('2022-05-27') in a cell, this value will be internally stored as 44708 (see below part of sheet1.xml, which is part of the Book1.xlsx file )

<row r="2" spans="1:1" x14ac:dyDescent="0.25">
<c r="A2" s="1">
<v>44708</v>
</c>
</row>

The only way to return this as string will return "44708", and never something in the format of MMM-YY.

CodePudding user response：

You are facing an ambiguous date problem, which is essentially unsolvable without user input.

You could try parsing the date in every available format and seeing how many results you get. If you do that you'll find that (for example) "15-Fév.-20" matches only one culture, "French (Morocco)".

But (for example) "12-Janv.-21" on my PC matches 45 French cultures and also "Latvian (Latvia)".

The best you can do is get a list of cultures in which the date string could be parsed, and then decide what to do if there's more than one match.

Here's a compilable console app that demonstrates; the CulturesCompatibleWithDate() method is the one that returns all the matching dates and cultures.

static class Program
{
    static void Main()
    {
        string format = "dd-MMM-yy";

        test("15-Fév.-20",  format); // Produces one answer.
        test("12-Janv.-21", format); // Produces many answers.
    }

    static void test(string candidate, string format)
    {
        Console.WriteLine("Testing "   candidate);

        foreach (var match in CulturesCompatibleWithDate(candidate, format))
        {
            Console.WriteLine($"Parsed to {match.date} in culture {match.culture.DisplayName}");
        }

        Console.WriteLine();
    }

    public static IEnumerable<(CultureInfo culture, DateTime date)> CulturesCompatibleWithDate(string dateStr, string format)
    {
        foreach (var culture in CultureInfo.GetCultures(CultureTypes.SpecificCultures)) // Or CultureTypes.AllCultures?
        {
            if (DateTime.TryParseExact(dateStr, format, culture, DateTimeStyles.None, out var date))
            {
                yield return (culture, date);
            }
        }
    }
}

Also note that this is VERY SLOW.

My strong advice is for you to go back to the people who issued the requirement and point out the problem. Perhaps there's something more specific that you can do to remove the ambiguity. As it is, I'd say that the requirement is unworkable.