この記事では、SQLのSelect文を使ってCSVファイル(ヘッダなし)から対象データを抽出しExcelのシートに貼り付ける方法についてご説明します。
今回はヘッダがないCSVファイルについてご説明します。
ヘッダがあるCSVファイルを扱う場合は次の記事をご参照ください。
【動画】SQLのSelect文を使ってCSVファイル(ヘッダなし)から対象データを抽出しExcelのシートに貼り付ける実際の動き
本題に入る前に、まずは次の動画をご覧ください。
マクロがADOを使用してCSVに接続し、SQLのSelect文を実行して対象データを検索・抽出しExcelのシートに貼り付けています。
抽出条件には、CSVファイルのデータの列位置(1番左のデータならF1、左から2番目のデータならF2)を指定しています。
マクロ作成の流れ
Select文が実行されると、Excelのシートにデータが貼り付けられます。
CSVファイルの例
今回使うテストデータのCSVファイルは次の通りです。
1行目にヘッダ(「月」「名前」と科目名)は無く、生徒名と月、点数が入力されています。
Excelファイルの例
今回は次のExcelファイルを用意しました。
マクロを実行すると、CSVファイルにある各生徒の点数がF列からJ列に出力されます。(D列には月、E列には生徒の名前が出力)
ちなみに下の画像は、国語で点数が75のデータを条件に指定してマクロを実行した結果です。
国語の点数は、CSVファイルの左から3番目の列の値なので、「3」を指定してマクロを実行しシート(D列からJ列)に出力しています。
配置されているセルの名前は次の通りです。
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 sqlStr As String 'SQL文用変数 Dim csvFilePath As String 'CSVファイルの在り処 Const csvFileNM As String = "data.csv" 'CSVファイル 'Recordsetオブジェクトのインスタンスを生成する Set rs = New ADODB.Recordset 'CSVファイルの在り処を取得する csvFilePath = Worksheets("top").Range("csvFilePath").Value 'CSVへのコネクション With adoCON .Provider = "Microsoft.ACE.OLEDB.12.0" .Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited" 'コネクションを開く .Open csvFilePath & "\" End With 'データを取得するSQL文を作成する sqlStr = "select" sqlStr = sqlStr & " *" sqlStr = sqlStr & " from" sqlStr = sqlStr & " [" & csvFileNM & "]" sqlStr = sqlStr & " where" Select Case Worksheets("top").Range("searchFld").Value Case 1, 2 '月か名前の場合(文字列データ) sqlStr = sqlStr & " F" & Worksheets("top").Range("searchFld").Value sqlStr = sqlStr & " = '" sqlStr = sqlStr & Worksheets("top").Range("searchVal").Value & "'" Case Else '月か名前以外場合(数値データ) sqlStr = sqlStr & " F" & Worksheets("top").Range("searchFld").Value sqlStr = sqlStr & " = " sqlStr = sqlStr & Worksheets("top").Range("searchVal").Value End Select 'SQLを実行しレコードセットを取得する Set rs = adoCON.Execute(sqlStr) 'データをシート「top」に貼り付ける Worksheets("top").Range("D2").CopyFromRecordset rs '後処理 adoCON.Close Set rs = Nothing Set adoCON = Nothing End Sub
コードの解説
注目すべきコード①
最初に見て頂きたいのは16行目から25行目です。
'CSVファイルの在り処を取得する csvFilePath = Worksheets("top").Range("csvFilePath").Value 'CSVへのコネクション With adoCON .Provider = "Microsoft.ACE.OLEDB.12.0" .Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited" 'コネクションを開く .Open csvFilePath & "\" End With
以上のコードは、CSVファイルの在り処を元に、マクロがデータソース接続情報を取得して接続するコードです。
以上のコードを実行することで、CSVファイルに接続する準備が整いました。
ポイントは、CSVファイル名を以上のコードで明記するのではなく、CSVファイルの置き場を指定(Openメソッドに指定)しているところです。
CSVファイルを参照するコードはSelect文のFROM句で説明しています。
注目すべきコード②
次に見て頂きたいのは28行目から52行目です。
'データを取得するSQL文を作成する sqlStr = "select" sqlStr = sqlStr & " *" sqlStr = sqlStr & " from" sqlStr = sqlStr & " [" & csvFileNM & "]" sqlStr = sqlStr & " where" Select Case Worksheets("top").Range("searchFld").Value Case 1, 2 '月か名前の場合(文字列データ) sqlStr = sqlStr & " F" & Worksheets("top").Range("searchFld").Value sqlStr = sqlStr & " = '" sqlStr = sqlStr & Worksheets("top").Range("searchVal").Value & "'" Case Else '月か名前以外場合(数値データ) sqlStr = sqlStr & " F" & Worksheets("top").Range("searchFld").Value sqlStr = sqlStr & " = " sqlStr = sqlStr & Worksheets("top").Range("searchVal").Value End Select
以上のコードは、Select文を生成するコードです。
フィールド名
29行目ではフィールド名を指定しています。
今回は列全てのデータが欲しいので「*」を指定しています。
ではもし列ごとのデータが欲しい場合はどうすればいいのでしょうか。
今回使うCSVファイルにはヘッダが無いのでフィールド名に指定する項目名が無いのでどうやってフィールド名を指定すればいいんだろう?…と困ってしまいますよね。
そこで、CSVファイルにヘッダが無い場合どうすればいいのかというと、列の何番目かを指定すればいいんです。
「列の何番目」をどのように指定するかというと、1番目の列なら「F1」、2番目の列なら「F2」と記述します。
sqlStr = sqlStr & " F1,F2"
「F」は固定値で、「F」の次の数値が「列の何番目」になります。
Fの文字は小文字(f)でもOKです。
sqlStr = sqlStr & " f1,f2"
FROM句
31行目ではCSVファイルの名前を指定しています。
データベースの場合、SQLのSelect文のFROM句にはテーブル名を指定しますが、CSVファイルの場合はCSVファイル名をFROM句に指定します。
このCSVファイル名は、フルパスのファイル名ではなくファイル名自体を指定します。
例えばCSVファイル「data.csv」が「C:¥work¥test」の配下にある場合、「C:¥work¥test¥data.csv」と指定するのではなく、「data.csv」のようにファイル名を指定します。
CSVファイルのパスは16行目と24行目で指定しています。
'CSVファイルの在り処を取得する csvFilePath = Worksheets("top").Range("csvFilePath").Value
'コネクションを開く .Open csvFilePath & "\"
WHERE句
32行目以降はWHERE句を組み立てるコードですが、文字列データの場合は「’」で値を囲まないといけないので、文字列データと数値データそれぞれにコードを用意する必要があります。
文字列データの場合は40行目から42行目、数値データの場合は48行目から50行目のコードが呼ばれます。
実際のSQL文(例)
コードだけではSelect文が分かりづらいと思うので、Select文の例を以下にお見せします。
検索対象の項目が文字列データの場合
名前が「生徒2」のデータを抽出する場合は次のSelect文になります。
select * from [data.csv] where F2 = '生徒2'
以上は条件を満たしたデータの、すべての項目のデータを取得する場合のSelect文です。
もし、条件を満たしたデータの、「名前」と「数学」のデータだけを取得したい場合は次の通りのSelect文になります。
※「名前」はCSVファイルにある1番左の列、「数学」は左から4番目の列
select F1, F4 from [data.csv] where F2 = '生徒2'
検索対象の項目が数値データの場合
「英語」の点数が「93」のデータを抽出する場合は次のSelect文になります。
select * from [data.csv] where 英語 = 93
以上は条件を満たしたデータの、すべての項目のデータを取得する場合のSelect文です。
もし、条件を満たしたデータの、「名前」と「数学」のデータだけを取得したい場合は次の通りのSelect文になります。
※「名前」はCSVファイルにある1番左の列、「数学」は左から4番目の列
select F1, F4 from data.csv where 英語 = 93
注目すべきコード③
次に見て頂きたいのは55行目から58行目です。
'SQLを実行しレコードセットを取得する Set rs = adoCON.Execute(sqlStr) 'データをシート「top」に貼り付ける Worksheets("top").Range("D2").CopyFromRecordset rs
以上はSelect文を実行し、CSVファイルから検索・抽出したデータをExcelのシートに貼り付けるコードです。
55行目では、Executeの引数にSelect文を指定してExecuteを呼び出すことでSelect文が実行されます。
抽出されたデータはRecordsetから取得することができます。
58行目で、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ファイルを扱う場合は次の記事をご参照ください。
プログラミングのスキルを習得するなら
プログラミングのスキルを習得したい、今のスキルをもっと高めたい、そう考えているなら「プログラミングスクール」がおすすめです。
プログラミングのスキルの基礎を身につけるなら「TechAcademy」で1週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。