【ExcelVBA】SQLのSelect文を使ってCSVファイルから対象データを抽出しExcelのシートに貼り付ける方法(※CSVのデータにヘッダなし)

8分

SHARE

この記事では、SQLのSelect文を使ってCSVファイル(ヘッダなし)から対象データを抽出しExcelのシートに貼り付ける方法についてご説明します。

参考

今回はヘッダがないCSVファイルについてご説明します。
ヘッダがあるCSVファイルを扱う場合は次の記事をご参照ください。

【ExcelVBA】SQLのSelect文を使ってCSVファイルから対象データを抽出しExcelのシートに貼り付ける方法(※CSVのデータにヘッダあり)

すぐにソースコードを見たい場合はこちらをクリック

【動画】SQLのSelect文を使ってCSVファイル(ヘッダなし)から対象データを抽出しExcelのシートに貼り付ける実際の動き

本題に入る前に、まずは次の動画をご覧ください。


マクロがADOを使用してCSVに接続し、SQLのSelect文を実行して対象データを検索・抽出しExcelのシートに貼り付けています。

抽出条件には、CSVファイルのデータの列位置(1番左のデータならF1、左から2番目のデータならF2)を指定しています。

マクロ作成の流れ

STEP.1
ADOを使用してCSVファイルに接続する
ADOを使用してCSVファイルに接続します。
STEP.2
Select文を生成する
Select文を生成します。
STEP.3
ADODB.ConnectionオブジェクトのExecuteメソッドを呼び出してSelect文を実行する
ADODB.ConnectionオブジェクトのExecuteメソッドを呼び出してSelect文を実行します。
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」ボタンをクリックします。

  1. 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週間の無料体験があるので、これで「プログラミングの基礎」を学ぶのにおすすめですよ。

→　TechAcademyの「1週間 無料体験」はこちら



目次へ戻る

前の記事

【ExcelVBA】SQLのSelect文を使ってCSVファイ…

次の記事

【ExcelVBA】SQLのUNION ALLで複数のCSVフ…