複数のXMLファイルをExcelで1シートにまとめる方法

複数のXMLファイルを、Excelで1シートにまとめる方法を紹介します。

XMLファイルには、Redmine公式サイトのチケットを使用しました。
ExcelのXML機能は、なかなか凄いですよ! 才才-!!w(゚o゚*)w

EIICHI

2021/01/17 記
Microsoft 365のExcelで記事を改訂しました。

目次

XML要素と列の関係付け

Excelには、任意の列にXML要素を関係付けてインポートする機能があります。

まずは、Excelを起動して、XMLファイルのどれか一つを開きます。
「[XMLソース] 作業ウィンドウを使用する」を選択して[OK]をクリックします。

[OK]をクリックします。

XML要素のツリーが表示されます。

ツリーから抽出したいXML要素をドラッグして、セル上にドロップしていきます。
ドロップした名前は、自由に変更できます。
作業が終わったら、一度保存しておきましょう。

上のスクリーンショットでは、次のように設定しました。

列番号列名XML要素補足
Bidissueid
Cproject_idprojectid
Dtracker_idtrackerid
Estatus_idstatusid
Fpriority_idpriorityid
Gauthor_idauthorid
Hsubjectsubject
Idescriptiondescription
Jstart_datestart_date
Kdue_datedue_date
Ldone_ratiodone_ratio
Mestimated_hoursestimated_hours
Ncreated_oncreated_on
Oupdated_onupdate_on
Pclosed_onclosed_on
Qcategory_idcategoryid
Rfixed_version_idfixed_versionid
Sassigned_to_idassigned_toid

XMLの対応付けのプロパティ

XML要素のセルの上で右クリックします。
メニューの[XML]から[XMLの対応付けのプロパティ]をクリックします。

ダイアログが表示されます。レイアウトが自動調整されるのがイヤな場合は「データのレイアウト」のチェックを外しておきます。また、次のインポートで前のデータが上書きされないよう、インポート時の処理を「追加」に変更しておきます。

変更が終わったら[OK]をクリックして閉じます。

XMLファイルのインポート

もう一度、XML要素のセルの上で右クリックします。
次は、メニューの[XML]から[インポート]をクリックします。

ダイアログが表示されます。
インポートしたいXMLファイルを選択して[インポート]をクリックします。
複数のファイルをまとめて指定できます。

シート上にXMLファイルがまとめてインポートされました。

テーブルから標準の範囲に変換

以上で、複数のXMLファイルを1シートにまとめることができました。

インポートしたデータには、テーブル(Excelの機能)が設定されています。
このままでもよいのですが、解除する場合は次のようにします。

テーブルの解除は、すべてのXMLファイルをインポートしてから解除してください。
解除するとインポートできなくなります。

どこでもいいのでXML要素のセルをクリックします。
メニューの[テーブルツール – デザイン]から[範囲に変換]をクリックします。

メッセージが表示されるので[はい]ボタンをクリックします。

テーブルが解除されて、標準の範囲になりました。

最後までご覧いただきありがとうございます。
では、また。

コメント

