Excelの表とJSONの関係…キー列は重要

by manamana 31. 7月 2013 17:00

先日 Exelの複数の表からJSONデータを作成する機能を Excel PowerShell Tool に組み込みました。
それはなかなか都合が良くて、大量の配信データを手軽に用意することが可能になりました。
それを応用してとあるアプリを組んでいるのですが、キー列を考慮するともっと便利になると気付かされました…  

例えば、以下のような表があるとします。

 

先日の機能で JSONデータを作ると次のようになります。

[
{"ID":1,"MemberOf":["C"],"Description":"初心者"},
{"ID":2,"MemberOf":["C", "B"],"Description":"中堅"},
{"ID":3,"MemberOf":["C", "B", "A"],"Description":"ベテラン"}
]

これは、表を一行単位でオブジェクト化し、表は「行の配列」として処理しているわで、配信データには都合が良い形です。 
しかし、この表の ID列は明らかにキーになっています。
このような場合、ID列の要素をキーとした JSONデータを作成すれば、キーによる検索が簡単になります。
具体的には次のようなものです。

{
"1": {"MemberOf":["C"],"Description":"初心者"},
"2": {"MemberOf":["C", "B"],"Description":"中堅"},
"3": {"MemberOf":["C", "B", "A"],"Description":"ベテラン"}
}

キー列の要素を使えば、表全体を一つのオブジェクトにできるわけです。
これなら、 $json."3" のような記述で簡単に三行目のデータにアクセスできますね。
※前者の場合、foreach で回して ID をチェック…とか、やってはダメな手法に陥りがちです

データを階層化しておけば多くの検索が不要になりますが、データのためにメモリーを無駄遣いします。
プログラムで簡単にリンクができるならその方が便利なことは多々あるので、使い分けが重要です。

ということで、JSONデータのの作成時にキー列を指定する事を可能にしました。
その他、任意の列名の最後に ! を加える事で、その列のデータ生成をスキップするとかも加えました。
明らかに不要な列を取り込むのは無駄なので意外に役に立ちます。

以上のような改良を加え、Excel PowerShell Tool Ver1.1 を近々公開予定です。 

Tags:

Google Maps Engine Lite のジオコーディングが便利だった

by manamana 26. 7月 2013 12:00

「マップ上に印を付けたい!」場面は多いと思います。

Google Mapのマイプレイス-マイマップ機能を使えば、
地図上にマーカーやポリゴンで印を付けたりして、個人用の地図を作ることが出来ました。
しかし、マーカーの配置は手動であり結構な手間だったりします。

Google は企業向けに Google Maps Engine を提供していて、強力な機能が使えます。
しかし、これは有料サービスであり個人が手軽に使えるものではありません。

そこで Googleは、Google Maps Engine Lite を提供してくれました。
まだβ版ですが、先日チュートリアル等も日本語化されたので、ここに紹介します。

 

操作等はチュートリアルに任せるとして、個人的なツボは 「CSV等で記録された住所一覧からマーカーを発生する」
いわゆるジオコーディングの自動化がサポートされていることです。

例えば、次のような野球場の住所録があるとします。

  

これを Excel PowerShell Tool 等で CSV ファイルにします。

 

Export-CSVコマンドレットは、既定の Encoding に ASCIIを使うので
日本語が入るテーブルを変換する場合には文字化けが発生します。
また、既定では先頭行に "#TYPE" に続いて、オブジェクトの完全修飾名が付加されますが、
Google のサービスは CSV の #行をコメントと扱わないので困ったことになります。
Export-CSV を使う時は、さり気なく以下のようにしましょう(笑)

PS:> XXXXX(任意のオブジェクト) | Export-CSV "ファイル名" -Encoding UTF8 -NoTypeInformation

このようにして得られた "野球場.csv" を示します。

 

 これを Maps Engine Lite に読み込みます。
レイヤーのインポートを指定して、ファイルを選択します。

 

