VBAサンプル集
ADOでCSVの読み込み(SQL)

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
公開日:2013年5月以前 最終更新日:2020-07-02

ADOでCSVの読み込み(SQL)


VBAでADOを使用し、CSVデータを読み込みます。


ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を省略します。
ADO以外の方法については、「CSVの読み込み方法 」を参考にして下さい。
・もっとも簡単かつ良くあるCSV読み込みVBAコード ・「,」「"」に対応したCSV読み込みVBAコード ・CSVをExcelブックとして開くVBA ・クエリーテーブルを使ったCSV読み込みVBAコード ・その他のCSV読み込み方法

エクセルのブックと同一フォルダにある「testcsv.csv」を読み込み、
シート「csv」に貼り付ける場合と、取得したレコードをCSV出力する場合のVBAコードをけ掲示しています。
また、TSV(タブ区切り)についての情報も紹介しています。

CSVテストデータ

ファイル名:csvtest.csv
マクロファイルと同一フォルダに置きます。

VBA マクロ ADO CSV

ADOでCSV読込のVBA

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)
  
  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
※「参照設定」で「Microsoft ActiveX Data Objects X.X Library」を追加して下さい。

.Properties("Extended Properties") = "Text;HDR=Yes;FMT=Delimited"
これが、CSVの指定になります。
最後の、FMT=Delimited、これは特に必要なく省略して構いません。

SQLの詳細については、SQL入門をご覧ください。
・DBとはSQLとは ・SQL入門の目次 ・SQLを使った既存サンプル
SQLのWHERE区は適宜変更してください。
WHERE句自体を指定しなければ、全件読み込まれます。

CSVにヘッダーが無い場合は、HDR=Noとして下さい。
HDR=Noの場合

自動的に列名が付きます。
F1,F2,F3,・・・
このような、「F+連番」の列名になります。

ADO使用時の注意点

ADOを使ってCSVを読み込む上では、以下のような制限があります。
このようなデータがあり得る場合は、ADOでの解決策を考えるより他の方法でCSVを読み込むことを検討してください。

カラムのデータ型が不統一の場合に正しく読み込まれない

各列のデータ型が自動判別されます。
同一列に、"ダブルクォーテーションで囲まれていない数値と文字列が混在している場合、文字列が正しく読み込まれなくなります。
,123,
,abc,
このような場合、abcが読み込まれないようです。
,"123",
,"abc",
これなら全て文字列として正しく読み込まれます。

255文字の制限

文字数が255を超える場合はエラーになったり255文字で切れてしまったりします。

255フィールドの制限

列数は255までに制限されます。

ADOレコードセットをCSV出力

ADOでCSVを読み込み、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メソッド
Variant = recordset.GetString(StringFormat, NumRows, ColumnDelimiter, RowDelimiter, NullExpr)

StringFormat
Recordsetを文字列に変換する方法を指定するStringFormatEnum値。
この列挙には、adClipStringしか定義されていません。
adClipString
RowDelimiterで行を区切り、ColumnDelimiterで列を区切り、NullExprでnull値を区切ります。
これらの3つのパラメータのGetStringメソッドは唯一で有効ですStringFormatのadClipString。
NumRows
オプション、省略時の既定値は-1。
Recordsetで変換される行の数。
NumRowsが指定されていない場合、またはレコードセット内の行の総数より大きい場合、レコードセット内のすべての行が変換されます。
ColumnDelimiter
オプション。
列間で使用される区切り文字(指定されている場合)、そうでない場合はTAB文字。
RowDelimiter
オプション。
行間で使用される区切り文字(指定されている場合)、そうでない場合はCARRIAGE RETURN文字。
NullExpr
オプション。
null値の代わりに使用される式(指定されている場合)、そうでない場合は空の文字列。

ADOでTSVの読み込み

