VBA批量读取csv数据到excel表格

VBA在处理数据时会碰到需要处理csv文档数据的情况，这种情况与处理txt文档类似，都需要去读取文本，然后获取其中的字符串，然后将满足要求的字符串通过数组或者循坏的方式写入excel表格中，然后再进一步处理。

下述代码就是读取csv文档并将数据写入到excel工作表的示例，具体实现方法如下：

Sub Dat_anl()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
'这部分代码是定义工作表名称，并清除指定范围内容
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
    Application.ScreenUpdating = False   '禁止屏幕更新
    Datatfile = ThisWorkbook.Name

    Set shR = Workbooks(Datatfile).Sheets("Raw data")  '定义worksheet名称
    Set shM = Workbooks(Datatfile).Sheets("My data")
    Set shF = Workbooks(Datatfile).Sheets("Fly data")
    Set shD = Workbooks(Datatfile).Sheets("Delta data")
    Set shS = Workbooks(Datatfile).Sheets("Start sheet")
    Set shPivot = Workbooks(Datatfile).Sheets("Pivot")
    
    shS.Range("F6:F11").ClearContents   '清除指定range的内容
    shD.Range("A2:W160000").ClearContents
    shF.Range("A2:U160000").ClearContents
    shM.Range("A2:U160000").ClearContents
    shR.Range("A2:S160000").ClearContents
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
'这部分代码是打开csv文件，并利用split函数拆分字符串
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''     
    shS.Select
    FilePath = "C:\Users\Desktop\Measurement data\"
    ChDrive "C"
    ChDir FilePath
    Dim fso As Object, sFile As Object, blnExist As Boolean
    Dim FileName As String, LineText As Variant, i As Integer, iCol As Integer
    Const ForReading = 1
    
    Set fso = CreateObject("Scripting.FileSystemObject")    '创建FileSystemObject对象
    Openfilename = Application.GetOpenFilename("raw files (*.csv), *.csv")   '打开*.csv文件
    blnExist = fso.FileExists(Openfilename) '判断文件是否存在，如果不存在，则退出过程
    If Not blnExist Then MsgBox "Please Open .CSV File ！": Exit Sub
    Set sFile = fso.OpenTextFile(Openfilename, ForReading)  '创建并打开名为sFile的TextStream对象
    
    i = 0
    j = 0
    
    Openfile = Split(Openfilename, "\")   'Split函数拆分字符串
    lotid = Split(Openfilename, "_")
    
    shS.Cells(6, 6) = lotid(UBound(lotid) - 1)
    shS.Cells(7, 6) = Openfilename
    shS.Cells(10, 6) = Now()
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
'这部分代码是逐行读取csv文档，并寻找满足条件的字符串，先写入到二维数组中，再写入到工作表中
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
    Do While Not sFile.AtEndOfStream    '如果不是文本文件的尾端，则读取数据 
        LineText = sFile.ReadLine       '逐行读取文本
        Check_Text = Left(LineText, 13)
        
        If Check_Text = "[Measurement]" Then
            i = i + 1
            All_Text = Split(LineText, ",")
            shR.Cells(2, j + 1) = All_Text(1)    
                j = j + 1

            Dim Arr_MTF(0 To 288, 0 To 13)
                For m = 0 To 288      
                        For n = 0 To 13            
                            Arr_MTF(m, n) = All_Text(n)   '将满足条件的文本写入到二维数组
                        Next n
                        j = 0
                Next m

                shM.Cells(2 + i - 1, 1) = i
                shM.Cells(2 + i - 1, 2) = shR.Cells(2, 1)
                shM.Cells(2 + i - 1, 3) = shR.Cells(2, 2)
                shM.Cells(2 + i - 1, 4) = shR.Cells(2, 1) & "," & shR.Cells(2, 2)
                
                shF.Cells(2 + i - 1, 1) = i
                shF.Cells(2 + i - 1, 2) = shR.Cells(2, 1)
                shF.Cells(2 + i - 1, 3) = shR.Cells(2, 2)
                shF.Cells(2 + i - 1, 4) = shR.Cells(2, 1) & "," & shR.Cells(2, 2)
                
                shD.Cells(2 + i - 1, 1) = i
                shD.Cells(2 + i - 1, 2) = shR.Cells(2, 1)
                shD.Cells(2 + i - 1, 3) = shR.Cells(2, 2)
                shD.Cells(2 + i - 1, 4) = shR.Cells(2, 1) & "," & shR.Cells(2, 2)
                
                shR.Range("C2:P290") = Arr_MTF   '二维数组的内容写入到工作表
            
                shPivot.Select
                ActiveSheet.PivotTables("Pivot").PivotCache.Refresh   '刷新数据透视表
                Range("B5:R5").Copy   '复制单元格内容                  
                shM.Cells(2 + i - 1, 5).Select   '粘贴到指定单元格
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                    
                Sheets("Pivot").Range("B10:R10").Select
                Application.CutCopyMode = False
                Selection.Copy
                shF.Cells(2 + i - 1, 5).Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
                Application.CutCopyMode = False

                For k = 0 To 16
                    shD.Cells(2 + i - 1, 5 + k) = (shF.Cells(2 + i - 1, 5) - shF.Cells(2 + i - 1, 5 + k)) * 1000
                Next k

                shD.Cells(2 + i - 1, 22) = (shD.Cells(2 + i - 1, 18) + shD.Cells(2 + i - 1, 20)) / 2
                shD.Cells(2 + i - 1, 23) = (shD.Cells(2 + i - 1, 19) + shD.Cells(2 + i - 1, 21)) / 2
            Else
        End If
  
    Loop
    sFile.Close
    
    Application.ScreenUpdating = True   '打开屏幕刷新，与Application.ScreenUpdating=false成对存在   
    shS.Cells(9, 6) = i
    shS.Cells(11, 6) = Now()
    MsgBox ("   Done !")
    
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
'这部分代码是将工作簿另存在指定路径下
''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''' 
    ThisWorkbook.SaveAs FileName:=FilePath & "\" & shS.Cells(6, 6) & "-data Summary.xlsm"  
    
End Sub

上述代码要点总结：
1.读取csv文档内容及split拆分字符串（与读取txt文档内容的方法一致）
2.将多个字符串写入二维数组，进而写入到excel工作表
3.数据透视表的使用方法