ジオコーディングの対象、今回は”住所”を指定します。

 

 表示用の項目、今回は”名称”を指定します。 ※名称に "" が付いているのは、多分バグです。 

 

 たったこれだけの手順で、住所録からマーカー付きの地図が出来上がってしまいます!

 

あとはマーカーを変更したり、データをブラウザ上で編集してやれば自分だけの地図が完成です。
完成した地図は当然のように共有することが可能です。

 

住所から緯度経度を得るジオコーディングは有料サービスが多いのですが、
無料でここまで出来てしまうとは…地図屋さんも大変です。
 

Tags:

その他の IT 系

Windowsストアアプリが起動しない

by manamana 20. 7月 2013 17:30

普段から Server 2012 を使っているのですが、Windowsストアアプリも利用します。

 

ストアに更新が一件あるようなので、いつものようにクリックしてやると…スタート画面に戻ります。
他のストアアプリ…SkyDriveやらWikipedia等、尽く起動しません! これはまずい。

思い当たるフシはあります。 謎のシステム停止、ディスプレイドライバーの更新、アカウントの追加…
何かがきっかけになってストアアプリに悪影響があったのでしょう。

とりあえず再起動→変化なし。真面目に調べないとダメみたいです。

イベントビューアを見ると、派手にエラーが出ています。
該当する部分を抜き出してみると、ストアに関しては以下の4つがセットになっているみたいです。

  1. アプリ winstore_cw5n1h2txyewy!Windows.Store のライセンス認証がエラーで失敗しました:
    リモート プロシージャ コールに失敗しました。。
    詳しくは、Microsoft-Windows-TWinUI/Operational ログをご覧ください。
  2. アプリ winstore_cw5n1h2txyewy!Windows.Store は、割り当てられた時間内に起動しませんでした
  3. 障害が発生しているアプリケーション名: WWAHost.exe、バージョン: 6.2.9200.16420、タイム スタンプ: 0x505a9152
    障害が発生しているモジュール名: KERNELBASE.dll、バージョン: 6.2.9200.16451、タイム スタンプ: 0x50988aa6
    例外コード: 0x00000004
  4. Web モジュール 1::Windowing::Bind が 0x0x80070005 で失敗したため、アプリ ホスティング プロセスは開始できませんでした。
1.の情報に従って、Microsoft-Windows-TWいnUI/Operational ログを見たところ、更に情報が得られます。
  1. エラー リモート プロシージャ コールに失敗しました。
    によりアプリ winstore_cw5n1h2txyewy!Windows.Store を Windows.Launch コントラクトに対してライセンス認証できませんでした。
  2. エラー リモート プロシージャ コールに失敗しました。
    によりアプリ winstore_cw5n1h2txyewy!Windows.Store を Windows.Protocol コントラクトに対してライセンス認証できませんでした。
  3. アプリ microsoft.microsoftskydrive_8wekyb3d8bbwe!Microsoft.MicrosoftSkyDrive が
    Windows.Launch コントラクトに対して登録されていないか、インストールされていません。
メッセージだけから判断すると、RPCが正しく動作しないのが原因でコントラクターがまともに機能していないようです。
この辺は未経験なので何とも言えません。
 
これらの情報からグーグル先生に聞いたところ、特定のセキュリティソフトが原因で似た症状が出たりしているようです。
しかし、私の環境には当てはまりません。これは時間がかかりそうだ~
 
ふと他のアカウントでログインしたところ、普通にストアアプリが動くことに気づきました!
そこで、必要なデータをバックアップした後、自分のプロファイルを削除してやり、
改めてログインしてやることで無事にストアプリが起動する環境に戻りました。 
もちろん、デスクトップ環境がリセットされるので完全にもとに戻すのは手間がかかりますが、
再インストールよりはずっとマシです。
 
たぶんこのトラブルの原因は簡単なもので、特定のファイルが壊れてしまった程度だと予想します。
すべてのストアアプリを道連れにする凶悪なものなので、そのうち修正されることでしょう。
とりあえず再インストールは回避したので良しとします。
 
 
 
 
2013/12/15 追記
hoge さんからプロファイル削除することなく解決する情報を頂きました!!
詳しくはコメント欄をご覧下さい。 

