VB6.0 讀取CSV檔案

最近做了一個Upload檔案的需求，檔案的格式為CSV，讀取檔案的方法整理了一下，如下：

1、先寫了一個讀取CSV檔案的Function：

1 '讀取CSV檔案
 2 '假設傳入的參數strFile=C:\Documents and Settings\Administrator\桌面\TPA_Report1 - 副本.CSV
 3 Public Function Read_CSVFile(strFile As String) As ADODB.Recordset
 4     Dim rs As ADODB.Recordset
 5     Set rs = New ADODB.Recordset
 6     Dim conn As ADODB.Connection
 7     Set conn = New ADODB.Connection
 8     Dim strFilePath As String, strFileName As String
 9     Dim i As Integer
10     i = Len(strFile)
11     Do Until Mid(strFile, i, 1) = "\" '從後向前查找倒數第一個"\"
12         i = i - 1
13     Loop
14     strFilePath = Left(strFile, i - 1) '傳回結果：C:\Documents and Settings\Administrator\桌面
15     strFileName = Mid(strFile, InStrRev(strFile, "\") + 1) '傳回結果：TPA_Report1 - 副本.CSV
16 
17     conn.Open "DRIVER={Microsoft Text Driver (*.txt; *.csv)};" & _
18                 "DBQ=" & strFilePath & ";", "", ""
19     
20     rs.Open "select * from [" & strFileName & "]", conn, adOpenStatic, _
21                 adLockReadOnly, adCmdText
22     Set Read_CSVFile = rs
23     Set rs = Nothing
24     Set conn = Nothing
25 End Function      

2、調用上面的Function：

1 Private Sub cmdUpload_Click()
 2     Dim rsData As ADODB.Recordset 'CSV中的所有的資料
 3     Dim rsUploadLog As ADODB.Recordset '上傳紀錄
 4 
 5     '取得CSV中所有的資料
 6     Set rsData = Read_CSVFile(txtFileName.Text)
 7     
 8     If rsData.RecordCount > 0 Then
 9         DoEvents
10         Dim iTotal As Integer
11         iTotal = 0
12         
13         rsData.MoveFirst
14         Do While Not rsData.EOF '循環資料集
15             'UploadLog
16             rsUploadLog.AddNew
17             rsUploadLog!Row = iTotal + 1
18             rsUploadLog!Createby = sUserID
19             rsUploadLog!CreateDate = dServerdateTime
20             rsUploadLog!Note = rsData.Fields("投保人名字") & "" '“投保人名字”為CSV檔案的表頭
21             iTotal = iTotal + 1
22             DoEvents
23             rsData.MoveNext
24         Loop
25                 
26         '一批資料裡面有的成功,有的失敗.進行儲存的時候,成功的就儲存更新，不成功的就不儲存
27         If Not BatchSaving(conn, rsUploadLog) = True Then
28             MsgBox "Upload un-successfully done", vbInformation, sApplicName
29         Else
30             MsgBox "Upload successfully done", vbInformation, sApplicName
31         End If
32     End If
33     '釋放資源，否則CSV打開會是隻讀狀态
34     rsData.Close
35     Set rsData.ActiveConnection = Nothing
36     Set rsData = Nothing
37     Exit Sub
38 End Sub      

如果您看了本篇部落格,覺得對您有所收獲，請點選右下角的

[推薦]

如果您想轉載本部落格，

請注明出處

如果您對本文有意見或者建議，歡迎留言

感謝您的閱讀，請關注我的後續部落格