[DBプログラミング特別企画] 2/3
ExcelデータをOracleに格納する裏技
日本オラクル
宮本 尚志
2005/5/18
■VBAでExcelデータをXML化してOracleに格納する
VBAでXML化する方法は、マクロでExcelシートのセルの値を取得し、それをタグの文字列と結合させ、XMLを生成するというものです。その後生成されたXMLファイルをXML DBリポジトリに送信し、データベースに格納します。従って、手順としては以下のようになります。
- マクロでExcelシートのデータをXMLに変換、ローカルに保存
- ローカルに保存したXMLファイルをFTPでOracleデータベース(XML DBリポジトリ)に送信
XML DBリポジトリは、表面的にはファイルシステムであり、FTPなどでアクセス可能ですが、データはOracleデータベースの内部的な表に格納されています。従って、このXML DBリポジトリにXMLファイルを保存すれば、Oracleデータベース内に格納したことになります。
マクロでExcelシートのデータをXML化
図2のような経費精算のExcelシートについて考えてみましょう。
図2 サンプル用の経費精算書(Excel文書)(画像をクリックすると拡大します) |
図2のサンプル用の経費精算書(Excel文書)は、以下のようなマクロでデータをXML化できます。
Public Sub create_xml() |
リスト1 ExcelデータをXML化するマクロ(表示の都合で改行されています。コードはkeihi1.clsから確認できます) |
この例では、シート内の項目名を“<”と“>”で囲んでタグにし、データを開始タグと終了タグで連結させて要素としています。このマクロを実行すると以下のようなXML文書が生成されます。
<?xml version="1.0" encoding="Shift-JIS"?> |
リスト2 リスト1のマクロにより生成されるXML |
マクロでXMLファイルをXML DBリポジトリに送信
生成したXMLファイルをFTPなどでXML DBリポジトリに送れば、Oracleデータベース内にXMLデータとして格納できます。XML DBリポジトリに対してFTPで接続する場合には、FTPサーバ名にOracleデータベースのサーバ名、ポート番号は2100(デフォルトの場合)を指定します。マクロからFTPで送信するようにしておけば、マクロを実行するだけでOracleデータベース内にXMLファイルを保存できます。
格納したデータの検索
XML DBリポジトリに保存したXMLファイルは、SQLからも検索可能です。XML DBリポジトリに格納したファイルの中身を取り出すには、XDBUriType関数を使用します。例えばXML DBリポジトリのhomeフォルダに「1234-20050401123045.xml」という名前で保存した場合、以下のようなSQL文でその中身を取得することが可能です。
SQL> select XDBUriType('/home/1234-20050401123045.xml').getXML() |
リスト3 XML DBリポジトリに対する検索 |
リレーショナルな表に格納した場合は、何回出現するか分からない各経費(XMLファイル中の「経費」要素以下)をそれぞれ1レコードに格納することになりますので、1つの経費申請全体は複数のレコードで構成されることになり、検索で必ず結合が発生します。XMLで表現すれば1つの経費申請が表の1レコードに対応しますから、操作・管理が容易になります。
また、アップロードしたXMLファイルの中身を別のマスタ表などに容易にINSERTできます。申請者からExcelファイルを経理担当者が受け取り、手作業でシステムに入力している場合、その手間を省くことができます。
ただし、この方法はアップロードしたXMLのSQLによる更新や、複数XML文書からの集計には適していません。また、VBAでXML化するのはシートのサイズが大きい場合には不向きです。これらの問題点は、次に説明するExcel 2003を使用することで解決できます。(次ページに続く)
2/3 |
Index | |
[DBプログラミング特別企画] ExcelデータをOracleに格納する裏技 |
|
Page
1 ・ExcelとOracleの相性はかなり良好 ・ExcelデータをXMLに変換してOracleに格納するメリット ・事前準備とExcelデータをXML化する2つの方法 |
|
Page
2 ・VBAでExcelデータをXML化してOracleに格納する |
|
Page
3 ・Excel 2003でXML化してOracleに格納 |
ExcelデータをOracleに格納する裏技 |
- Oracleライセンス「SE2」検証 CPUスレッド数制限はどんな仕組みで制御されるのか (2017/7/26)
データベース管理システムの運用でトラブルが発生したらどうするか。DBサポートスペシャリストが現場目線の解決Tipsをお届けします。今回は、Oracle SE2の「CPUスレッド数制限」がどんな仕組みで行われるのかを検証します - ドメイン参加後、SQL Serverが起動しなくなった (2017/7/24)
本連載では、「SQL Server」で発生するトラブルを「どんな方法で」「どのように」解決していくか、正しい対処のためのノウハウを紹介します。今回は、「ドメイン参加後にSQL Serverが起動しなくなった場合の対処方法」を解説します - さらに高度なSQL実行計画の取得」のために理解しておくべきこと (2017/7/21)
日本オラクルのデータベーススペシャリストが「DBAがすぐ実践できる即効テクニック」を紹介する本連載。今回は「より高度なSQL実行計画を取得するために、理解しておいてほしいこと」を解説します - データベースセキュリティが「各種ガイドライン」に記載され始めている事実 (2017/7/20)
本連載では、「データベースセキュリティに必要な対策」を学び、DBMSでの「具体的な実装方法」や「Tips」などを紹介していきます。今回は、「各種ガイドラインが示すコンプライアンス要件に、データベースのセキュリティはどのように記載されているのか」を解説します
|
|