Tags:

Server

Excel の複数テーブルから JSONデータを作る

by manamana 12. 7月 2013 18:00

このところ、JSONを扱うことが多くなってきました。
JavaScript を操る人にはお馴染みの JSON ですが、データ作成はどうしているのでしょうか?

・CSVからの変換で間に合っている
・Web上のツールで基本を作って後はText Editor
・システム化しているからあまり関心がない…

ちょっとしたデータなら Text Editor でも間に合いますが、大規模なものだと大変だと思います。
ExcelやCSVからの変換ツールも多数存在しますが、一つのテーブルを JSON にするだけのものが殆どです。
これでは CSV を利用しているのと大差ありません。

私が考えるに、JSON の最大の特徴はレコードとテーブルをオブジェクトとして扱い、それらを階層化できることです。
CSVのような単純な構造を JSON にするだけでは勿体無いないのです。

そこで以下のルールに従い、Excel の「複数テーブル」から一つのJSONデータを作る新API GetJsonFromTable()
Excel PowerShell Tool に追加しました。

[テーブル]

テーブルの各一行(=レコード)がオブジェクト単位である
テーブル全体も一つのオブジェクトで、オブジェクトの配列である
ただし、一行だけのテーブルは配列として扱わない

テーブルの列名がキー
ただし、一列だけのテーブルはキーの存在は無意味なので値のみとする

[セル]

改行コードは "\n" に置換
数値の場合は Value2 を採用 

・セルが文字列書式

自動的に "" を付加

・セルが標準書式

TRUEは true、FALSEは false、空セルは null として扱う
数値として判定できるものは数値、
"#"で始まる文字列はテーブルリンク
"["で始まる文字列は配列として、加工しない
それ以外は文字列として自動的に "" を付加

・それ以外の書式

