OleDbDatareader Decimal Issue

Question

I have a tab-separated text file with some decimal values formated like that: 0,12 (comma as decimal separator). I parse its contents to an OleDBDataReader via Provider=Microsoft.Jet.OLEDB.4.0, but when I look at the result, the value that should be decimal was converted to DateTime!

How to "force" my application to understand comma as decimal separator for numbers? Didn't work changing cultureinfo to en-US or any other.

public static OleDbDataReader CriarOleDbDataReader(string sCaminhoArquivo)
    {
        FileInfo fi = new FileInfo(sCaminhoArquivo);

        string format;

        string linha;

        if (fi.Extension.ToLower().Equals(".csv"))
        {
            string texto = System.IO.File.ReadAllText(sCaminhoArquivo).Replace(',', ';');
            System.IO.File.WriteAllText(sCaminhoArquivo, texto);
            linha = System.IO.File.ReadAllLines(sCaminhoArquivo)[0];
            format = "Delimited(;)";
        }
        else
        {                
            linha = System.IO.File.ReadAllLines(sCaminhoArquivo)[0];
            format = (linha.Split('\t').Count() > 1) ? "TabDelimited" : "Delimited(;)";
        }

        DefinirSchema(fi, format);

        string cn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fi.DirectoryName + ";Extended Properties='text;HDR=Yes';";

        OleDbConnection con = new OleDbConnection(cn);
        OleDbCommand cmd = new OleDbCommand("select * from [" + fi.Name + "]", con);
        OleDbDataReader dr;

        con.Open();

        dr = cmd.ExecuteReader(CommandBehavior.CloseConnection);

        return dr;

    }

Field "IOF" is a decimal, its value in .txt file is 0,02 but it returns datetime.

dr["iof"]
{30/12/1899 00:02:00}
    Date: {30/12/1899 00:00:00}
    Day: 30
    DayOfWeek: Saturday
    DayOfYear: 364
    Hour: 0
    Kind: Unspecified
    Millisecond: 0
    Minute: 2
    Month: 12
    Second: 0
    Ticks: 599264353200000000
    TimeOfDay: {00:02:00}
    Year: 1899

Answer 1

You need one of these

1.

Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("any culture where decimal separated by comma not the point");

2.

If you're in US culture

This is pseudo-code, not exact syntax

decimal.Parse("0,02").Split(",").Join("."))

3.

Your file is tab-delimited. Load it using stream into datatable instead of using OleDb

Answer 2

"How to "force" my application to understand comma as decimal separator for numbers?"

Thread.CurrentThread.CurrentCulture = CultureInfo.CreateSpecificCulture("fr-FR");
decimal val = decimal.Parse("0,02");

The above code produces the decimal value 0.02.

Answer 3

Just solved it. I had to add some more lines in my schema.ini file, whose tries to determine de datatype for each column in the file. Got it here

These two babies did the trick:

ColNameHeader=True

MaxScanRows=0

Hope it helps.