Access VBA:TransferTextを使用せずにVBAでCSVなどの区切り記号つきテキストファイルを既存のテーブルにインポートするサンプルプログラム

AccessのVBAでCSVファイルなどの区切り記号付きテキストファイルをインポートするサンプルプログラムを覚書。

［ファイルを開く］ダイアログボックスの表示と組み合わせたサンプルプログラムも作成しておいた。

TransferTextについて

AccessでCSVファイルなどの区切り記号付きテキストファイルをインポートするには、通常"TransferText"を使用する。

しかし、TransferTextでは、一括してデータを読み込むため個々のデータをインポート時に加工したりデータ型を変換するなどの細かい処理を指定できない。

インポート時の定義を作成し、定義を指定することである程度はカバーできるが、インポートするデータに何かしらの加工処理を実行した結果を保存する場合には役に立たない。

以下に、VBAの標準I/Oである"Line Input #"ステートメントを使用して区切り記号付きテキストファイルを読み込んでテーブルにレコード追加するサンプルプログラムを記述する。

"Line Input #"ステートメントを使用した基本サンプルプログラム

以下のサンプルプログラムは、"Line Input #"ステートメントを使用した基本的なサンプルプログラム。

テキストファイルを1行ずつ読み込み、読み込んだデータをイミディエイトウィンドウに表示する。

※イミディエイト ウィンドウは、[表示] - [イミディエイト ウィンドウ]で表示されます。

サンプルプログラム1

Sub Sample01()
Dim txtData As String, FNo As Long
'ファイル番号を取得
FNo = FreeFile
'ファイルを開く
Open "c:¥temp¥社員.csv" For Input As #FNo
    
    'ファイルの最後の行まで繰り返し実行
    Do While Not EOF(FNo)
        'ファイルのデータを1行読み込む(txtDataに1行分のデータが代入される)
        Line Input #FNo, txtData
        '読み込んだデータをイミディエイトウィンドウに表示
        Debug.Print txtData
    Loop
'ファイルを閉じる
Close #FNo
End Sub

csvファイル（カンマ区切り）ファイルを読み込むサンプルプログラム

以下のサンプルプログラムは、","カンマでデータを区切って変数に代入し、個々のデータをイミディエイト ウィンドウに表示するサンプルプログラム。

代入された変数は自動的に配列変数になる。

サンプルプログラム2

Sub Sample02()
Dim txtData As String, FNo As Long, arrData, i As Integer
'ファイル番号を取得
FNo = FreeFile
'ファイルを開く
Open "c:¥temp¥社員.csv" For Input As #FNo
    
    'ファイルの最後の行まで繰り返し実行
    Do While Not EOF(FNo)
        'ファイルのデータを1行読み込む(txtDataに1行分のデータが代入される)
        Line Input #FNo, txtData
        '読み込んだデータを","で区切ってバラバラにして配列arrDataに代入する
        arrData = Split(txtData, ",")
            '配列に代入された個々のデータをイミディエイトウィンドウに表示
            For i = 0 To UBound(arrData)
                Debug.Print arrData(i)
            Next i
    Loop
'ファイルを閉じる
Close #FNo
End Sub

