vba-geek.jp は、サーバー移転に伴い https://selifelog.com にリニューアルされました。
自動的に https://selifelog.com にリダイレクトされます。
AccessのVBAでCSVファイルなどの区切り記号付きテキストファイルをインポートするサンプルプログラムを覚書。
［ファイルを開く］ダイアログボックスの表示と組み合わせたサンプルプログラムも作成しておいた。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列目は、オートナンバー型で定義されているためデータを代入することができない。
したがってCSVファイルの1列目のデータは無視する。 - データを囲っている引用符(")ダブルクォーテーションを省く。
サンプルプログラム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:ファイルを開く、名前を付けて保存ダイアログボックスを表示するサンプルプログラム」
- 「Access VBA:ファイルを開く、名前を付けて保存ダイアログボックスを表示するサンプルプログラム(その2)」
- Access VBA:AccessのFileDialogで[ファイルを開く]ダイアログボックスを表示し、複数のファイルを選択できるようにするサンプルプログラム
- Access VBA:AccessのFileDialogを使用して[ファイルを開く]ダイアログボックスを表示するサンプルプログラム
サンプルプログラム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ファイルの内容を不正なデータがないかチェックしつつデータベースに登録していくような処理に使ったことがあって、確かに処理には時間がかかったけど、ほったらかしでいいので特に気にならなかった。 当然デイリーで動作するバッチ処理なんかだと全体の処理時間を考慮する必要があるので注意しないとならないが、特に処理時間にシビアな状況でなければ十分使えると思う。 よく使うので自分のために覚書。
関連記事
