Twitter の全ツイート履歴データを Excel で見やすく抽出する方法:CData Excel Add-in for JSON

こんにちは。CData Software Japan リードエンジニアの杉本です。

Twitterの機能で、全ツイート履歴をダウンロードする方法があるってご存知ですか?

help.twitter.com

f:id:sugimomoto:20210106180252p:plain

通常、Twitter APIで取得する場合だと色々と制限があるんですが、この機能を使うことで自分のツイートを一括で取得して、分析したり活用したりすることが可能です。

実はこのデータを活用して、以下の記事とかも作成していました。

www.cdatablog.jp

ただですね、フォーマットがちょっと面倒で、ZIPファイルを展開すると、以下のようなJSファイルが並んでいて、

f:id:sugimomoto:20210106180307p:plain

APIで取得できるJSONオブジェクトのままなんですよね。

f:id:sugimomoto:20210106180313p:plain

おそらく何も知らずにダウンロードした人は「これはどうすればいいんじゃ!」となること間違いなしです。

そこで今回は以下のような感じでこの全ツイート履歴のデータをExcelで見やすく、かつ特定のキーワードで抽出したりする方法を紹介します。

f:id:sugimomoto:20210106180430p:plain

目次

JSONデータを少し調整する

まず、対象のファイルの「window.YTD.tweet.part0 = 」の部分を削除します。

<span class="synError">window</span>.<span class="synError">YTD</span>.<span class="synError">tweet</span>.<span class="synError">part0</span> = <span class="synSpecial">[</span> <span class="synSpecial">{</span>
  "<span class="synStatement">tweet</span>" : <span class="synSpecial">{</span>
    "<span class="synStatement">retweeted</span>" : <span class="synConstant">false</span>,
    "<span class="synStatement">source</span>" : <span class="synConstant">
      <a href="http://twitter.com/download/android" rel="nofollow">Twitter for Android</a>
    </span>,
    "<span class="synStatement">entities</span>" : <span class="synSpecial">{</span>
      "<span class="synStatement">hashtags</span>" : <span class="synSpecial">[</span> <span class="synSpecial">]</span>,
      "<span class="synStatement">symbols</span>" : <span class="synSpecial">[</span> <span class="synSpecial">]</span>,
      "<span class="synStatement">user_mentions</span>" : <span class="synSpecial">[</span> <span class="synSpecial">]</span>,
      "<span class="synStatement">urls</span>" : <span class="synSpecial">[</span> <span class="synSpecial">]</span>
    <span class="synSpecial">}</span>,
    "<span class="synStatement">display_text_range</span>" : <span class="synSpecial">[</span> "<span class="synConstant">0</span>", "<span class="synConstant">82</span>" <span class="synSpecial">]</span>,
    "<span class="synStatement">favorite_count</span>" : "<span class="synConstant">0</span>",
    "<span class="synStatement">id_str</span>" : "<span class="synConstant">99059949191176192</span>",

その後「tweet.js」ファイルを「tweet.json」に変更してください。

f:id:sugimomoto:20210106180320p:plain

以下のようになればOKです。

f:id:sugimomoto:20210106180325p:plain

CData Excel Add-in for JSON で接続する

続いてこのJSONデータを見やすくするために、「CData Excel Add-in for JSON」をインストールします。

以下のURLからトライアルが入手可能です。

https://www.cdata.com/jp/drivers/json/excel/

f:id:sugimomoto:20210106180331p:plain

セットアップすると、ExcelにCDataタブが追加されるので「データの取得」→「取得元:JSON」を選択してください。

f:id:sugimomoto:20210106180338p:plain

接続設定の画面が立ち上がるので、対象の「tweet.json」ファイルをURIに指定しましょう。

f:id:sugimomoto:20210106180345p:plain

あとは「json」というテーブルを選択すればOKです。

f:id:sugimomoto:20210106180350p:plain

とりあえずOKをクリックすれば、100件ツイートデータがExcel上にロードできます。

f:id:sugimomoto:20210106180354p:plain

Likeやリツイートのカウントなども取得できるので、便利です。

f:id:sugimomoto:20210106180401p:plain

ちなみに、最大行数のチェック(LIMIT 100)を外せば、全件ロードできます。

f:id:sugimomoto:20210106180409p:plain

また、カラムのチェックボックスをON/OFFすることで、必要なカラムだけ表示することも可能です。

f:id:sugimomoto:20210106180415p:plain

特定キーワードを含むツイートだけ抽出する

もし、特定のキーワードだけ含むツイートを抽出したい場合は、以下のように「tweet.full_text」の項目に対して「次の値を含む:API」といった形で設定することで対応できます。

f:id:sugimomoto:20210106180423p:plain

SQLでも書けるようになっていて、以下のような指定になります。

<span class="synStatement">SELECT</span> * <span class="synSpecial">FROM</span> [CData].[JSON].[json] <span class="synSpecial">WHERE</span> [CData].[JSON].[json].[tweet.full_text] <span class="synStatement">LIKE</span> <span class="synSpecial">'</span><span class="synConstant">%API%</span><span class="synSpecial">'</span>

こんな感じでロードできました。

f:id:sugimomoto:20210106180430p:plain

是非お試しあれ

トライアル・お問い合わせ

関連コンテンツ