この記事では、指定した文字(列)が含まれる行がCSVファイルの何行目にあるのか特定する方法についてご説明します。
【動画】指定した文字(列)が含まれる行がCSVファイルの何行目にあるのか特定する実際の動き
本題に入る前に、まずは次の動画をご覧ください。
まずはCSVをExcelのシートに取り込み、行番号を振ります。
その行番号を振ったデータから条件に合致するデータを抽出するSQL文を実行し、取得されたデータと行番号をシートに貼り付けています。
この行番号がCSVファイルの何行目にあるのかを表しています。
なぜわざわざシートに貼り付けて行番号を振っているのかというと、CSVファイルのデータを取り込むタイミングでそのデータに対し行番号を振ることができないからです。
なのでその解決策として一旦Excelのシートに貼り付けてROW関数を使い行番号を振っています。
マクロ作成の流れ
このシートは、CSVファイルのデータを取り込むタイミングでそのデータに対し行番号を振ることができない解決策として作成しておきます。
この項目名は、指定して文字(列)を条件にデータを抽出するためのSELECT文のフィールド名に使います。
なお、2行目からCSVデータが出力されているので、ROW関数の戻り値から一つ値をマイナスさせます。(ROW関数は行位置を返すので、STEP.2で出力した項目名の行1つ分をマイナスさせないと値が1つずれる)
STEP.4で降った項番がCSVファイルの何行目にあるのかを表しています
Excelファイルの例
今回用意したExcelファイルは次の通りです。
コードの例
Excelのマクロのコード(例)
Option Explicit
Private Sub btn_getCSVRowPos_Click()
Dim cAdoCON As ADODB.Connection 'Connection用変数(CSV接続用)
Dim eAdoCON As ADODB.Connection 'Connection用変数(自分自身のExcel接続用)
Dim cRs As ADODB.Recordset 'レコードセット用変数(CSV接続用)
Dim eRs As ADODB.Recordset 'レコードセット用変数(自分自身のExcel接続用)
Dim ws As Worksheet 'Worksheet用変数
Dim sqlStr As String 'SQL文用変数
Dim csvFilePath As String 'CSVファイルの在り処
Dim shtExistFlg As Boolean 'CSVファイルの取り込み先シートの存在確認フラグ
Dim rng As Range 'Rangeオブジェクト格納用変数
Const csvFileNM As String = "data.csv" 'CSVファイル
Const csvDataSheetNM As String = "work" 'CSVファイルデータを取り込む先のシートの名前
'シート存在確認フラグにfalse(存在しない)を設定する
shtExistFlg = False
'フォルダ名とファイル名貼り付け用シート有無のチェック
For Each ws In Worksheets
If ws.Name = csvDataSheetNM Then
'シート「data」が存在している場合
'変数「shtExistFlg」にTrueを設定する
shtExistFlg = True
End If
Next ws
If shtExistFlg = False Then
'シート「data」が存在しない場合
'テーブルから取得するデータを列挙するためのシートを新規作成する
Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
.Name = csvDataSheetNM
'シート名を取得する
Set ws = Worksheets(csvDataSheetNM)
Else
'シート「data」が存在する場合
'シート名を取得する
Set ws = Worksheets(csvDataSheetNM)
'シート「data」のセル全てをクリアする
ws.Cells.Clear
End If
'CSVのデータを貼り付ける先のシートに項目名(ヘッダ)を出力する
ws.Range("A1").Value = "行"
ws.Range("B1").Value = "データ"
'データを出力するセルの範囲を取得する
Set rng = Worksheets("top").Range("D2").End(xlDown)
'データを出力するセルをクリアする
Worksheets("top").Range("D2:H" & rng.Row).ClearContents
'CSVファイルの在り処を取得する
csvFilePath = Worksheets("top").Range("csvFilePath").Value
'Connectionインスタンスの生成
Set cAdoCON = New ADODB.Connection
'CSVへのコネクション
With cAdoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
'コネクションを開く
.Open csvFilePath & "\"
End With
'クライアントサイドカーソルに変更
cAdoCON.CursorLocation = adUseClient
'Recordsetオブジェクトのインスタンスを生成する
Set cRs = New ADODB.Recordset
'SQL文作成
sqlStr = "select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & csvFileNM & "]"
'SQLを実行しレコードセットを取得する
Set cRs = cAdoCON.Execute(sqlStr)
'データをシート「top」に貼り付ける
ws.Range("B2").CopyFromRecordset cRs
'行を振る
ws.Range("A2:A" & cRs.RecordCount + 1).Formula = "=ROW()-1"
'シート「top」を表示する
Worksheets("top").Select
'Connectionインスタンスの生成
Set eAdoCON = New ADODB.Connection
With eAdoCON
'接続情報の取得
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
";Extended Properties =Excel 12.0;"
'データソースへの接続を開く
.Open
End With
'Recordsetオブジェクトのインスタンスを生成する
Set eRs = New ADODB.Recordset
'SQL文作成
sqlStr = "select"
sqlStr = sqlStr & " 行"
sqlStr = sqlStr & " ,データ"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & csvDataSheetNM & "$A1:B" & cRs.RecordCount + 1 & "]"
sqlStr = sqlStr & " where"
sqlStr = sqlStr & " データ like '%" & Worksheets("top").Range("searchVal").Value & "%'"
'Recordsetを開く
eRs.Open sqlStr, eAdoCON, adOpenStatic
'データをシート「top」に貼り付ける
Worksheets("top").Range("D2").CopyFromRecordset eRs
'後処理
cAdoCON.Close
eAdoCON.Close
Set cAdoCON = Nothing
Set eAdoCON = Nothing
Set cRs = Nothing
Set eRs = Nothing
End Sub
注目すべきコード①
最初に見て頂きたいのは22行目から56行目です。
'フォルダ名とファイル名貼り付け用シート有無のチェック
For Each ws In Worksheets
If ws.Name = csvDataSheetNM Then
'シート「data」が存在している場合
'変数「shtExistFlg」にTrueを設定する
shtExistFlg = True
End If
Next ws
If shtExistFlg = False Then
'シート「data」が存在しない場合
'テーブルから取得するデータを列挙するためのシートを新規作成する
Worksheets.Add(After:=Worksheets(Worksheets.Count)) _
.Name = csvDataSheetNM
'シート名を取得する
Set ws = Worksheets(csvDataSheetNM)
Else
'シート「data」が存在する場合
'シート名を取得する
Set ws = Worksheets(csvDataSheetNM)
'シート「data」のセル全てをクリアする
ws.Cells.Clear
End If
以上のコードは、CSVファイルのデータを取り込んで一旦保持するためのシートを作成するコードです。
シート作成するにはまずそのシートが既に存在するかを確認します。
シートが既に存在するか確認するのにピンポイントで確認する機能は無いので、方法としてはExcelファイルに存在するシートの名前を一つ一つ突き合わせて存在しているのかを確認します。
Excelファイルに存在するシートの名前を一つ一つ突き合わせるために、22行目のFor文でExcelのシートの数分繰り返します。
24行目ではExcelのシート名と、CSVファイルのデータを取り込んで一旦保持するためのシートの名前が一致するかを確認します。
一致すれば、CSVファイルのデータを取り込んで一旦保持するためのシートが存在していることが確認できたので、54行目でそのシートをクリアします。
一致するシートは無ければWorksheetsコレクションのAddメソッドでシートを作成します。(40行目)
注目すべきコード②
次に見て頂きたいのは59行目と60行目です。
'CSVのデータを貼り付ける先のシートに項目名(ヘッダ)を出力する
ws.Range("A1").Value = "行"
ws.Range("B1").Value = "データ"
以上のコードは、作成したCSVファイルのデータを取り込んで一旦保持するためのシートの1行目に項目名を出力するコードです。
この項目名は、指定して文字(列)を条件にデータを抽出するためのSELECT文のフィールド名に使います。
注目すべきコード③
次に見て頂きたいのは59行目から60行目です。
'CSVファイルの在り処を取得する
csvFilePath = Worksheets("top").Range("csvFilePath").Value
'Connectionインスタンスの生成
Set cAdoCON = New ADODB.Connection
'CSVへのコネクション
With cAdoCON
.Provider = "Microsoft.ACE.OLEDB.12.0"
.Properties("Extended Properties") = "Text;HDR=No;FMT=Delimited"
'コネクションを開く
.Open csvFilePath & "\"
End With
以上のコードは、マクロがCSVに接続するための接続情報を取得するコードです。
69行目では、CSVの在り処を取得し、72行目で生成したConnectionインスタンスに設定します。(75行目から83行目)
注目すべきコード④
次に見て頂きたいのは89行目から98行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set cRs = New ADODB.Recordset
'SQL文作成
sqlStr = "select"
sqlStr = sqlStr & " *"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & csvFileNM & "]"
'SQLを実行しレコードセットを取得する
Set cRs = cAdoCON.Execute(sqlStr)
以上のコードは、CSVファイルからデータを取得するSELECT文を用意して実行するコードです。
92行目から95行目ではCSVファイルからデータを取得するSELECT文を用意し、98行目ではそのSELECT文をExecuteメソッドの引数に指定して実行しています。
なお、SELECT文のFROM句にはテーブル名ではなくCSVファイルのファイル名を指定しています。(フルパスでは指定しない)
注目すべきコード⑤
次に見て頂きたいのは101行目から104行目です。
'データをシート「top」に貼り付ける
ws.Range("B2").CopyFromRecordset cRs
'行を振る
ws.Range("A2:A" & cRs.RecordCount + 1).Formula = "=ROW()-1"
以上のコードは、取り込んだCSVデータをシートに貼り付けて(B列)、その左隣のセル(A列)にROW関数(=ROW())を貼り付けている処理のコードです。
貼り付けた後のイメージは下の画像の通りです。
ちなみに、上記のCSVデータの元は下の画像の通りです。
上のCSVファイルをそのままシートに貼り付けています。
シートの1行目は項目名が出力されていてその次の2行目からCSVファイルを貼り付けられるので、ROW関数の戻り値から一つ値をマイナス(=ROW()-1)させます。(ROW関数は行位置を返すので、項目名の行1つ分をマイナスさせないと値が1つずれる)
注目すべきコード⑥
次に見て頂きたいのは110行目から122行目です。
'Connectionインスタンスの生成
Set eAdoCON = New ADODB.Connection
With eAdoCON
'接続情報の取得
.Provider = "Microsoft.ACE.OLEDB.12.0"
.ConnectionString = "Data Source = " & ThisWorkbook.FullName & _
";Extended Properties =Excel 12.0;"
'データソースへの接続を開く
.Open
End With
以上のコードは、マクロがExcelに接続するための接続情報を取得するコードです。
110行目では、Connectionインスタンスを生成し、Excelファイルの接続情報を取得してConnectionインスタンスに設定しています。
120行目では、ConnectionインスタンスのOpenメソッドを実行し、Excelファイルのデータソースに接続しています。
Openメソッドを実行することで、マクロがSELECT文を実行してデータを取得することができるようになります。
注目すべきコード⑦
次に見て頂きたいのは125行目から137行目です。
'Recordsetオブジェクトのインスタンスを生成する
Set eRs = New ADODB.Recordset
'SQL文作成
sqlStr = "select"
sqlStr = sqlStr & " 行"
sqlStr = sqlStr & " ,データ"
sqlStr = sqlStr & " from"
sqlStr = sqlStr & " [" & csvDataSheetNM & "$A1:B" & cRs.RecordCount + 1 & "]"
sqlStr = sqlStr & " where"
sqlStr = sqlStr & " データ like '%" & Worksheets("top").Range("searchVal").Value & "%'"
'Recordsetを開く
eRs.Open sqlStr, eAdoCON, adOpenStatic
以上のコードは、CSVファイルのデータを取り込んで一旦保持するためのシートから検索条件に該当するデータを抽出するSELECT文を作成し実行するコードです。
125行目では、Recordsetオブジェクトのインスタンスを生成しています。
128行目から134行目では、CSVファイルのデータを取り込んで一旦保持するためのシートから検索条件に該当するデータを抽出するSELECT文を作成しています。
なお今回のサンプルでは、「部分一致」で検索しています。(LIKE演算子を指定して検索条件の前後に「%」を指定)
SELECT文が用意出来たら137行目でそのSELECT文を、RecordsetオブジェクトのOpenメソッドの引数に指定して実行します。
注目すべきコード⑧
次に見て頂きたいのは140行目です。
Recordsetを開く
eRs.Open
以上のコードは、SELECT文を実行して抽出したデータをシートに貼り付けている処理のコードです。
貼り付け後のイメージは次の通りです。
動作確認
マクロ実行前
CSVファイルから「dll」の文字列が含まれるデータの行を特定します。
CSVには「dll」の文字列が含まれるデータの行は次の通りです。
マクロ実行後
マクロを実行すると、「dll」の文字列が含まれるデータの行と行番号がシートに出力されました。
【注意】参照設定が必要です
一つ注意点があるのですが、先ほどのコードを動かすには参照設定が必要です。
参照設定の一覧(下の画像を参考)から次の項目(ライブラリ)にチェックを付けて「OK」ボタンをクリックします。
- Microsoft ActiveX Data Objects 2.8 Library(msado28.tlb)
なぜ必要かというと、Excelのマクロのコードの5行目と6行目の「ADODB.Connection」、7行目と8行目の「ADODB.Recordset」というオブジェクトが「msado28.tlb」というファイルを参照するからです。
Dim cAdoCON As ADODB.Connection 'Connection用変数(CSV接続用)
Dim eAdoCON As ADODB.Connection 'Connection用変数(自分自身のExcel接続用)
Dim cRs As ADODB.Recordset 'レコードセット用変数(CSV接続用)
Dim eRs As ADODB.Recordset 'レコードセット用変数(自分自身のExcel接続用)
この参照設定をしないと下の画像のエラーが出ますので必ず行う必要があります。
ここでは「msado28.tlb」とは何者かについては記事の本題から逸れてしまうので詳細は割愛しますが、マクロで「ADODB.Connection」「ADODB.Recordset」というオブジェクトを使う場合は参照設定しないと動かない、程度に思って頂ければと思います。
最後に
本記事では、指定した文字(列)が含まれる行がCSVファイルの何行目にあるのか特定する方法についてご説明しました。
もしCSVファイルの中からある文字(列)が何行目に含まれているのか知りたい場合は本記事を参考にしてみてくださいね。
Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら
Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。
Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。
→ 受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら
