複数のXMLファイルを、Excelで1シートにまとめる方法を紹介します。
XMLファイルには、Redmine公式サイトのチケットを使用しました。
ExcelのXML機能は、なかなか凄いですよ！ 才才－!!w(ﾟoﾟ*)w
EIICHI2021/01/17 記
Microsoft 365のExcelで記事を改訂しました。
XML要素と列の関係付け
Excelには、任意の列にXML要素を関係付けてインポートする機能があります。
まずは、Excelを起動して、XMLファイルのどれか一つを開きます。
「[XMLソース] 作業ウィンドウを使用する」を選択して[OK]をクリックします。
[OK]をクリックします。
XML要素のツリーが表示されます。
ツリーから抽出したいXML要素をドラッグして、セル上にドロップしていきます。
ドロップした名前は、自由に変更できます。
作業が終わったら、一度保存しておきましょう。
上のスクリーンショットでは、次のように設定しました。
| 列番号 | 列名 | XML要素 | 補足 |
|---|---|---|---|
| B | id | issue | id |
| C | project_id | project | id |
| D | tracker_id | tracker | id |
| E | status_id | status | id |
| F | priority_id | priority | id |
| G | author_id | author | id |
| H | subject | subject | |
| I | description | description | |
| J | start_date | start_date | |
| K | due_date | due_date | |
| L | done_ratio | done_ratio | |
| M | estimated_hours | estimated_hours | |
| N | created_on | created_on | |
| O | updated_on | update_on | |
| P | closed_on | closed_on | |
| Q | category_id | category | id |
| R | fixed_version_id | fixed_version | id |
| S | assigned_to_id | assigned_to | id |
XMLの対応付けのプロパティ
XML要素のセルの上で右クリックします。
メニューの[XML]から[XMLの対応付けのプロパティ]をクリックします。
ダイアログが表示されます。レイアウトが自動調整されるのがイヤな場合は「データのレイアウト」のチェックを外しておきます。また、次のインポートで前のデータが上書きされないよう、インポート時の処理を「追加」に変更しておきます。
変更が終わったら[OK]をクリックして閉じます。
XMLファイルのインポート
もう一度、XML要素のセルの上で右クリックします。
次は、メニューの[XML]から[インポート]をクリックします。
ダイアログが表示されます。
インポートしたいXMLファイルを選択して[インポート]をクリックします。
複数のファイルをまとめて指定できます。
シート上にXMLファイルがまとめてインポートされました。
テーブルから標準の範囲に変換
以上で、複数のXMLファイルを1シートにまとめることができました。
インポートしたデータには、テーブル(Excelの機能)が設定されています。
このままでもよいのですが、解除する場合は次のようにします。
どこでもいいのでXML要素のセルをクリックします。
メニューの[テーブルツール – デザイン]から[範囲に変換]をクリックします。
メッセージが表示されるので[はい]ボタンをクリックします。
テーブルが解除されて、標準の範囲になりました。
最後までご覧いただきありがとうございます。
では、また。
コメント
コメント一覧 （8件）
ご返信いただき、ありがとうございます。
先のコメントは見当違いでしたね、申し訳ありません。
私も似たような現象を確認しました。
ヘッダの要素を配置するとセルには枠だけ表示され、インポートを実施すると値が展開されなかったです。（画像を添付しています）
いろいろ試したところ、「トップのフォルダ」をシートに配置すると、全要素が行方向に展開された状態でインポートできました。(ヘッダは同じ値の繰り返しになります。)
後で不要な列を削除する必要がありますが、一度試していただけないでしょうか？（こちらも画像を添付しています）
EIICHI様
検証まで行って頂き、本当に有難うございます。
ここまで教えて頂いて心苦しいのですが、もう１点教えてください。
画像２のようにすべてのフォルダを配置しようとしますが、
全ての項目が枠表示になり、画像の様に青色書式になりません。
試しに、再度新しいエクセルを開いて、③「ReqDtls」（内容）を配置しようとしましたが、こちらも枠になってしまいました。
EIICHI様の画像２のようになれば感激です。
何とかお知恵を貸していただきたく宜しくお願い致します。
ご確認いただき、ありがとうございます。
なかなか手強い問題のようです。
最初のところで「XMLテーブルとして開く」を選択して実行すると、XMLソースの表示を飛ばして、画面2の状態になります。
Excel内部では同様にXMLソースの解析をしているようなので、結果は同じかもしれませんが、この方法も試していただけないでしょうか？
EIICHI様
できました！
教えて頂いた方法でうまくいかず、何度もエクセル作成→対応付け→削除を繰り返しました。
エクセル→データ→データの取得→XMLファイルからも試しました。
上記の方法で対応付けを行うと、③「ReqDtls」（内容）が突然、青色書式に戻り、これより左側のセルに②「Req」（ヘッダ）を配置すると枠になるので、右側に一旦配置し、行を切り取り→左側に挿入で全てが青色書式になるように配置しました。
これでインポートを行うと５つのXMLファイル全てを取り込めました。
無料かつ相談窓口でもないと思いますが、親切に教えて頂き本当に有難う御座いました。
今後もこちらの記事を参考にさせて頂きたいと思います。
林様
良かったです！！
私も、内容より右側に配置したヘッダが青色になることを、確認できました。これには、教えていただくまで全く気がつきませんでした！！
ご教示いただきありがとうございます。
またご覧いただける記事が書けるよう頑張りますので、よろしくお願いいたします。(^^)
初心者の私にも分かりやすく助かりました。有難うございます。
この方法で作成しましたが、私のやり方が悪く、エラーがでてしまいます。
他に同様の方法を書いている方がいらっしゃらず、ここにコメントさせていただくことご容赦ください。
XMLファイルを５つ取り込みますが、１つしか取り込まずエラーになります。（なぜエラーかもわからない状況です）
試しに、インポートできたファイルのコピーをその５つの中にいれて取り込みますが、やはりエラーになります。
５つのXMLファイルは同じ項目で、各々ファイル自体のエラーは考えにくいです。
なにか思い当たる理由または解決方法をご存じないでしょうか。
はじめまして、管理人のEIICHIと申します。
せっかく記事を参考にしていただいたのに、申し訳ありません。
早速ですが、次の操作を試していただけないでしょうか？
記事のメニュー１から２まで記載の通り進めていただき、３のインポートを１ファイルずつ指定して繰り返します。
Excel側がスキーマ(構造)が同じであると判断したファイルは、メニュー１で作成されたスキーマでインポートされると思います。
逆にスキーマが違うと判断したファイルは、抽出する要素が無いことになり、インポートされないと思います。
Excel側がスキーマをどう判断しているかは、Excelが作成するXMLのソースツリーからうかがい知ることができます。
ハジかれたファイルを別ブックにインポートして、作成されたXMLのソースツリーと比較すると、相違点が見つかるかもしれません。
特に、要素名の先頭に「ns1:～」などの文字が付いていないでしょうか？
Excelが、要素名が同じでも名前空間(出処)が違うと判断している可能性があります。
本来もっと詳しく説明すべきところ、言葉が足りず申し訳ありません。
いくつか私も参考になったURLを貼らせていただきます。ご参考になれば幸いです。
特に、公式の図解が分かりやすかったです。
※ すでにご存じでしたらご容赦ください。
https://support.microsoft.com/ja-jp/office/excel-%E3%81%AE-xml-%E3%81%AE%E6%A6%82%E8%A6%81-f11faa7e-63ae-4166-b3ac-c9e9752a7d80
http://www.xmlmaster.org/article/d01/c11/#:~:text=XML%E5%90%8D%E5%89%8D%E7%A9%BA%E9%96%93%E3%81%A8%E3%81%AF,%E3%81%99%E3%82%8B%E3%81%9F%E3%82%81%E3%81%AE%E4%BB%95%E7%B5%84%E3%81%BF%E3%81%A7%E3%81%99%E3%80%82
EIICHI様 ご回答頂きありがとうございます。
もう１度試して理由が分かった気がします。
私が作成したエクセルは、上の記事の「シート上にXMLファイルがまとめてインポートされました」のスクリーンショットのように、青色書式が設定されず、セルを枠で囲んであるだけで、要素名も表示されていませんでした。
元のXMLを見ると、①「Bde_Req」（要素無し）、②「Req」（ヘッダ）、③「ReqDtls」（内容）の３段階で作成されており、③の要素のみを選択して作成すると、青色書式で、５つのXML全てを一括で読み込むことができました。（記事の通りです）
②だけで作成するとやはりエラーになり、青色書式もなく、枠のみです（要素名は枠の左や上に選択すると表示されます）
私の目標は、②も③も同じシートで横一列に読み込みたいのですが、この仕組みでは不可でしょうか。
もしご存じでしたら、教えてください。
立て続けの質問で申し訳ありません。