ADOでCSVの読み込み(SQL)
VBAでADOを使用し、CSVデータを読み込みます。
ADO以外の方法については、「CSVの読み込み方法 」を参考にして下さい。
エクセルのブックと同一フォルダにある「testcsv.csv」を読み込み、
シート「csv」に貼り付ける場合と、取得したレコードをCSV出力する場合のVBAコードをけ掲示しています。
また、TSV(タブ区切り)についての情報も紹介しています。
CSVテストデータ
マクロファイルと同一フォルダに置きます。
ADOでCSV読込のVBA
Sub ReadCsv()
Dim objCn As New ADODB.Connection
Dim objRS As ADODB.Recordset
Dim i As Long
Dim strSQL As String
'CSVへのコネクション
With objCn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=Yes;FMT=Delimited"
.Open ThisWorkbook.Path & "\"
End With
'SQL文作成
strSQL = ""
strSQL = strSQL & " SELECT *"
strSQL = strSQL & " FROM"
strSQL = strSQL & " [csvtest.csv]"
strSQL = strSQL & " WHERE 列2 = 'bb'"
'SQLを実行しレコードセット取得
Set objRS = New ADODB.Recordset
Set objRS = objCn.Execute(strSQL)
With Worksheets("csv")
.UsedRange.ClearContents
'1行目に見出しの出力
For i = 0 To objRS.Fields.Count - 1
.Cells(1, i + 1) = objRS.Fields(i).Name
Next
'レコードセットを一括出力
.Range("A2").CopyFromRecordset objRS
End With
objCn.Close
Set objRS = Nothing
Set objCn = Nothing
End Sub
これが、CSVの指定になります。
最後の、FMT=Delimited、これは特に必要なく省略して構いません。
WHERE句自体を指定しなければ、全件読み込まれます。
HDR=Noの場合
F1,F2,F3,・・・
このような、「F+連番」の列名になります。
ADO使用時の注意点
このようなデータがあり得る場合は、ADOでの解決策を考えるより他の方法でCSVを読み込むことを検討してください。
カラムのデータ型が不統一の場合に正しく読み込まれない
同一列に、"ダブルクォーテーションで囲まれていない数値と文字列が混在している場合、文字列が正しく読み込まれなくなります。
,123,
,abc,
このような場合、abcが読み込まれないようです。
,"123",
,"abc",
これなら全て文字列として正しく読み込まれます。
255文字の制限
255フィールドの制限
ADOレコードセットをCSV出力
Sub ReadCsv()
Dim objCn As New ADODB.Connection
Dim objRS As ADODB.Recordset
Dim i As Long
Dim strSQL As String
'CSVへのコネクション
With objCn
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=Yes;FMT=Delimited"
.Open ThisWorkbook.Path & "\"
End With
'SQL文作成
strSQL = ""
strSQL = strSQL & " SELECT *"
strSQL = strSQL & " FROM"
strSQL = strSQL & " [csvtest.csv]"
strSQL = strSQL & " WHERE 列2 = 'bb'"
'SQLを実行しレコードセット取得
Set objRS = New ADODB.Recordset
Set objRS = objCn.Execute(strSQL)
'カラム名を配列に
Dim ary() As String
ReDim ary(objRS.Fields.Count - 1)
For i = 0 To objRS.Fields.Count - 1
ary(i) = objRS.Fields(i).Name
Next
'csv出力
Open ThisWorkbook.Path & "\csvtest2.csv" For Output As #1
Print #1, Join(ary, ",") 'カラム名
Print #1, objRS.GetString(adClipString, -1, ",", vbCrLf, "")
Close #1
objCn.Close
Set objRS = Nothing
Set objCn = Nothing
End Sub
CSV出力では、レコードセットをループさせても良いのですが、
上記ではADODB.RecordsetのGetStringを使用しています。
GetStringメソッド
この列挙には、adClipStringしか定義されていません。
adClipString
これらの3つのパラメータのGetStringメソッドは唯一で有効ですStringFormatのadClipString。
Recordsetで変換される行の数。
NumRowsが指定されていない場合、またはレコードセット内の行の総数より大きい場合、レコードセット内のすべての行が変換されます。
列間で使用される区切り文字(指定されている場合)、そうでない場合はTAB文字。
行間で使用される区切り文字(指定されている場合)、そうでない場合はCARRIAGE RETURN文字。
null値の代わりに使用される式(指定されている場合)、そうでない場合は空の文字列。
ADOでTSVの読み込み
TSVは、タブ区切り(TAB区切り)になります。
ADOでTSVを読み込む場合の情報を掲載しておきます。
参考になるサイトとして以下があります。
要点としては、
schema.ini
[TSVファイル名1]
ColNameHeader=True/False
Format=TabDelimited/CSVDelimited/Delimited(1文字)
Col1=カラム名 データ型 [長さ]
Col2=カラム名 データ型 [長さ]
・・・
[TSVファイル名2]
ColNameHeader=True/False
Format=TabDelimited/CSVDelimited/Delimited(1文字)
Col1=カラム名 データ型 [長さ]
Col2=カラム名 データ型 [長さ]
・・・
[TSVファイル名]がセクションになり、複数のファイル情報を記載できます。
指定方法の詳細は、上記サイトにそれぞれ詳しく書かれています。
このテキストファイルの文字コードは「ANSI」で作成してください。
Windows10のメモ帳の既定がUTF-8に変わっているのご注意してください。
したがって、後ろに列が増減するような場合はこの方法を使う事は難しくなるでしょう。
テキストファイルとして先頭だけ読み込んで、schema.iniを自動生成する方法も考えられますが、さすがに面倒な感じmします。
ADOでCSVの読み込みについて
データ量が多いときの速度も速く、また簡単な記述でもあり、使いようによってはとても便利な機能です。
ただし制約も多いので、固定的なフォーマットであれば良いのですが、
フォーマットが乱れる可能性があるCSVの場合には、常用するには難しい面があります。
これを使用する場合は、対象となるCSVデータを良く確認して下さい。
不定フォーマットの場合は、ADO以外の方法(CSVの読み込み方法 )を検討してください。
本サイトにあるCSV関連記事一覧
※ほとんどの記事でUTF-8に対応しています。
同じテーマ「マクロVBAサンプル集」の記事
CSVの読み込み方法
CSVの読み込み方法(改)
CSVの読み込み方法(改の改)
CSVの読み込み方法(ジャグ配列)
CSVの読み込み方法(ジャグ配列)(改)
CSVの出力(書き出し)方法
UTF-8でCSVの読み書き(ADODB.Stream)
ADOでマスタ付加と集計(SQL)
ADOでマスタ更新(SQL)
ADOでCSVの読み込み(SQL)
ADOでテキストデータを集計する
新着記事NEW ・・・新着記事一覧を見る
TRIMRANGE関数(セル範囲をトリム:端の空白セルを除外)|エクセル入門(2024-08-30)
正規表現関数(REGEXTEST,REGEXREPLACE,REGEXEXTRACT)|エクセル入門(2024-07-02)
エクセルが起動しない、Excelが立ち上がらない|エクセル雑感(2024-04-11)
ブール型(Boolean)のis変数・フラグについて|VBA技術解説(2024-04-05)
テキストの内容によって図形を削除する|VBA技術解説(2024-04-02)
ExcelマクロVBA入門目次|エクセルの神髄(2024-03-20)
VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.RangeとCellsの使い方|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.セルのクリア(Clear,ClearContents)|VBA入門
8.条件分岐(Select Case)|VBA入門
9.メッセージボックス(MsgBox関数)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門
- ホーム
- マクロVBA応用編
- マクロVBAサンプル集
- ADOでCSVの読み込み(SQL)
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。