利用ADO读取CSV文件

 

CSV文件是一种文字档，各项资料使用”，”号分隔。例如

编号,分类,姓名,国文,数学,理化,社会,合计
78,A,王五,36,47,54,42,179
66,B,吴三,41,35,33,73,182
24,C,邓四,62,37,35,49,183
81,D,杨姐,43,46,32,64,185
58,A,杨兄,31,35,35,87,188
30,B,林董,50,34,35,70,189

当然Excel可以直接读取CSV档，这只是另一个做法而已。
有个好处可以指定读取后存放的位置。

'coded by crdotlin@2005.12.15
'Module: 一般模块-Module1
Option Explicit
Dim myDB As clsADODBopen
Sub Main()
Dim strCmn As String
Dim FN As String
     FN = "SrcData.csv"
    Set myDB = New clsADODBopen
     strCmn = "select * from " & FN
    With myDB
         .subConn ThisWorkbook.Path & "\data\"
         .subOpen strCmn
    End With
     subShow
    Set myDB = Nothing
End Sub
Sub subShow()
Dim i As Integer
Dim pt As Range
    Set pt = ActiveSheet.Range("a1")
    With myDB.theRST
        For i = 1 To .Fields.Count
             pt.Offset(0, i - 1).Value = .Fields(i - 1).Name
        Next
         pt.Offset(1, 0).CopyFromRecordset myDB.theRST
    End With
End Sub

'coded by crdotlin@2005.12.15
'Purpose: 读取CSV文件
'Method: ADO
'Requirement:Microsoft Active Data 2.x Object Library
'Module: 对象类别模块-clsADODBopen
'Emphases:   1. 每个csv档视为一个资料表
'                    2. 连结字串"DBQ="后面只接路径即可, 不需文件名
'                    3. 数据库引擎为"Driver={Microsoft Text Driver (*.txt; *.csv)};"
Option Explicit
Dim theCON As ADODB.Connection
Public theRST As ADODB.Recordset
Sub subConn(strFullName As String)
Dim strDrv As String
     strDrv = "Driver={Microsoft Text Driver (*.txt; *.csv)};" & _
     " DBQ=" & strFullName & ";"
     theCON.Open "Provider=MSDASQL; " & strDrv
End Sub
Sub subOpen(strCmn As String)
     theRST.Open Source:=strCmn, ActiveConnection:=theCON
End Sub
Private Sub Class_Initialize()
    Set theCON = New ADODB.Connection
    Set theRST = New ADODB.Recordset
End Sub
Private Sub Class_Terminate()
     theCON.Close
    Set theRST = Nothing
    Set theCON = Nothing
End Sub