ADO Imports CSV File with Wrong Data Type

[caffeine]

I have some data which looks like this (abbreviated test.csv):


"Abilene, TX",10180,1985,4,-,-
"Abilene, TX",10180,1986,1,-,-
"Abilene, TX",10180,1986,2,-,-
"Abilene, TX",10180,1986,3,107.61,( 2.89)
"Abilene, TX",10180,1986,4,95.57,( 3.02)
"Abilene, TX",10180,1987,1,99.65,( 2.91)

and I wrote a nifty VBA program that uses ADO to import the file into the worksheet. The problem is that the wrong datatype is being detected in the 5th column.

107.61 is being imported as 107
95.57 is being importated as 95
etc.

AFAICT, ADO "peeks" at the first few rows when determining which datatype to choose, because if you delete one of the first few lines of the csv file (one of the lines with missing data) then everything is hunky dory. 107.61 imports as 107.61, 95.57 imports as 95.57, etc.

Through trial and error, I narrowed it down to one row -- you only need to delete one of the first rows of the csv file for the 5th column to be imported as a Double instead of an Integer.

I Googled, and the answer appears to be to write a schema file. For various reasons, I don't want to do this. I want to force the 5th column to be a Double programatically.

I've included a really convenient spreadsheet and datafile. Can some kind soul please tell me how I can force the 5th column to be imported as a Double?

I know there are other ways of reading a text file, and perhaps I should've pursued them since I'm on "company time". However, at this point, it's personal. This is something I want to know, not necessarily accomplish (until my boss starts yelling at me to finish up already).

Thank you!!!
Pete

[anhn]

I understand the problem you have when dealing with database on an Excel or CSV column that cotains mixed datatype.
You have chosen a hard way to do the job and still not happy with it.
This is a simple way for me as a VBA "beginner":

Code only required in Sheet1 module:

Option Explicit

Private Sub btnImportData_Click()
    Dim vFileName As Variant
    Dim wb As Workbook

    vFileName = Application.GetOpenFilename("CSV Files (*.csv),*.csv,", 1, "Choose File to Import", , False)
    If vFileName = False Then Exit Sub
    
    Application.ScreenUpdating = False
    Me.UsedRange.Clear
    Set wb = Workbooks.Open(filename:=vFileName)
    With wb.Sheets(1).UsedRange.Resize(, 5)
        Me.[A1].Resize(.Rows.Count, 5) = .Value
    End With
    wb.Close False
    Set wb = Nothing
    Me.[A:D].EntireColumn.AutoFit
    Application.ScreenUpdating = True
End Sub

Private Sub btnClearSheet_Click()
    Me.UsedRange.Clear
End Sub