もし、テキストファイルの区切り記号で区切られた各データが(")ダブルクォーテーションなどの引用符でくくられていて、その引用符削除したい場合は、以下のように変更する。

変更前

Debug.Print arrData(i)

変更後

Debug.Print Replace(arrData(i), """", "")

csvファイル(カンマ区切り)を読み込んでテーブルにレコードとして保存するサンプルプログラム

以下のサンプルプログラムは、CSVファイルを読み込み、1行分のデータを1レコードとしてテーブルに追加するサンプルプログラム。

サンプルプログラムを実行するデータベースとしてAccessに添付されているサンプルデータベース"Northwind.mdb" を使用している。

テーブルは、Northwind.mdbにある"運送会社"テーブルを使用。

読み込むデータは、カンマで区切られたCSVファイルで、ファイルの内容は以下のとおり。

サンプルcsvファイルの内容

"運送コード","運送会社","電話番号"
"1","黒猫","(03) 3955-11xx"
"2","ペリカン","(03) 3681-22xx"
"3","レッドハット","(03) 3566-33xx"

1行目が項目名になっており、各データは引用符(")でくくられてカンマで区切られている。

ファイル名を"運送会社.csv"とした。

サンプルプログラムを実行する際は、上記のCSVファイルを作成し、"C:\temp"フォルダ(tempフォルダがない場合は作成する)に保存し、サンプルプログラム自体は、Northwind.mdbに標準モジュールを追加して貼り付ければよい。

このCSVファイルをインポートする上で注意するべき点は、以下の3点。

1. 1行目の項目名はテーブルに追加する必要がないので無視する。
2. 追加先の"運送会社"テーブルの1列目は、オートナンバー型で定義されているためデータを代入することができない、したがってCSVファイルの1列目のデータは無視する。
3. データを囲っている引用符(")ダブルクォーテーションを省く。

上記の注意点を踏まえてサンプルプログラムを作成すると以下のようになる。

サンプルプログラム3

Sub Sample03()
Dim txtData As String, FNo As Long, arrData, i As Integer
Dim Con As New ADODB.Connection, Rec As New ADODB.Recordset
Set Con = CurrentProject.Connection
Rec.Open "運送会社", Con, adOpenDynamic, adLockOptimistic
FNo = FreeFile
Open "c:¥temp¥運送会社.csv" For Input As #FNo
    'ファイルの1行目の項目名部分を読み込む(何も処理しない)
    Line Input #FNo, txtData
    
        '実際のデータ部分(2行目)からの処理
        Do While Not EOF(FNo)
            Line Input #FNo, txtData
            arrData = Split(txtData, ",")
                Rec.AddNew
                    '引用符を削除してからフィールドに値を代入する
                    Rec("運送会社") = Replace(arrData(1), """", "")
                    Rec("電話番号") = Replace(arrData(2), """", "")
                Rec.Update
        Loop
        
    Close #FNo
    
End Sub

ポイントとしては、カンマで区切られた各データが配列として変数に代入される点。

インポート時に特定のデータに何かしらの加工処理をしたければ、そのデータが格納されている配列の要素を加工してからフィールドに代入すればよい。

トランザクション処理を使用したサンプルプログラム

サンプルプログラム3のプログラムをトランザクション処理に変更した。

トランザクション処理にすることにより、何らかのエラーがあった際にロールバック(巻き戻し)させ処理を行う前の状態に戻すことができる。

簡単に言うと実行された処理を「なかったことにする」ことができる。

データが途中まで入った状態でエラーとなって処理が止まってしまったりした場合、どこまで処理が進んでいたかなど考慮する必要がないため、余計な手間をかけないで済む。

また、以下のサンプルプログラムには、[ファイルを開く]ダイアログボックスを表示させ、ユーザーが任意のファイルを指定できるようにプログラムに追加し、より汎用性を高めた。

AccessのVBAを使用して[ファイルを開く]ダイアログボックスを表示するサンプルプログラムについての詳細は、以下の記事を参照。

Access VBA:WizHookオブジェクトを使用して[ファイルを開く]、[名前を付けて保存]ダイアログボックスを表示するサンプルプログラム

AccessのVBAで"WizHook"オブジェクトを使用して、ダイアログボックスを表示する方法について覚書。

selifelog.com

2006.12.01

Access VBA:WizHookオブジェクトを使用して[ファイルを開く]、[名前を付けて保存]を表示する際に初期フォルダを指定するサンプルプログラム

AccessのVBAでWizHookオブジェクトを使用してもしくは、ダイアログボックスを表示する際に選択される初期フォルダを任意のフォルダに指定する方法についての覚書です。

selifelog.com

2007.10.24

VBA:AccessのFileDialogを使用して[ファイルを開く]ダイアログボックスを表示するサンプルプログラム

今回はAccessの"FileDialog"オブジェクトを使用してダイアログボックスを表示させて利用するサンプルプログラムを覚書。

selifelog.com

2014.01.23

VBA:AccessのFileDialogで[ファイルを開く]ダイアログボックスを表示し、複数のファイルを選択できるようにするサンプルプログラム

AccessのFileDialogオブジェクトを使用して複数のファイルを選択できるダイアログボックスを表示して利用する方法について覚書します。

selifelog.com

2014.01.23

サンプルプログラム4

Sub Sample04()
Dim txtData As String, FNo As Long, arrData, i As Integer
Dim Con As New ADODB.Connection, Rec As New ADODB.Recordset
Dim strFilePath As String, returnValue
    '[ファイルを開く]ダイアログボックスを表示
    WizHook.Key = 51488399
    returnValue = WizHook.getFileName( _
                         0, "", "", "", strFilePath, "", _
                         "CSVファイル (*.csv)|*.csv", _
                         0, 0, 0, True _
                         )
    WizHook.Key = 0
    
    '[キャンセル]がクリックされた場合は即終了
    If returnValue <> 0 Then
        Exit Sub
    End If
Set Con = CurrentProject.Connection
Rec.Open "運送会社", Con, adOpenDynamic, adLockOptimistic
FNo = FreeFile
Open strFilePath For Input As #FNo
    'ファイルの1行目の項目名部分を読み込む(何も処理しない)
    Line Input #FNo, txtData
    
    On Error GoTo ErrHndl
    'エラーが発生した場合にデータのインポートをなかったこと(ロールバック)
    'にするためにトランザクション処理として実行
    Con.BeginTrans
        '実際のデータ部分(2行目)からの処理
        Do While Not EOF(FNo)
            Line Input #FNo, txtData
            arrData = Split(txtData, ",")
                Rec.AddNew
                    '引用符を削除してからフィールドに値を代入する
                    Rec("運送会社") = Replace(arrData(1), """", "")
                    Rec("電話番号") = Replace(arrData(2), """", "")
                Rec.Update
        Loop
        
    Con.CommitTrans
    
Close #FNo
Exit Sub
ErrHndl:
    Close #FNo
    Con.RollbackTrans
    MsgBox "以下のエラーが発生したためロールバックしました。" & vbCrLf & _
            Err.Description, vbCritical
    
End Sub

"Line Input #"ステートメントを使用したサンプルプログラムまとめ

"Line Input #"ステートメントは、データを1行ずつ読み込むため基本的に処理速度は遅いけど細かなデータ加工ができたりするので未だによく利用する。

これまで作ったプログラムの中には、数十万件のcsvファイルの内容を不正なデータがないかチェックしつつデータベースに登録していくような処理に使ったことがあって、確かに処理には時間がかかったけど、ほったらかしでいいので特に気にならなかった。

当然デイリーで動作するバッチ処理なんかだと全体の処理時間を考慮する必要があるので注意しないとならないが、特に処理時間にシビアな状況でなければ十分使えると思う。

よく使うので自分のために覚書。