この記事では、SQLのSelect文を使ってCSVファイル(ヘッダあり)から対象データを抽出しExcelのシートに貼り付ける方法についてご説明します。
今回はヘッダがあるCSVファイルについてご説明します。
ヘッダがないCSVファイルを扱う場合は次の記事をご参照ください。
【ExcelVBA】SQLのSelect文を使ってCSVファイルから対象データを抽出しExcelのシートに貼り付ける方法(※CSVのデータにヘッダなし)
【動画】SQLのSelect文を使ってCSVファイル(ヘッダあり)から対象データを抽出しExcelのシートに貼り付ける実際の動き
本題に入る前に、まずは次の動画をご覧ください。
マクロがADOを使用してCSVに接続し、SQLのSelect文を実行して対象データを検索・抽出しExcelのシートに貼り付けています。
抽出条件には、CSVファイルのヘッダの文字列(CSVファイルの1行目)を指定しています。
マクロ作成の流れ
Select文が実行されると、Excelのシートにデータが貼り付けられます。
CSVファイルの例
今回使うテストデータのCSVファイルは次の通りです。
1行目にヘッダ(「月」「名前」と科目名)があり、2行目以降は生徒名と月、点数が入力されています。
Excelファイルの例
今回は次のExcelファイルを用意しました。
マクロを実行すると、CSVファイルにある各生徒の点数がF列からJ列に出力されます。(D列には月、E列には生徒の名前が出力)
ちなみに下の画像は、国語で点数が75のデータを条件に指定してマクロを実行した結果です。
配置されているセルの名前は次の通りです。
csvFilePath
CSVファイルが置いてある場所を入力します。
searchFld
検索条件を指定する場合に、このセルに検索対象の項目名(フィールド名)を入力します。
searchVal
検索条件を指定する場合に、このセルに検索対象の項目名(フィールド名)に対する検索値を入力します。
コードの例
Excelのマクロのコード(例)
Option Explicit
Private Sub btn_getDirFileList_Click()
Dim adoCON As New ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
Dim conStr As String 'データソース接続情報用変数
Dim sqlStr As String 'SQL文用変数
Dim csvFilePath As String 'CSVファイルの在り処
Const csvFileNM As String = "data.csv" 'CSVファイル
'CSVファイルの在り処を取得する
csvFilePath = Worksheets("top").Range("csvFilePath").Value
'データソース接続情報を取得する
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & csvFilePath & ";" _
& "Extended Properties=""Text;HDR=YES;FMT=Delimited"""
'コネクションを開く
adoCON.Open conStr
'データを取得するSQL文を作成する
sqlStr = "select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from "
sqlStr = sqlStr & csvFileNM
sqlStr = sqlStr & " where "
Select Case Worksheets("top").Range("searchFld").Value
Case "月", "名前"
'月か名前の場合(文字列データ)
sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value & _
" = '" & _
Worksheets("top").Range("searchVal").Value & "'"
Case Else
'月か名前以外場合(数値データ)
sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value & _
" = " & _
Worksheets("top").Range("searchVal").Value
End Select
'SQL文を実行する
Set rs = adoCON.Execute(sqlStr)
'データをシート「top」に貼り付ける
Sheets("top").Range("D2").CopyFromRecordset rs
'後処理
adoCON.Close
Set rs = Nothing
Set adoCON = Nothing
End Sub
コードの解説
注目すべきコード①
最初に見て頂きたいのは14行目から22行目です。
'CSVファイルの在り処を取得する
csvFilePath = Worksheets("top").Range("csvFilePath").Value
'データソース接続情報を取得する
conStr = "Provider=Microsoft.ACE.OLEDB.12.0;" _
& "Data Source=" & csvFilePath & ";" _
& "Extended Properties=""Text;HDR=YES;FMT=Delimited"""
'コネクションを開く
adoCON.Open conStr
以上のコードは、CSVファイルの在り処を元に、マクロがデータソース接続情報を取得して接続するコードです。
以上のコードを実行することで、CSVファイルに接続する準備が整いました。
ポイントは、CSVファイル名を以上のコードで明記するのではなく、CSVファイルの置き場を指定(Data Sourceに指定)しているところです。
CSVファイルを参照するコードはSelect文のFROM句で説明しています。
注目すべきコード②
次に見て頂きたいのは25行目から49行目です。
'データを取得するSQL文を作成する
sqlStr = "select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from "
sqlStr = sqlStr & csvFileNM
sqlStr = sqlStr & " where "
Select Case Worksheets("top").Range("searchFld").Value
Case "月", "名前"
'月か名前の場合(文字列データ)
sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value & _
" = '" & _
Worksheets("top").Range("searchVal").Value & "'"
Case Else
'月か名前以外場合(数値データ)
sqlStr = sqlStr & Worksheets("top").Range("searchFld").Value & _
" = " & _
Worksheets("top").Range("searchVal").Value
End Select
以上のコードは、Select文を生成するコードです。
フィールド名
26行目ではフィールド名を指定しています。
今回は列全てのデータが欲しいので「*」を指定しています。
列ごとのデータが欲しい場合は列名を指定します。
列名というと分かりにくいと思いますが、CSVファイルの1行目にある項目名のことです。
もし「名前」と「数学」の列のデータが欲しい場合は26行目を次のコードの通りにします。
sqlStr = sqlStr & " 名前, 数学"
FROM句
28行目ではCSVファイルの名前を指定しています。
データベースの場合、SQLのSelect文のFROM句にはテーブル名を指定しますが、CSVファイルの場合はCSVファイル名をFROM句に指定します。
このCSVファイル名は、フルパスのファイル名ではなくファイル名自体を指定します。
例えばCSVファイル「data.csv」が「C:¥work¥test」の配下にある場合、「C:¥work¥test¥data.csv」と指定するのではなく、「data.csv」のようにファイル名を指定します。
CSVファイルのパスは14行目と18行目で指定しています。
'CSVファイルの在り処を取得する
csvFilePath = Worksheets("top").Range("csvFilePath").Value
& "Data Source=" & csvFilePath & ";" _
WHERE句
29行目以降はWHERE句を組み立てるコードですが、文字列データの場合は「’」で値を囲まないといけないので、文字列データと数値データそれぞれにコードを用意する必要があります。
文字列データの場合は37行目から39行目、数値データの場合は45行目から47行目のコードが呼ばれます。
実際のSQL文(例)
コードだけではSelect文が分かりづらいと思うので、Select文の例を以下にお見せします。
検索対象の項目が文字列データの場合
名前が「生徒2」のデータを抽出する場合は次のSelect文になります。
select
*
from
data.csv
where
名前 = '生徒2'
以上は条件を満たしたデータの、すべての項目のデータを取得する場合のSelect文です。
もし、条件を満たしたデータの、「名前」と「数学」のデータだけを取得したい場合は次の通りのSelect文になります。
select
名前,
数学
from
data.csv
where
名前 = '生徒2'
検索対象の項目が数値データの場合
「英語」の点数が「93」のデータを抽出する場合は次のSelect文になります。
select
*
from
data.csv
where
英語 = 93
以上は条件を満たしたデータの、すべての項目のデータを取得する場合のSelect文です。
もし、条件を満たしたデータの、「名前」と「数学」のデータだけを取得したい場合は次の通りのSelect文になります。
select
名前,
数学
from
data.csv
where
英語 = 93
注目すべきコード③
次に見て頂きたいのは52行目から55行目です。
'SQL文を実行する
Set rs = adoCON.Execute(sqlStr)
'データをシート「top」に貼り付ける
Sheets("top").Range("D2").CopyFromRecordset rs
以上はSelect文を実行し、CSVファイルから検索・抽出したデータをExcelのシートに貼り付けるコードです。
52行目では、Executeの引数にSelect文を指定してExecuteを呼び出すことでSelect文が実行されます。
抽出されたデータはRecordsetから取得することができます。
55行目で、Recordsetからデータを取り出してExcelのシートに貼り付けています。
動作確認
マクロを実行した実行結果は、本記事内のExcelファイルの例の内容をご覧ください。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目の「ADODB.Connection」と6行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim adoCON As New ADODB.Connection 'Connection用変数
Dim rs As ADODB.Recordset 'レコードセット用変数
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、SQLのSelect文を使ってCSVファイル(ヘッダあり)から対象データを抽出しExcelのシートに貼り付ける方法についてご説明しました。
CSVファイル(ヘッダあり)から対象データを抽出しExcelのシートに貼り付けたい時は参考にしてみてくださいね。
今回はヘッダがあるCSVファイルについてご説明しました。
ヘッダがないCSVファイルを扱う場合は次の記事をご参照ください。
【ExcelVBA】SQLのSelect文を使ってCSVファイルから対象データを抽出しExcelのシートに貼り付ける方法(※CSVのデータにヘッダなし)
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。
