ある法則を持って書かれたhtmlソースのテキストデータをEXCELでリスト化したいです。


ブロックで固められて書かれた要素を複数持つ、ホームページのソースをテキスト化したものが手元にあります。
これをうまいことEXCELにリスト化したいです。

たとえばあるソースの中に
<TR>
<TD class="ln">
<A HREF="●">▲<BR>■</A></TD>
<TD>◆</TD>
</TR>

<TR>
<TD class="ln">


みたいな書き方で複数行にわたる要素が書かれたブロックが複数書かれていたら、

A1には最初に書かれていたデータ●の、B1には▲、C1には■、D1には◆日付、
次のデータの●をB1、▲をB2、…って感じですべての行のデータををexcelに取り込む方法です。

あくまでも指定したソースからEXCELデータが取り出せればいいので、
たとえばソースをテキスト化して取り出したい表現を正規表現を使って表してEXCELにぶち込めば勝手に抽出してくれる、みたいな機能でもかまいません。

個人的にはテキストでのタブによるインデントは隣の枠に移るので、それをうまく利用すれば簡単にexcelに取り込めるのではないかと考えましたが、自分では無理でした。

何かいい方法はないでしょうか。ご存知の方、どうかよろしくお願いします。

回答の条件
  • URL必須
  • 1人5回まで
  • 13歳以上
  • 登録:2011/01/12 21:47:21
  • 終了:2011/01/19 21:50:02

ベストアンサー

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/01/13 00:08:35

ポイント35pt
Sub Macro1()
'   参照設定で以下2つにチェックをいれておくこと
'   Microsoft AxtiveX Data Objects x.x Library  x.xは2.5以降(もう少し古いものでもOKかもしれませんが調べてません)
'   Microsoft VBScript Regular Expressions x.x   x.xは5.5以降
'   (実行時バインドに変更する場合はUTF-8やadTypeTextなどを数値指定に置き換える必要あり)
'
'   事前準備
    Dim fileName As String, chrCode As String, sheetName As String, strText As String
    fileName = "d:\ソース.html": '読み込むファイル(フルパスでの指定が必要です ActiveWorkbook.Path を使うと便利かもしれません)
    chrCode = "UTF-8": 'ファイルの文字コード
    strText = ""
    sheetName = "Sheet1": '書き込むシート
    startCell = "A1": 'セル内の書き込み開始位置
    
'   読み込み
    Dim st As ADODB.Stream
    Set st = New ADODB.Stream
    With st
        .Type = adTypeText: 'テキストモード
        .Charset = chrCode: 'ファイルの文字コード
        .Open: '開始
        .LoadFromFile (fileName): 'ファイルオープン
        strText = .ReadText(adReadAll): '一括読み込み
        .Close: '閉じる
    End With
    Set st = Nothing

'   シートのクリア
    Worksheets(sheetName).Select
    Cells.ClearContents: 'シート全体をクリアしますので、一部のみを削除する場合は変更が必要
    Range(startCell).Select
    
'   抜き出しとセルへの書き込み
    Dim re As RegExp, mc As MatchCollection, m As Match
    Set re = New RegExp
    With re
        .Pattern = "<tr>(\n|.)+?href=""(.+?)"">(.+?)<br>(.+?)</a>(\n|.)+?<td>(.+?)</td>(\n|.)*?</tr>": 'パターンはもう少しスマートになるかもしれません
        .IgnoreCase = True: '大文字小文字は区別しない
        .Global = True: '全体を検索対象とする
        .MultiLine = False: '複数行として取り扱わない
        Set mc = .Execute(strText): '実行
        For Each m In mc
            Selection.Value = m.SubMatches(1)
            Selection.Offset(0, 1).Value = m.SubMatches(2)
            Selection.Offset(0, 2).Value = m.SubMatches(3)
            Selection.Offset(0, 3).Value = m.SubMatches(5)
            Selection.Offset(1, 0).Select: '次の行にカーソル移動
        Next m
        Set mc = Nothing
        Set re = Nothing
    End With
End Sub

http://msdn.microsoft.com/ja-jp/library/cc364272.aspx

http://msdn.microsoft.com/ja-jp/library/cc392403.aspx

id:holoholobird

早速のご回答、どうもありがとうございます。

With reのPatternの正規表現の部分について、どの記号がどういう意味を持つのかを、もう少し詳しく教えていただきたいです。