CSVはカンマ区切りですが、CSVと言いつつ実際にデータを見るとTSVの場合が多々あります。
TSVは、タブ区切り(TAB区切り)になります。
ADOでTSVを読み込む場合の情報を掲載しておきます。
参考になるサイトとして以下があります。
詳細については、上記のサイトを参考にしてください。
要点としては、
schema.ini
ANSIテキストファイルで用意します。
[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の情報を定義しておきます。
[TSVファイル名]がセクションになり、複数のファイル情報を記載できます。
指定方法の詳細は、上記サイトにそれぞれ詳しく書かれています。

このテキストファイル(schema.ini)を読み込むTSVファイルと同一フォルダに置いておくだけになります。
このテキストファイルの文字コードは「ANSI」で作成してください。
Windows10のメモ帳の既定がUTF-8に変わっているのご注意してください。

schema.iniさえ用意すれば先のVBAがそのまま使用できます。

カラム定義が必須のようですので、全てのカラムが完全に決まっている固定フォーマットでしか使う事が出来ません。
したがって、後ろに列が増減するような場合はこの方法を使う事は難しくなるでしょう。
テキストファイルとして先頭だけ読み込んで、schema.iniを自動生成する方法も考えられますが、さすがに面倒な感じmします。

ADOでCSVの読み込みについて

ADOでのCSV読込は、
データ量が多いときの速度も速く、また簡単な記述でもあり、使いようによってはとても便利な機能です。
ただし制約も多いので、固定的なフォーマットであれば良いのですが、
フォーマットが乱れる可能性があるCSVの場合には、常用するには難しい面があります。
これを使用する場合は、対象となるCSVデータを良く確認して下さい。
不定フォーマットの場合は、ADO以外の方法(CSVの読み込み方法 )を検討してください。
・もっとも簡単かつ良くあるCSV読み込みVBAコード ・「,」「"」に対応したCSV読み込みVBAコード ・CSVをExcelブックとして開くVBA ・クエリーテーブルを使ったCSV読み込みVBAコード ・その他のCSV読み込み方法

本サイトにあるCSV関連記事一覧

VBAでのCSVの扱い方まとめ
・本サイトにあるCSV関連記事一覧 ・CSVの読込方法 ・CSVの読み込み方法(改) ・CSVの読み込み方法(改の改) ・CSVの読み込み方法(ジャグ配列) ・CSVの読み込み方法(ジャグ配列)(改) ・CSVの出力(書き出し)方法 ・UTF-8でCSVの読み書き(ADODB.Stream) ・ADOでCSVの読み込み(SQL)
CSVの読込方法
・もっとも簡単かつ良くあるCSV読み込みVBAコード ・「,」「"」に対応したCSV読み込みVBAコード ・CSVをExcelブックとして開くVBA ・クエリーテーブルを使ったCSV読み込みVBAコード ・その他のCSV読み込み方法
CSVの読み込み方法(改)
実施したいこと ・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。掲示板で上記のリクエストを頂きました。ということで、対応ロジックを書いてみました。
CSVの読み込み方法(改の改)
・CSVの形式について ・CSV読み込みVBAコード ・配列を使ってシートにまとめて出力する場合 ・QueryTablesを使ったCSV読み込みVBAコード ・本サイトにあるCSV関連記事一覧
CSVの読み込み方法(ジャグ配列)
・CSVの読み込み方法(改の改)での予告 ・CSV読み込みでのジャグ配列の使いどころ ・CSV読み込みVBAコード:ジャグ配列バージョン ・最後に ・本サイトにあるCSV関連記事一覧
CSVの読み込み方法(ジャグ配列)(改)
・CSV読み込みVBAコード:CSVの読み込み方法(ジャグ配列)(改) ・CSVの読み込み方法(ジャグ配列)(改)の使用例 ・本サイトにあるCSV関連記事一覧
CSVの出力(書き出し)方法
・エクセルの機能をそのまま利用します ・直接CSVを出力 ・本サイトにあるCSV関連記事一覧
UTF-8でCSVの読み書き(ADODB.Stream)
・アクティブシートの内容をUTF-8でCSV出力します ・UTF-8のCSVを読込、シートに出力します ・ADODB.Streamのメソッドとプロパティ ・本サイトにあるCSV関連記事一覧
ADOでCSVの読み込み(SQL)
・CSVテストデータ ・ADOでCSV読込のVBA ・ADO使用時の注意点 ・ADOレコードセットをCSV出力 ・ADOでTSVの読み込み ・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入門




このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。


このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
本文下部へ