コメント一覧 (8件)

  • ご返信いただき、ありがとうございます。
    先のコメントは見当違いでしたね、申し訳ありません。

    私も似たような現象を確認しました。
    ヘッダの要素を配置するとセルには枠だけ表示され、インポートを実施すると値が展開されなかったです。(画像を添付しています)

    いろいろ試したところ、「トップのフォルダ」をシートに配置すると、全要素が行方向に展開された状態でインポートできました。(ヘッダは同じ値の繰り返しになります。)
    後で不要な列を削除する必要がありますが、一度試していただけないでしょうか?(こちらも画像を添付しています)

    • EIICHI様
      検証まで行って頂き、本当に有難うございます。
      ここまで教えて頂いて心苦しいのですが、もう1点教えてください。
      画像2のようにすべてのフォルダを配置しようとしますが、
      全ての項目が枠表示になり、画像の様に青色書式になりません。
      試しに、再度新しいエクセルを開いて、③「ReqDtls」(内容)を配置しようとしましたが、こちらも枠になってしまいました。
      EIICHI様の画像2のようになれば感激です。
      何とかお知恵を貸していただきたく宜しくお願い致します。

      • ご確認いただき、ありがとうございます。
        なかなか手強い問題のようです。
        最初のところで「XMLテーブルとして開く」を選択して実行すると、XMLソースの表示を飛ばして、画面2の状態になります。
        Excel内部では同様にXMLソースの解析をしているようなので、結果は同じかもしれませんが、この方法も試していただけないでしょうか?

        • EIICHI様
          できました!
          教えて頂いた方法でうまくいかず、何度もエクセル作成→対応付け→削除を繰り返しました。
          エクセル→データ→データの取得→XMLファイルからも試しました。

          上記の方法で対応付けを行うと、③「ReqDtls」(内容)が突然、青色書式に戻り、これより左側のセルに②「Req」(ヘッダ)を配置すると枠になるので、右側に一旦配置し、行を切り取り→左側に挿入で全てが青色書式になるように配置しました。

          これでインポートを行うと5つのXMLファイル全てを取り込めました。

          無料かつ相談窓口でもないと思いますが、親切に教えて頂き本当に有難う御座いました。
          今後もこちらの記事を参考にさせて頂きたいと思います。

          • 林様
            良かったです!!
            私も、内容より右側に配置したヘッダが青色になることを、確認できました。これには、教えていただくまで全く気がつきませんでした!!
            ご教示いただきありがとうございます。

            またご覧いただける記事が書けるよう頑張りますので、よろしくお願いいたします。(^^)

  • 初心者の私にも分かりやすく助かりました。有難うございます。
    この方法で作成しましたが、私のやり方が悪く、エラーがでてしまいます。
    他に同様の方法を書いている方がいらっしゃらず、ここにコメントさせていただくことご容赦ください。
    XMLファイルを5つ取り込みますが、1つしか取り込まずエラーになります。(なぜエラーかもわからない状況です)
    試しに、インポートできたファイルのコピーをその5つの中にいれて取り込みますが、やはりエラーになります。
    5つのXMLファイルは同じ項目で、各々ファイル自体のエラーは考えにくいです。
    なにか思い当たる理由または解決方法をご存じないでしょうか。

    • はじめまして、管理人のEIICHIと申します。
      せっかく記事を参考にしていただいたのに、申し訳ありません。

      早速ですが、次の操作を試していただけないでしょうか?
      記事のメニュー1から2まで記載の通り進めていただき、3のインポートを1ファイルずつ指定して繰り返します。

      Excel側がスキーマ(構造)が同じであると判断したファイルは、メニュー1で作成されたスキーマでインポートされると思います。
      逆にスキーマが違うと判断したファイルは、抽出する要素が無いことになり、インポートされないと思います。

      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様 ご回答頂きありがとうございます。
        もう1度試して理由が分かった気がします。

        私が作成したエクセルは、上の記事の「シート上にXMLファイルがまとめてインポートされました」のスクリーンショットのように、青色書式が設定されず、セルを枠で囲んであるだけで、要素名も表示されていませんでした。

        元のXMLを見ると、①「Bde_Req」(要素無し)、②「Req」(ヘッダ)、③「ReqDtls」(内容)の3段階で作成されており、③の要素のみを選択して作成すると、青色書式で、5つのXML全てを一括で読み込むことができました。(記事の通りです)

        ②だけで作成するとやはりエラーになり、青色書式もなく、枠のみです(要素名は枠の左や上に選択すると表示されます)

        私の目標は、②も③も同じシートで横一列に読み込みたいのですが、この仕組みでは不可でしょうか。
        もしご存じでしたら、教えてください。
        立て続けの質問で申し訳ありません。

へ返信する コメントをキャンセル

目次