不勉強ながら申し訳ありませんが、どうかご指導のほど、よろしくお願いします。

2011/01/14 01:23:55

その他の回答(2件)

id:windofjuly No.1

うぃんど回答回数2625ベストアンサー獲得回数11492011/01/13 00:08:35ここでベストアンサー

ポイント35pt
Sub Macro1()
'   参照設定で以下2つにチェックをいれておくこと
'   Microsoft AxtiveX Data Objects x.x Library  x.xは2.5以降(もう少し古いものでもOKかもしれませんが調べてません)
'   Microsoft VBScript Regular Expressions x.x   x.xは5.5以降
'   (実行時バインドに変更する場合はUTF-8やadTypeTextなどを数値指定に置き換える必要あり)
'
'   事前準備
    Dim fileName As String, chrCode As String, sheetName As String, strText As String
    fileName = "d:\ソース.html": '読み込むファイル(フルパスでの指定が必要です ActiveWorkbook.Path を使うと便利かもしれません)
    chrCode = "UTF-8": 'ファイルの文字コード
    strText = ""
    sheetName = "Sheet1": '書き込むシート
    startCell = "A1": 'セル内の書き込み開始位置
    
'   読み込み
    Dim st As ADODB.Stream
    Set st = New ADODB.Stream
    With st
        .Type = adTypeText: 'テキストモード
        .Charset = chrCode: 'ファイルの文字コード
        .Open: '開始
        .LoadFromFile (fileName): 'ファイルオープン
        strText = .ReadText(adReadAll): '一括読み込み
        .Close: '閉じる
    End With
    Set st = Nothing

'   シートのクリア
    Worksheets(sheetName).Select
    Cells.ClearContents: 'シート全体をクリアしますので、一部のみを削除する場合は変更が必要
    Range(startCell).Select
    
'   抜き出しとセルへの書き込み
    Dim re As RegExp, mc As MatchCollection, m As Match
    Set re = New RegExp
    With re
        .Pattern = "<tr>(\n|.)+?href=""(.+?)"">(.+?)<br>(.+?)</a>(\n|.)+?<td>(.+?)</td>(\n|.)*?</tr>": 'パターンはもう少しスマートになるかもしれません
        .IgnoreCase = True: '大文字小文字は区別しない
        .Global = True: '全体を検索対象とする
        .MultiLine = False: '複数行として取り扱わない
        Set mc = .Execute(strText): '実行
        For Each m In mc
            Selection.Value = m.SubMatches(1)
            Selection.Offset(0, 1).Value = m.SubMatches(2)
            Selection.Offset(0, 2).Value = m.SubMatches(3)
            Selection.Offset(0, 3).Value = m.SubMatches(5)
            Selection.Offset(1, 0).Select: '次の行にカーソル移動
        Next m
        Set mc = Nothing
        Set re = Nothing
    End With
End Sub

http://msdn.microsoft.com/ja-jp/library/cc364272.aspx

http://msdn.microsoft.com/ja-jp/library/cc392403.aspx

id:holoholobird

早速のご回答、どうもありがとうございます。

With reのPatternの正規表現の部分について、どの記号がどういう意味を持つのかを、もう少し詳しく教えていただきたいです。

不勉強ながら申し訳ありませんが、どうかご指導のほど、よろしくお願いします。

2011/01/14 01:23:55
id:windofjuly No.2

うぃんど回答回数2625ベストアンサー獲得回数11492011/01/14 02:09:14

ポイント35pt

コメント欄に一度書いたのですが、少々崩れたので回答欄のほうに投稿しなおしました

 

いずれも大学の1講座になるようなものになりますので、最低限のさわりだけとさせていただきます

 

【1】マクロ記述の基本

マクロの記述方法については下記ページの「プログラムを書き始めるまでの準備」と「プログラミングと実行」がわかりやすいでしょう

http://brain.cc.kogakuin.ac.jp/~kanamaru/lecture/vba2003/2007_01...

Option Explicit をモジュールの一番上に書いておくと、変数の記述ミスなどを防げて便利なので書いておくほうがよいでしょう

ちなみに、私のコードの前にOption Explicitを書いた場合にはstartCell = の箇所で変数が宣言されていませんというエラーが出ますが、

これはdimで宣言するのを忘れているためなので、エラー発生場所の少し上にある宣言文にstartCell As Stringを加えて下記のようにすればエラーはなくなります

