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

ExcelマクロVBAの実用サンプル、エクセルVBA集と解説
最終更新日:2020-07-02

ADOでCSVの読み込み(SQL)


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


ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を省略します。
ADO以外の方法については、「CSVの読み込み方法 」を参考にして下さい。
エクセルのVBAでのCSVの読込方法としては。・テキストファイルとして読み込む ・ワークブックとして読み込む ・クエリーテーブルを使う ・ADOを使う ・PowerQueryを使う 大別するとこのようになります。この記事を書いた当初は、エクセルの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入門をご覧ください。
社会的にパソコンで扱うデータ量は近年急激に増えています。これに呼応してエクセルも2003までは65536行まででしたが、2007から飛躍的に増えて1048576行となっています。しかしエクセルで100万行扱えるといっても、データ量としては列数もありますので、実際には100万行はおろか数十万行でもエクセルが重くなって扱いづらくなってしまいます。
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の読み込み方法 )を検討してください。
エクセルのVBAでのCSVの読込方法としては。・テキストファイルとして読み込む ・ワークブックとして読み込む ・クエリーテーブルを使う ・ADOを使う ・PowerQueryを使う 大別するとこのようになります。この記事を書いた当初は、エクセルのVBAでCSVの読み込みについてネットで検索したところ、

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

VBAでのCSVの扱い方まとめ
マクロVBAでCSVの読み書きする方法はいくつもあり、当サイトでも複数のページでそれぞれVBAコードを掲載しています。順次記事を掲載しているので、それぞれどのような特徴があるかが良く分からなくなってしまっているようです。そこで、CSVに関するページをまとめておきました。
CSVの読込方法
エクセルのVBAでのCSVの読込方法としては。・テキストファイルとして読み込む ・ワークブックとして読み込む ・クエリーテーブルを使う ・ADOを使う ・PowerQueryを使う 大別するとこのようになります。この記事を書いた当初は、エクセルのVBAでCSVの読み込みについてネットで検索したところ、
CSVの読み込み方法(改)
実施したいこと ・ファイル名を指定し、形式をカンマ区切り、文字列で開く、その際、改行コードLF、CRLF、CRいずれにも対応、セル内の","や改行についてはカラムで区切らない。掲示板で上記のリクエストを頂きました。ということで、対応ロジックを書いてみました。
CSVの読み込み方法(改の改)
CSVのVBAでの読込方法については複数の記事を掲載しており、人気記事として多くのアクセスがあります。掲載しているVBAコードは汎用的に書いてあり、ほぼそのまま使用できるものです。しかし、CSVは多くの形式(区切り文字、文字コード等)があり、今まで掲載したコードでは解決出来ないものがあります。
CSVの読み込み方法(ジャグ配列)
CSVのマクロVBAでの読込方法についての記事は、人気記事として多くのアクセスがあります。当初作成して以来、ご要望をいただいたり自身で使っている中で、対応できないCSVが出てくるたびに改良を重ねています。今回のVBAは、一旦ジャグ配列を使用したCSV読み込み方法になります。
CSVの読み込み方法(ジャグ配列)(改)
CSVのマクロVBAでの読込方法についての記事は、人気記事として多くのアクセスがあります。順次改定していくつかのバージョンが存在します。最新のジャグ配列(配列の配列)で読み込むVBAについて、UTF-8Nの文字コード判別の課題が残っていました。
CSVの出力(書き出し)方法
シート内容をCSV出力(書き出し)する方法です。CSVの読込は、VBAでのCSVの扱い方まとめ こちらを参照して下さい。以下では、2通りの方法を紹介します。エクセルの機能をそのまま利用します ※csv出力時日付がm/d/yyyyになってしまう場合の対処 日付がm/d/yyyyになってしまう場合に、yyyy/m/dにする場合には、
UTF-8でCSVの読み書き(ADODB.Stream)
VBAでUTF-8を扱う為には、ADODB.Streamを使う必要があります。以下のコードを使用するには、参照設定で、「MicrosoftActiveXDataObjects2.8Library」にチェックを付けて下さい。または、DimadoStAsNewADODB.Stream ここを DimadoStAsObject SetadoSt=CreateO…
ADOでCSVの読み込み(SQL)
VBAでADOを使用し、CSVデータを読み込みます。ADOではSQL文が必要になりますが、ここではSQL文の詳細については説明を省略します。ADO以外の方法については、「CSVの読み込み方法」を参考にして下さい。

※ほとんどの記事でUTF-8に対応しています。



同じテーマ「マクロVBAサンプル集」の記事

CSVの読み込み方法
CSVの読み込み方法(改)
CSVの読み込み方法(改の改)
CSVの読み込み方法(ジャグ配列)
CSVの読み込み方法(ジャグ配列)(改)
CSVの出力(書き出し)方法
UTF-8でCSVの読み書き(ADODB.Stream)
ADOでマスタ付加と集計(SQL)
ADOでマスタ更新(SQL)
ADOでCSVの読み込み(SQL)
Excelファイルを開かずにシート名を取得


新着記事NEW ・・・新着記事一覧を見る

Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)
VBAで表やグラフをPowerPointへ貼り付ける|VBAサンプル集(6月19日)
アクティブシート以外の表示(Window)に関する設定|VBA技術解説(6月17日)
マクロ記録での色のマイナス数値について|エクセル雑感(6月16日)
ツイッター投稿用に文字数と特定文字で区切る|エクセル雑感(6月15日)
日付の謎:IsDateとCDate|エクセル雑感(6月14日)
IFステートメントの判定|エクセル雑感(6月13日)
インクリメンタルサーチの実装|ユーザーフォーム入門(6月12日)


アクセスランキング ・・・ ランキング一覧を見る

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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