項目

CSVファイルの読み書き

概要

CSVファイルの読み書きの例です。
ファイルとテーブルの構成を同じようにするとコード量が減ります

例

'------------------------------------------------------------
'■CSVファイル読込■
'
'CSV→DB
'CSVファイルと同じ構成のTABLEに追加するようにします。
'------------------------------------------------------------
Private Function ReadCsvFile()

    Dim rs          As New ADODB.Recordset
    Dim fnum        As Long
    Dim lineBuffer  As String
    Dim arrayItem
    Dim i           As Long
    
    fnum = FreeFile
    
    Open "c:\temp\hoge.txt" For Input As #fnum
    
    rs.Open "select * from t_hoge", CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    
    Do Until EOF(fnum)
    
        '行読み込み
        Line Input #fnum, lineBuffer
            
        '配列作成
        arrayItem = Split(lineBuffer, ",", , vbBinaryCompare)
        
        With rs
            .AddNew '追加
            
            '値設定
            For i = 0 To rs.Fields.Count - 1
                .Fields(i) = arrayItem(i)
            Next
            
            .Update '更新
        End With
    Loop
    
    'クローズ
    rs.Close
    Close #fnum
    
End Function

'------------------------------------------------------------
'■CSVファイル書込■
'
'DB→CSV
'------------------------------------------------------------
Private Function WriteCsvFile()

    Dim rs          As New ADODB.Recordset
    Dim fnum        As Long
    Dim lineBuffer  As String
    Dim arrayItem
    Dim i           As Long
    
    fnum = FreeFile
    
    Open "c:\temp\hoge.txt" For Output As #fnum
    
    rs.Open "select * from t_hoge", CurrentProject.Connection, adOpenForwardOnly, adLockOptimistic
    
    Do Until rs.EOF
        
        lineBuffer = ""
        
        '行作成
        For i = 0 To rs.Fields.Count - 1
            lineBuffer = lineBuffer & "," & rs.Fields(i)
        Next
        
        '初めの","を取る
        lineBuffer = Mid(lineBuffer, 2)
        
        '書き込み
        Print #fnum, lineBuffer
    
        rs.MoveNext
    Loop
    
    'クローズ
    rs.Close
    Close #fnum
    
End Function