Range.Textで評価(※幅が足りない場合は ##### 等で評価されるので注意)
数値として判定できるものは数値、
それ以外は文字列として自動的に "" を付加

[テーブルリンク]

#テーブル名 [範囲]
範囲の書式
数値指定された一行
数値:数値指定された複数行
列名=値テーブル名の列で、値により見つかる最初の一行

 

最大の特徴は、"テーブルリンク" と呼ぶ、テーブル間の結合です。 #テーブル名 [範囲]で指定します。
これにより、複数のテーブルをまとめて1つのJSONにまとめることが可能になっています。
リンクするテーブとリンクされるテーブルは階層構造をもち、プロパティによるアクセスが可能です。


例えば、メンバーを管理するテーブルを用意しましょう。

 

Security に関しては項目が多数ありそうなので、別のテーブル"Security"で管理したいところです。

 

Security Level が高い人の MemverOf は沢山ありそうなので、またまた別のテーブルで管理したいところです。

 

これくらい用意すれば、将来の性能インフレにも耐えられそうです(笑)

では、これらの3つのテーブルをリンクさせてみましょう。"メンバー" と "Security" にテーブルリンクを記入します。

 

 

このシートからJSONを得るためのスクリプトを示します。

#@PSTOOL

$addin = $_Excel.ComAddins.Item("ExPSTool").Object

# "メンバー"テーブルを指定して、JSON文字列を取得
$json = $addin.GetJsonFromTable("メンバー")

# $json は単純なStringなのでそのままだと一つのCellに表示してしまう
# 改行コードで Split してやればOK
$json.split("`n")

…これだけですww

得られる JSONのデータです。実際には改行コードは入っていますが、インデントは手抜きしています。
整形されたJSONを得たい場合には JSONLint がお勧めです。

 

[
    {
        "ID": 1,
        "Name": "伊藤",
        "Tel": "000-111",
        "GropuName": "営業",
        "Security": {
            "Level": 1,
            "MemberOf": "あいうえお",
            "Description": "初心者"
        }
    },
    {
        "ID": 2,
        "Name": "加藤",
        "Tel": "000-112",
        "GropuName": "開発",
        "Security": [
            {
                "Level": 2,
                "MemberOf": [
                    "C",
                    "B"
                ],
                "Description": "中堅"
            },
            {
                "Level": "ABC",
                "MemberOf": [
                    "C",
                    "B",
                    "A"
                ],
                "Description": "ベテラン"
            }
        ]
    },
    {
        "ID": 3,
        "Name": "鈴木",
        "Tel": "000-114",
        "GropuName": "マーケティング",
        "Security": {
            "Level": "ABC",
            "MemberOf": [
                "C",
                "B",
                "A"
            ],
            "Description": "ベテラン"
        }
    },
    {
        "ID": 4,
        "Name": "田中",
        "Tel": "000-119",
        "GropuName": "経理",
        "Security": {
            "Level": "ABC",
            "MemberOf": [
                "C",
                "B",
                "A"
            ],
            "Description": "ベテラン"
        }
    },
    {
        "ID": 5,
        "Name": "佐藤",
        "Tel": "000-222",
        "GropuName": "営業",
        "Security": {
            "Level": 4,
            "MemberOf": [
                "C",
                "B",
                "A",
                "S",
                "SS",
                "SSS",
                "SSSS",
                "SSSSS"
            ],
            "Description": "神"
        }
    }
]

 

複数のテーブルをまとめることができると、とても便利です。
これは RDB と同一の思想で、Excel の応用範囲が広がります。
もちろん本格的な SQL が使えるわけでなないですが、 多くの場合これくらいの機能で十分ではないでしょうか?
Excel + Json + PowerShell で大抵のデータを扱える気がして来ました。

GetJsonFromTable() を追加したことで、PS Tool は Version 1.1 になります。
それも、まだ私が JSON の経験不足な点もあるのでβ版としてテスト公開します。
Ver 1.1 からは PowerShell 3.0 が必須になります。
Win7 の環境では Windows Update のオプション扱いなので注意してください。
※もしかすると、もう必須扱いか? 

Windows 7 SP1 および Windows Server 2008 R2 SP1 用の Windows Management Framework 3.0 について

日頃 JSON のデータ編集や管理で苦労されている方、是非使ってみてください。
そしてご意見等お願いします。

 

ダウンロード↓↓↓

ExPSTool-1-1-beta.zip (470.35 kb)

Tags:

PowerShell

Yahoo! ショッピングWeb APIで商品検索

by manamana 3. 7月 2013 09:00

Yahoo! の Web API 一覧を見ていたらショッピングWeb APIがありました。

Yahoo! JAPANが提供するショッピングWebAPI

以前、VSTOで Amazonや楽天のサービスを使い画像を取得するアドインを組んだことがありますが、
それと同様のことが Excel PowerShell Tool でも簡単に構築できそうです。 
…ということで作ってみました。 サンプルとして机上にあった商品を検索してみました。

 

簡単に出来るつもりでしたが、結構ハマりました。
Invoke-RestMethod で XMLデータを取得すると何故か文字化けをするのです。
Encode には UTF-8 を利用しているし、これといった問題は見つかりません。
念の為に UTF-8 -> UTF-16(=Unicode) への手動変換とかしてみましたが効果なし。
真面目に調べると時間がかかるので、JSONにて処理をしたところ普通に動きました。 
※ただし、ドキュメントとは微妙にエントリー位置が異なったりする
Invoke-RestMethod + XML に関しては後日検証しなければ… 

MSはWebサービスに対してSOAP形式を推していましたが、世の中はREST+JSONが主流です。
REST+JSONは必要な機能を備えているし、テキストベースでも開発できますからね。
ExcelでJSONデータの編集をするツールでも作ってみるかな~

Tags:

PowerShell


スポンサーリンク

Calendar

<<  3月 2024  >>
252627282912
3456789
10111213141516
17181920212223
24252627282930
31123456

View posts in large calendar

Month List

Twitter