MT形式でエクスポートデータしたブログをExcelで分析する-正規表現での加工編-

はじめに

相当ニッチだという自覚はあるのですが。

JBS Tech Blogを開設して早3ヶ月となるのですが、今後を見据えて運用ルールが曖昧だった部分の見直しを進めています。

その中で、見出しの使い方がバラバラになっているという問題がありました。記事を全て目次確認するのは大変なので、なんとかhtmlのソースを使って分析できないか試行錯誤したので記事にしてみました。

おことわり

それなりの形まで持って行けたのですが、完璧ではなく、一部データが欠けるといった問題が残っています。参考にして頂く際はその点をご注意下さい。

※ 6/1追記:少し改善して、ほぼ理想のデータになったと思います。

エクスポートしたデータを確認する

はてなブログのエクスポートデータはMT形式となっています。ブログ間のデータ移行では標準的なので取り回しはいいのですが、Excelに入れて分析出来る形にはなっていません。

これをなんとかExcelで分析可能な形に出来るものでしょうか…?

理想とする結果を確認しておく

欲しいデータの理想はこのような形式です。

幸い、素のデータにも規則性はあるので、ここでは正規表現を使ってなんとかしてみたいと思います

正規表現エディタとしてはVisual Studio Codeを使います。

正規表現でMT形式のデータを加工する

一部のデータを削る、結合する(6/1追記)

EXTENDED BODYを結合する

EXTENDED BODYがあるとこの後の処理が失敗するので、BODYと結合しておきます。

  • 検索ワード:^-----$\nEXTENDED BODY:
  • 置換ワード:(空白)
IMAGEを削る

サムネイルの画像パスを表すIMAGEという項目があるのですが、サムネイルがない記事があった場合にデータがずれてしまいます。

ダミーのパスを入れてもいいのですが、今回は不要なので削ってしまうことにしました。

  • 検索ワード:IMAGE:.*$
  • 置換ワード:(空白)

データ区切りの位置を明示しておく

データの区切りの文字を明示的にしておきたいと思います。

BODYの前後の-----を置換しておきます。今回は他で使われてなさそうな;-;BODYの区切り;-;とします。

  • 検索ワード:^-----$
  • 置換ワード:;-;BODYの区切り;-;

また、データの終わり(AUTHORの前)の--------``も置換しておきます。今回は他で使われてなさそうな;-;データの区切り;-;```とします。

  • 検索ワード:^--------$
  • 置換ワード:;-;データの区切り;-;

改行だけの行を削除

改行だけの行を消しておきます。

  • 検索ワード:^\n
  • 置換ワード:(空白)

html内の改行を削除

今回は本文をexcelの一つのセルに入れる必要があります。htmlタグの後の改行は全て削除します。ただし、;-;BODYの区切り;-;は独立した行として残します。

  • 検索ワード:>\n(?!;-;BODYの区切り;-;$)
  • 置換ワード:>

ただ、これでも記事内にソースコードがあるケースなどでかなり改行が残ります。

これをBODYで始まること、BODYの終わりが;-;BODYの区切り;-;であることを利用して置換します。

  • 検索ワード:(^BODY:\n.+)(\n)(?!;-;BODYの区切り;-;$)
  • 置換ワード:$1

これを、置換が実行されなくなるまで連打して繰り返します。(一回で1行ずつ削っています)

CATEGORY を一行にまとめる

MT形式だとCATEGORY を設定した数だけデータが改行されて入ります。

このままだとデータがずれるので、カテゴリデータは1行にまとめてしまいます。

まず、CATEGORY の文字列と改行コードを消します。ただ、後で記事ごとのCATEGORY を識別出来る用に区切り文字を入れておきます。なるべく本文で使われてないものがよいので、今回はまず利用されないであろう、;-;CATEGORY;-;に一度置き換えてみます。

  • 検索ワード:\nCATEGORY:
  • 置換ワード:;-;CATEGORY;-;

すると、DATEとCATEGORY の間の改行まで消えてしまいます。が、日付のデータであることと、区切り文字の;-;CATEGORY;-;がある事を利用して置換します。

  • 検索ワード:(^DATE: [0-9/ :]*)(;-;CATEGORY;-;)
  • 置換ワード:$1\nCATEGORY:

このままだとカテゴリの仕切り文字が見にくいので、直しておきます。

  • 検索ワード:;-;CATEGORY;-;
  • 置換ワード:;

こうなりました。

列名に該当する文字列を削除する

DATEやCATEGORY などの文字列は後で列名として設定するので、本文からは消してしまいます。

個別にやるのは面倒なので一気にやってしまいます。

  • 検索ワード:^[A-Z ]+:
  • 置換ワード:(空白)

ただ、BODYだけ改行の持ち方など違うので分けてやります。

  • 検索ワード:BODY:\n
  • 置換ワード:(空白)

改行を全てタブに変置換する

ここまでやったら、一度全ての改行を全てタブに変換します。(必要な改行はこの後戻します)

  • 検索ワード:\n
  • 置換ワード:\t

置換すると、すべてのデータが1行になってしまいます。

BODYの区切り文字を使って行を区切る

ここで、;-;データの区切り;-;を使って必要な改行を戻します。

  • 検索ワード:;-;データの区切り;-;\t
  • 置換ワード:\n

これでだいぶ理想の形になりました。

BODY内の区切り文字を使ってhtml本文を""で囲む

最後に、;-;BODYの区切り;-;を使ってBODY部分を""で囲い、一つのセルに入るようにします。

  • 検索ワード:;-;BODYの区切り;-;
  • 置換ワード:"

Excelで読み込む

Excelで読み込むと、分析出来そうな形のデータを取得出来ました。

おわりに

このあとは実際に分析をしていきます。が、そこから先はPower Queryの話になるので、別の記事で書きたいと思います。