OleDbDataAdapter reads Integer insted of Double from *.csv ...

[Joe Duchtel]

Hello -

I am using the following code to read in a *.csv file. The problem is
that if the first 25 lines of data only contain Integer, it interpret
any following rows as Integer as well.

Row 1 a,b,c
Row 2 -90,10,0
....
Row 26 -90,40,0
Row 27-90.1,10,0

Is there any way I can tell it that I was the data to be read in as
Double instead of Integer?

Dim lConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;"
& _
"Data Source=" & lFilePath & ";" & _
"Extended
Properties=""Text;HDR=Yes;FMT=Delimited\"""

Dim lConnection As New OleDb.OleDbConnection(lConnectionString)

Dim lDataAdapter As New OleDb.OleDbDataAdapter("Select * from " &
lFileName, lConnection)

lDataAdapter.Fill(lDataSet, "TextFile")

Dim lDataTable As DataTable = lDataSet.Tables(0)

For Each lRow As DataRow In lDataTable.Rows
If Not lRow.IsNull(0) Then
... CDbl(lRow.Item(0)) <<< lRow.Item(0) is internally stored
as Integer!

Thanks,
Joe

 

[Joe Duchtel]

Hello -

Thanks for the information. I also did some digging and found the
MaxScanRows. The problem is that you cannot specify this in the
connection string ... or at least it will not do anything. There is
also the IMEX=1 that could be added and that will force to use the
Import Mode. This enforces the ImportMixedTypes=Text registry
setting. The problem is that this will not work reliably.

The only way I got this to work was to change the HKEY_LOCAL_MACHINE
\SOFTWARE\Microsoft\Jet\4.0\Engines\Text\MaxScanRows registry key to
0.

Thanks!
Joe