こんにちは！ 健史です。
日次・週次・月次など定期的な業務で、コンピュータシステムから出力されたデータや取引先などから提供されるデータから特定の文字列を探して確認や転記作業を行っていることありませんか。
プログラムで指定したフォルダ内のテキストファイルから探したい文字列をＥｘｃｅｌシートに抽出する処理を作成してみました。
姉妹版の記事として、以下も参照頂ければ思います。
尚この記事は、Excel-VBAを起動して貼り付ければ動作することを目指しています。
Excel-VBAの構造、起動方法、実行方法などを理解されていない方は、以下の記事を参照しながら一度やってみてから対応されることをお勧めします。
実行中に発生したエラーの対処法は、上記記事の2.エラーが発生したときの対処法に記載しています。(この段落のリンクから直接遷移します)
プログラムの概要
今回のサンプルプログラムは
イメージは以下です。
1.入力
◇ファイル指定
◇file10.txt
SQL*Loader: Release ～ ・・・省略・・・ 制御ファイル: E:\学校.ctl データファイルE:\学校.csv ・・・省略・・・ 表学校: 11行のロードに成功しました。 12行はデータ・エラーのためロードされませんでした。 13行はWHEN句のエラーのためロードされませんでした。 14行はすべてのフィールドがNULLのためロードされませんでした。 ・・・省略・・・ スキップされた論理レコードの合計: 5 読み込まれた論理レコードの合計: 6 拒否された論理レコードの合計: 7 廃棄された論理レコードの合計: 8 ・・・省略・・・ 制御ファイル: E:\学生.ctl データファイルE:\学生.csv ・・・省略・・・ 表学生: 18行のロードに成功しました。 17行はデータ・エラーのためロードされませんでした。 16行はWHEN句のエラーのためロードされませんでした。 15行はすべてのフィールドがNULLのためロードされませんでした。 ・・・省略・・・ スキップされた論理レコードの合計: 4 読み込まれた論理レコードの合計: 3 拒否された論理レコードの合計: 2 廃棄された論理レコードの合計: 1 ・・・省略・・・
◇file20.txt
SQL*Loader: Release ～ ・・・省略・・・ 制御ファイル: E:\学校.ctl データファイルE:\学校.csv ・・・省略・・・ 表学校: 11行のロードに成功しました。 12行はデータ・エラーのためロードされませんでした。 13行はWHEN句のエラーのためロードされませんでした。 14行はすべてのフィールドがNULLのためロードされませんでした。 ・・・省略・・・ スキップされた論理レコードの合計: 15 読み込まれた論理レコードの合計: 16 拒否された論理レコードの合計: 17 廃棄された論理レコードの合計: 18 ・・・省略・・・ 制御ファイル: E:\学生.ctl データファイルE:\学生.csv ・・・省略・・・ 表学生: 18行のロードに成功しました。 17行はデータ・エラーのためロードされませんでした。 16行はWHEN句のエラーのためロードされませんでした。 15行はすべてのフィールドがNULLのためロードされませんでした。 ・・・省略・・・ スキップされた論理レコードの合計: 14 読み込まれた論理レコードの合計: 13 拒否された論理レコードの合計: 12 廃棄された論理レコードの合計: 11 ・・・省略・・・
2.出力
上記テキストファイルより白い文字を抽出します。
仕様概要は、以下の通りです。
1.設定シートの[入力ファイル]を読み込み、ワークシートに格納する
2.設定シートの[検索文字]をワークシートから検索する
3.上記2.で検索した以降の範囲で、設定シートのC列[成功]からJ列[廃棄]までをワークシートの検索し以下の処理を行う
1).C列[成功]からF列[NULL]までは、1桁目から"行"という文字列の前までを抽出
2).G列[スキップ]からJ列[廃棄]までは、"合計:"という文字列の後ろから最後尾までを抽出
今回のサンプルデータはオラクルデータベースにおける、CSVデータをＤＢに直接ロードする「ＳＱＬＬＯＡＤＥＲ」というツールの出力結果を使用した処理です。実物はもっと長いのですが、省略し短くしています。
日々の業務でコンピュータシステムが出力したテキストなどのデータから文字列を検索して、確認や別のＥｘｃｅｌシートなどに転記するのは面倒でストレスもかかります、間違えることもあるでしょう。
テキストファイルの文字列を自動で検索し一覧表に表示してくれるのであれば、あとは数字を確認したり転記したりするだけです。
別のＥｘｃｅｌシートへ転記するのであれば、その処理を加えてもよいでしょう。
プログラムの作成と実行
実行までの手順
1.データ準備
1).検索対象となるデータ
検索対象のデータを[入力ファイル]に作成しておきます。
①サクラエディタやTeraPadの場合
上記イメージの入力欄に記載した２つのデータをコピー＆ペーストし、サクラエディタやTeraPadなどのエディタにて、文字コードを"SJIS"で保存します。
サクラエディタとTeraPadでは、右下(文字位置の行・桁を表示している行)の表示が"SJIS"になっていればそのまま保存します。
"UTF-8"など別の文字コードになっている場合は、以下で変更します。
・サクラエディタ
[ファイル(F)]-[名前を付けて保存(A)]で、ファイル名・ファイル種類の下の[文字コードセット(C)]に"SJIS"を選択
・TeraPad
[ファイル(F)]-[文字/改行コード指定保存(K)]で、[文字コード(J)]に"SHIFT-JIS"を選択
②メモ帳(notepad)の場合
保存するときに[文字コード(E)]を"ANSI"に変えて保存します。
これは、Windowsの[メモ帳(notepad)]の文字コード既定値が"UTF-8"なったための対応です。
参考記事：メモ帳の文字コード既定値がUTF-8に、Windows 10「May 2019 Update」
文字コードが"UTF-8"で保存されると、設定シートで指定した検索文字として検索できません。
[メモ帳(notepad)]にて"UTF-8"で保存した場合には再度開いて、[名前を付けて保存(A)]を選択し、[文字コード(E)]を"ANSI"にして保存します、「上書きしますか？」のメッセージで"はい(Y)"を選択して。
尚、当記事のプログラムを流用してコンピュータが出力したテキストファイルを検索できないことがあるかもしれませんが、そのときは文字コードを確認します。
実際にどうなるのかわかりませんが、その際は検索できる文字コードで出力してもらうように依頼するとか、できないなら変換する処理を加える必要があると思います。
余談ですが、私は「サクラエディタ」を使用しています。
サクラエディタに限らずダウンロードするときは全てにおいて、広告表示されている別の意図しないソフトウエアをダウンロードしないように注意します。
2.Excelを起動
1).シート１(設定シート)に以下のデータを貼り付ける
ドラッグ＆コピー後にExcelに貼り付けるとき、[形式を選択して貼り付ける]-[テキスト]で貼り付けます。(セル[A1]で[右クリック]、[形式を選択して貼り付ける]-[テキスト])
入力ファイル 検索文字 成功 データ・エラー WHEN句 NULL スキップ 読み込まれた 拒否 廃棄 G:\TEST2\file10.txt 学校 学生 G:\TEST2\file20.txt 学生 学校
プログラムを実行する場合のフォルダ名やファイル名は、実際の環境に合わせて修正します。
3.Excel-VBAを起動
Altを押した状態でF8キーを押します。
4.[マクロ名(M):]に'test'と入力(''は不要、''内のtestを入力)
5.[作成(C)]をクリック
私はマクロ名を入力したら、そのままEnterを押します。
6.以下のプログラムをドラッグ＆コピー
Const iy1_from As Long = 3
Const iy1_to As Long = 10
Const iy1_branch As Long = 7
Const cnsBRCHAR1 As String = "行"
Const cnsBRCHAR2 As String = "合計:"
Const cnsTXT1 As String = "表"
Const cnsTXT2 As String = ":"
'
Dim ix1, ix1_max, ix2, ix2_t As Long
Dim strTxt As String
'範囲を示す変数はRANGEで定義
Dim rng_1, rng_2, row_from, rng_tmp As Range
Dim pos_now, pos_st, pos_end, pos_len As Long
'値がすべて有効値であるセルB列の最終行を取得する
ix1_max = Sheets(1).Range("B1").End(xlDown).Row
'取得した内容を格納するセルをクリア
For ix1 = 2 To ix1_max
For iy1 = iy1_from To iy1_to
Sheets(1).Cells(ix1, iy1) = ""
Next
Next
For ix1 = 2 To ix1_max
'入力ファイル名に値がある場合のみワークシートに読み込む
If Sheets(1).Cells(ix1, 1) <> "" Then
If Dir(Sheets(1).Cells(ix1, 1)) = "" Then
'①C列に"入力ファイルがありません"をセット
Sheets(1).Cells(ix1, 3) = "入力ファイルがありません"
'②入力ファイルに値ある行の前まで[ix1]をカウントアップ
ix1 = ix1 + 1
Do Until ix1 > ix1_max Or Sheets(1).Cells(ix1, 1) <> ""
ix1 = ix1 + 1
Loop
'※入力ファイルが空白でないところまでの[ix1]を求めた後にマイナス１
ix1 = ix1 - 1
'③処理を抜ける
GoTo L1
End If
Open Sheets(1).Cells(ix1, 1) For Input As #1
Sheets(2).Cells.Clear
ix2 = 0
Do Until EOF(1)
Line Input #1, strTxt
ix2 = ix2 + 1
Sheets(2).Cells(ix2, 1) = strTxt
Loop
Close #1
'検索する範囲を求めておく
Set rng_1 = Sheets(2).Range("A1:A" & ix2)
End If
'検索文字(B列)の列を求める
strTxt = cnsTXT1 & Sheets(1).Cells(ix1, 2) & cnsTXT2
Set rng_tmp = rng_1.Find(strTxt, LookIn:=xlValues, lookat:=xlPart)
If rng_tmp Is Nothing Then
Sheets(1).Cells(ix1, 3) = "検索文字(B列)がありません"
GoTo L1
End If
row_from = rng_tmp.Row
'検索文字(B列)の行を起点とする検索範囲をセット
Set rng_2 = Sheets(2).Range("A" & row_from & ":A" & ix2)
'求めたい値の行を検索し値をセット
For iy1 = iy1_from To iy1_to
strTxt = Sheets(1).Cells(1, iy1)
Set rng_tmp = rng_2.Find(strTxt, LookIn:=xlValues, lookat:=xlPart)
If rng_tmp Is Nothing Then
Sheets(1).Cells(ix1, iy1) = "検索文字がありません"
GoTo L1
End If
'①求めたい値の行を[ix2_t]にセット
ix2_t = rng_tmp.Row
'②求めたい値が異なるので判断
If iy1 < iy1_branch Then
'③求めたい文字列の抽出-1
pos_st = 1
pos_now = InStr(pos_st, Sheets(2).Cells(ix2_t, 1), cnsBRCHAR1)
pos_len = pos_now - pos_st
Sheets(1).Cells(ix1, iy1) = Left(Sheets(2).Cells(ix2_t, 1), pos_len)
Else
'③求めたい文字列の抽出-2
pos_st = 1
pos_now = InStr(pos_st, Sheets(2).Cells(ix2_t, 1), cnsBRCHAR2)
pos_end = pos_now + 2
pos_len = Len(Sheets(2).Cells(ix2_t, 1)) - pos_end
Sheets(1).Cells(ix1, iy1) = Right(Sheets(2).Cells(ix2_t, 1), pos_len)
End If
Next
L1:
Next
6.以下に貼り付け(ペースト)
Sub test()
ここに貼り付ける
End Sub
7.F5キーで実行
8.実行結果の確認
設定シートの件数欄に値が入っています。
またハードコピーは貼り付けませんが、ワークシートには最後に読み込んだファイルが残っています。
プログラムの詳細説明
For文については、以下の記事を参照下さい。
プログラム内に記載したコメントについて説明します。
◇範囲を示す変数はRANGEで定義
今回「A1:A10」といった範囲を示す値を変数として使いますが、変数の属性は「As Range」として定義します。
◇値がすべて有効値であるセルB列の最終行を取得する
２つ下の「入力ファイル名に値がある場合のみワークシートに読み込む」に記載しますが、設定シートのA列に値がない場合があります。
処理としてはB列に値があるものすべてを処理する必要があるため、行の最大値を取得する場合は全ての行に値のあるB列を指定します。
行の最大値を取得する説明は、以下に記載しています。
参考：last_row1 = Worksheets(1).Range("A1").End(xlDown).Row について
◇取得した内容を格納するセルをクリア
読み込んだファイルから取得する件数欄をあらかじめクリアしておきます。
◇入力ファイル名に値がある場合のみワークシートに読み込む
まずファイルの読み込みで必要な処理だけを以下に記述します。詳細は説明致しません。
Dim text1 As string
Open "G:\TEST2\file10.txt" For Input As #1
Do Until EOF(1)
Line Input #1, text1
' １件読み込んだデータを処理する内容を記載
Loop
Close #1
次に当プログラムでの概要を説明します。
・入力ファイルに値がない場合は、入力ファイルは読み込みません。値がある時だけ読み込みます。
・入力ファイルがない場合は、
①C列に"入力ファイルがありません"をセット
②入力ファイルに値ある行の前まで[i1]をカウントアップ
※入力ファイルが空白でないところまでの[i1]を求めた後にマイナス１
③処理を抜ける
処理を抜けた後に[For i1 = 2 To i1_max][Next]にて自動で[i1]がカウントアップされます。
(ですから)次の入力ファイルに値ある行の前まで処理したようにしておきます。
◇検索する範囲を求めておく
ワークシートに読み込んだ最後の[i2]までを範囲とし[rng_1]にセットします。
10行であれば、検索範囲[rng_1]には[A1:A10]がセットされます。
◇検索文字(B列)の列を求める
当記事の例では、ＳＱＬＬＯＡＤＥＲでＤＢ上にロードするテーブル名は、"学校"と"学生"です。
今回検索位置として求める行は、次の検索として求める行がある直前の"表学校:"や"表学生:"としています。
理由は、"制御ファイル: E:\学校.ctl"の後に次に求める文字列"成功"や"NULL"などが、本来検索すべき行の前(省略した部分)にあった場合はその行を検索してしまうからです。
ですから、検索したい行の直前に位置付けるためのユニークな文字列"表学校:"という文字列を検索しています。
今回はユニークな文字あるから対応しましたが、無いのであれば別の工夫が必要な場合もあります。
◇検索文字(B列)の行を起点とする検索範囲をセット
上記で求めた行から最後の行までの範囲を[rng_2]にセットします。
◇求めたい値の行を検索し値をセット
仕様概要[3.]で説明した部分です。
上記で求めた[rng_2]の範囲で、C列[成功]からJ列[廃棄]まで検索します。
①求めたい値の行を[i2_t]にセット
検索できた文字列の行をセットします。
②求めたい値が異なるので判断
仕様概要[3.]、①・②の説明です。
[スキップ]のG列は7番目です。
7番目[G列]から10番目[J列]までと3番目[C列]から6番目[F列]まででは求めたい値のある位置が異なります。
7番目を境に処理を分けるので判断する内容を7番目にしました。
6番目でも良いのですよ。その場合は大小比較処理を変更する必要があります。
◇求めたい文字列の抽出
文字列抽出の説明は、以下に記載しています。
制限事項
制約事項がいくつかあります。思い付くものを記載します。
◇起動したＥｘｃｅｌファイルには２つのシートが必要
[設定]シートの他に[ワーク]シートをもう一つ追加しておきます。
このプログラムでは、シート名は[設定]や[ワーク]でなくてもよいです、[Sheet1][Sheet2]でもよいです。
◇プログラム修正が必要な場合
当記事のプログラムを流用し活用する場合です。
制約事項とは言えないですが、この括りの中に記載します。
修正内容の想定も考えると切りがないのですが、いくつか記載します。
当記事のプログラムは、①B列[検索文字]を検索し、②次に以降のC列[成功]からJ列[廃棄]を検索し、②の行の中の文字列から抽出します。
また、最終的に抽出する文字列の位置が「C列[成功]からF列[NULL]」と「G列[スキップ]からJ列[廃棄]」で異なります。
上記を踏まえての修正が必要な場合です。修正内容までは記載しません。
・２段階の文字列検索ではなく、①B列[検索文字]の行に抽出したい文字列がある場合
・抽出する文字列の位置が列によって異なるのではなく同じ場合
・抽出する文字列を見つけるための"行"や"合計:"ではなく例えば"データ件数は"の場合
・抽出する文字の個数(②)が多い・少ない場合
「C列[成功]からF列[NULL]」だけでよい、もっと増やしたい などです。
◇データ量に制限がある
[1.設定シートの[ファイル名]を読み込み、ワークシートに格納する]で、ワークシートに格納できるデータ量はEXCELのバージョンによって異なります。
尚、当記事のプログラムは行数の上限をチェックしていません。
最後に
今回使用したのはＳＱＬＬＯＡＤＥＲが出力したファイルでしたが、それだけが入力ファイルになるものではありません。
また敢えて抽出する文字の位置が異なるプログラムを作成しました。
作成したプログラムの動作確認の意味もあり、１つのファイルをコピーして内容を変更し、また学校と学生の順番を敢えて入れ替えてみました。
検索対象のテキストに記載されている順序はＡ→Ｂでも、確認したい順序はＢ→Ａといった場合に対応できるようになっています。
文字列にルールがある場合には使えます。
使うシーンに応じて対応して頂ければと思います。
コメント