Dim fileName As String, chrCode As String, sheetName As String, strText As String, startCell As String

 

【2】正規表現パターンの説明

 

(1)正規表現を言葉で説明することは実に難しいため一部抜き出しでの説明とさせていただきます

.        改行以外の何か1文字を示します
.+       改行以外の文字が1つ以上つながっている状態を示します
\n       改行文字です
(\n|.)+  上記を組み合わることによって、改行を含む何かが1つ以上つながっている状態を示します
.*       何も無い。もしくは、改行以外の文字が1つ以上つながっている状態を示します
(\n|.)*  何も無い。もしくは、改行を含む何かが1つ以上つながっている状態を示します
?        最短マッチ

その他にもパターンで使えるメタ文字はいくつかありますので下記参照してみてください

http://msdn.microsoft.com/ja-jp/library/cc392437.aspx

 

(2)パターンの意味を頭から見ていきます

<tr>(\n|.)+href=  <tr>とhref=の間に色々な文字があるという状態を示します
上の表記では最初に見つかった<tr>から一番最後に見つかったhref=までを一まとめにしてしまう(最長マッチと言います)ため?を加えて一番最初に見つかったhref=で止まるようにします(最短マッチ)
<tr>(\n|.)+?href=
同様に</tr>までの構成パターンを順に記述していくと回答1のようになります

スマートになるかもしれませんという言葉は、対象となるhtmlのソースをいくつも眺めてパターンを確実につかむことが出来れば、もしかするともっと単純なパターンですむかもしれないということです

id:holoholobird

なるほどです。ご丁寧な回答、ありがとうございます。

参照設定でチェックを入れたのですが、「この名前は既にあるモジュール、プロジェクト、オブジェクト ライブラリで使われています。」と表示が出てしまいチェックできませんでした。

回答可能件数を5件までに変更しましたので、どうか返信をよろしくお願いします。

なお、ページはUNICODE表記になります。読み込みたいファイルは高々10件程度なので、こちらは手作業で何とかなると思います。

しかし各々のファイルのテーブル情報が多いので、それらの解析は自動で行いたいと考えました。

2011/01/14 02:34:41
id:SweetSmile1978 No.3

SweetSmile1978回答回数191ベストアンサー獲得回数292011/01/13 00:10:24

ポイント10pt

http://codezine.jp/article/detail/2149

http://www001.upp.so-net.ne.jp/isaku/tips/download.html

http://codezine.jp/article/detail/1655

このあたりが参考になるのかな。

指定URLからダウンロードしたhtml データを文字列として取得し、

正規表現で必要なデータを取り出すという流れですね。

個人的にwebから同様にデータ取ることしてますが、

Visual Basic コードはどうも好きになれないので、

C#で組んで、csv として出すようにしてます。

プログラム組むことになれていないと

