CSV to DataTable parsing float values as Date

Question

I'm trying to parse a CSV to DataTable in a VB.NET project, the issue is that when i'm importing the CSV file, the columns where the values are float like ;2,08;2;0,82 are imported as 30/12/1899 02:08:00 and 30/12/1899 02:00:00

How could i prevent the oledb on formatting the data?

Here is how i'm loading the csv to datatable:

        Dim con As New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & path & ";Extended Properties=""text;HDR=YES;FMT=Delimited(;)""")

        con.Open()
        Dim da As New OleDbDataAdapter(sqlquery, con)
        da.Fill(dt)

And in the same folder where the CSV is i have the schema.ini with the following content:

[anar_adm.csv]
Format=Delimited(;)

Answer 1

I maintain a library that can handle this scenario: Sylvan.Data.Csv

This is the code that I wrote to verify that it works as expected. Sorry, this is in C# as I don't know VB very well, should be straightforward to translate.

using var reader = new StringReader("Name;Value1;Value2\nTest;2,08;0,82\n");

var schema = Schema.Parse("Name,Value1:float,Value2:float");

var options = new CsvDataReaderOptions {
    Schema = new CsvSchema(schema),
    // you would likely replace this with CurrentCulture
    // the default is InvariantCulture
    Culture = CultureInfo.GetCultureInfoByIetfLanguageTag("it-IT")
};
var csv = CsvDataReader.Create(reader, options);
var dt = new DataTable();
dt.Load(csv);

The CsvDataReader comes from Sylvan.Data.Csv and the Schema.Parse line comes from Sylvan.Data, which is pre-release. You don't have to use the Schema.Parse, instead you can provide your own ICsvSchemaProvider. There is an example of a simple schema provider in this Answer.

The advantage that this has over OleDB is that it is entirely managed, no external driver needed, and doesn't require the schema.ini. It is also much, much faster, if performance is a concern (in fact, it's the fastest for .NET). The disadvantage is that you need to provide the schema yourself, while OleDB will try to automatically detect it from the data (and sometimes gets it wrong).