長く辛い闘いになるかもしれません。

  • id:windofjuly
    うぃんど 2011/01/12 22:08:41
    htmlソース のキャラクタコードは何ですか?
  • id:zmhyzbiccx
    コメント荒らし キタ━━(━(━(-( ( (゚∀゚) ) )-)━)━) ━━ !!!!!
     
    これからもコメントを荒らすと堂々宣言!
    もーっ人力検索中毒なのか、このオッサン、って感じ
  • id:windofjuly
    うぃんど 2011/01/13 00:13:13
    キャラクタコードを自由に設定できるように作っておきました(初期値はUTF-8になっています)
    そちらの最終目標がわからないので1ファイルだけを読み込むように作ってありますが、
    コメントも極力入れておきましたので大量のファイルを連続して読み込むように改造したりするのもさほどは難しくないと思います
  • id:windofjuly
    うぃんど 2011/01/14 02:48:57
    >この名前は既にあるモジュール、プロジェクト、オブジェクト ライブラリで使われています。

    同じ名前でいくつものバージョンが存在しますので、例えば
    Microsoft AxtiveX Data Objects 6.0 Library に既にチェックが入っている状態で
    Microsoft AxtiveX Data Objects 2.5 Library にチェックを入れようとしたりすればエラーとなります
    その場合は新しいほうをそのまま使えば良いです
     
    >読み込みたいファイルは高々10件程度
     
    マクロや正規表現で悩むよりも手作業でコピーしたほうが早いですね
    (1)ブラウザでhtmlファイルを表示する
    (2)テーブルをドラッグして選択しておいてコピー
    (3)メモ帳を起動して、メモ帳に貼り付ける
    (4)メモ帳に貼り付けたものをすべて選択してコピー
    (5)Excelに貼り付ける
    ブラウザ上でコピーしたものを、いきなりExcelに貼り付けると1つのセル内にすべてが収まってしまうので、メモ帳に貼り付けるという事を行ってデータを自動的に置き換えさせています
  • id:holoholobird
    ご回答、どうもありがとうございます。どうやら設定はうまくいったみたいです。
    現在、windofjuly様に頂いたマクロを少々改変させてもらっています。
    教えていただいた正規構文を基に、以下のように改変を加えました。

    パターンはスマートにと書かれていたので、該当ソースのPatternの部分を<tr(\n|.)+?p>(.+?)</td> (\n|.)+?l=(.+?)">(.+?)a(\n|.)+?p>(.+?)</td>(\n|.)+?f="(.+?)"(\n|.)+?"(.+?)"(\n|.)+?c="(.+?)(\n|.)+?t="(.+?)"(\n|.)+?</td>(\n|.)+?p>(.+?)</td>(\n|.)+?p>(.+?)</td>(\n|.)+?p>(.+?)</td>\n</tr>\n

    For Eachの部分を
    Selection.Offset(0, 1).Value = m.SubMatches(2)
    Selection.Offset(0, 2).Value = m.SubMatches(4)
    Selection.Offset(0, 3).Value = m.SubMatches(5)
    Selection.Offset(0, 4).Value = m.SubMatches(7)
    Selection.Offset(0, 5).Value = m.SubMatches(9)
    Selection.Offset(0, 6).Value = m.SubMatches(11)

    と書きました。これを実行したら、「コンパイル エラー 修正候補:識別子 または 角かっこで囲む必要がある名前が含まれています。」と表示されました。
    調べてみたところ、文法エラーであるらしいとわかったのですが、実行した場合、最初に出てくるaのひとつ前の(.+?)の+マークがハイライトされます。が、なぜここがハイライトされるのか、原因がわかりません。
    原因がわかるのでしたら、どうか教えていただきたいです。
    よろしくお願いします。
  • id:windofjuly
    うぃんど 2011/01/14 16:30:10
    数秒パッとみただけで詳しく実証しているわけでもありませんが、取りあえず1点と余談2点
    (ハイライトされている部分は、エラーが具現化した位置を示しているだけであって、そこが真の原因とは限らないのでやっかいです
     回答2の Option Explicitを書くとエラーがでますが直す場所は違うといったものが実例です)

    (1)ダブルクォーテーション
    .Pattern = "xxxxx"
    ダブルクォーテーションで囲まれたxxxxxの部分でダブルクォーテーションを使いたい場合は""という具合に2個繋げて書く必要があります
    (回答文の中でも""と書いてある部分が2箇所あるはずです)

    (2)Windowsの改行コードとLinuxやMacなどで使われている改行コードは異なる
    こちらはエラーとは関係の無い話になりますが、末尾のほうで\nと書いている部分はパターンとしてマッチしない(適合しない)可能性があります
    エラーがでなくなってもマッチしない場合は(\n|.)+?と書くようにしてみてください

    (3)短いものから試しましょう
    慣れている人でも、いきなり長大な正規表現を書いたりできる人は稀です(どういう頭してるんだろうと思います)
     
    まずはパターンを <tr(\n|.)+?p>(.+?)</td> だけにしましょう
    (For Eachのほうも Selection.Offset(0, 1).Value = m.SubMatches(2) だけにしないといけないですね)
     
    うまくいったら <tr(\n|.)+?p>(.+?)</td>(\n|.)+?l=(.+?)">
    For Eachは Selection.Offset(0, 2).Value = m.SubMatches(4) を追加してみましょう
     
    うまくいったら、さらに・・・
     
    以上、検討を祈ります
  • id:holoholobird

    ありがとうございます。"を""に置換して、\nも同様に(\nl.)に置換して、短いほうから試してみることにします。
    実はあのあと頑張って、出力するところまではいったのですが、

    1:出力すべき予定の2つのURLのうち、正常に出力されたものは1つ。
    (もう片方はそもそも出力されなかった(ブランク)。ただし、すべてのurlに下の3のように、スラッシュ+半角数字+スラッシュとなる部分がある)

    2:日本語の単語が入るであろうと予想される場所(サイト名)の列がことごとく文字化けしている

    3:その他の、テーブルに記載されている当該サイトの更新日時(2004/12/03のように、半角数字。ただしスラッシュ含む)が
    書かれていた数字がことごとく「r」と表示されている

    それと、正規構文を扱っていて疑問が出てきたのですが、

    1:正規構文中の半角スペースはそのまま扱ってもよろしいのでしょうか。
    それとも、メタ文字を使って置換する必要があるのでしょうか。

    2:正規構文より出力される要素の文字列の長さに上限は存在しますか?

    3:なるべく原文ママで、異なる部分だけを置換するのが最も安定して正規構文をかけると愚考しました。
    pattern:""の""内は一行で書かなければいけないルールがあるのですか?それと、""内の文字列の長さに上限はあるのでしょうか。

    どうかご回答のほど、よろしくお願いします。
  • id:windofjuly
    うぃんど 2011/01/15 03:01:11
    【A】読み込めない、文字化け
     
    3つの問題の原因として「ファイルのキャラクタコードがUTF-8ではない」可能性が考えられますのでファイルのキャラクタコードを確認してください
    (HTMLソースファイル内にUTF-8と書かれていても、ホームページをファイルとして保存した時にShift-JISに変更されているなども十分ありえます)
     
    【B】正規表現

    B1:\sは半角スペースだけではなくタブやラインフィード、キャリッジリターンなどを含む場合に用います
     半角スペースを検出したい場合は半角スペースを使い、全角スペースを検出したい場合には全角スペースを使います
     
    B2:格納できる文字列の長さはメモリに依存します
     VBAの変数が格納できる文字列はバージョンにもよりますが、多くの場合は限界まで使えず、搭載されているメモリ量に依存するようです
     
    B3:原文ママで、異なる部分だけを検索する
     それがダメとは言えませんが・・・プログラムが長くなればなるほど、人間のほうの間違えやすさも増大しますので・・・

    複数行に分ける場合は、行の最後にアンダーバーをつけることで次の行が式の続きであるということを示します
    .Pattern = "xxxxx" _
    & "yyyyy" _
    & "zzzzz"   最後の行にはアンダーバーはつけません
    上記では"xxxxx"と"yyyyy"と"zzzzz"という3つの文字列を1つに&するという形になってますが、
    4つでも5つでも好きなだけ繋いでください(制限の確認をしたことはありませんが20行や30行くらいなら問題無いです)
     
    今週末の応答は非常に悪くなる可能性がありますが、何かあればコメントしておいてください
    (可能であればですがURLもしくはHTMLのソースを教えてもらえると、こちらとしても楽です)
     
    phpでの開発を行っている(あるいは学習中)であるならば、phpにてURLから直接データを読み取ってCSV形式のファイルとして出力させたほうが楽かもしれないですね
    dreamweaverCS5は使ってないので答えてないですけど、同一PC内に開発環境と実行テスト環境の両方を入れるのだとすると、いろいろと面倒なことになりかねなかったりしますので、xamppは別マシン上(出来ればLinux)にして、dreamweaverCS5からは同期を使ってファイルのアップロードを行い、本番同様の環境でテストするという手を使ったほうが良いですね
    (現時点ではまだ1件もオープンしていないようですが、仮にdreamweaverの保存先をapacheのドキュメントルートにしてしまうなどという回答が来ていたとすれば、その人の回答はあまりあてにはできないものですから気をつけましょう)
  • id:holoholobird
    統計学のテスト期間中で返答が遅れてしまい、大変申し訳ありません。

    実行してみたところ、無事に抽出できました。
    これからは、正規表現をマスターすべく、他のサイトなども巡って、データの抽出の練習を学びたいと思います。

    本当に勉強になりました。
    ここまで来れたのは、windofjulyさんのご丁寧な回答のおかげです。

    また、機会があればよろしくおねがいします。
    ありがとうございました。

この質問への反応(ブックマークコメント)

トラックバック

  • 人力検索へのフォロー http://q.hatena.ne.jp/1294836439   VBScriptの正規表現はあまり複雑なものを一度に処理しきれないので、ちょっと長ったらしいものになってしまいました また、複数のTABLEが
「あの人に答えてほしい」「この質問はあの人が答えられそう」というときに、回答リクエストを送ってみてましょう。

これ以上回答リクエストを送信することはできません。制限について

絞り込み :
はてなココの「ともだち」を表示します。
回答リクエストを送信したユーザーはいません