［DBプログラミング特別企画］　2/3

ExcelデータをOracleに格納する裏技

日本オラクル
宮本 尚志
2005/5/18

■VBAでExcelデータをXML化してOracleに格納する

　VBAでXML化する方法は、マクロでExcelシートのセルの値を取得し、それをタグの文字列と結合させ、XMLを生成するというものです。その後生成されたXMLファイルをXML DBリポジトリに送信し、データベースに格納します。従って、手順としては以下のようになります。

1. マクロでExcelシートのデータをXMLに変換、ローカルに保存
2. ローカルに保存した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()          '' XMLファイル保存先の指定     Dim iFileNum As Integer     iFileNum = FreeFile     Dim SaveFileName As String     SaveFileName = "C:\temp\" & Cells(3, 3) & ".xml"     Open SaveFileName For Output As #iFileNum          '' 各経費項目の要素名     Dim elem_1 As String     elem_1 = "経費"          '' XMLの生成     Print #iFileNum, "<?xml version=""1.0"" encoding=""Shift-JIS""?>"     Print #iFileNum, "<" & Cells(2, 5) & ">"     Print #iFileNum, "<" & Cells(3, 2) & ">" & Cells(3, 3) & "</" & Cells(3, 2) & ">"     Print #iFileNum, "<" & Cells(4, 2) & ">" & Cells(4, 3) & "</" & Cells(4, 2) & ">"     Print #iFileNum, "<" & Cells(3, 4) & ">" & Cells(3, 5) & "</" & Cells(3, 4) & ">"     Print #iFileNum, "<" & Cells(4, 4) & ">" & Cells(4, 5) & "</" & Cells(4, 4) & ">"     Print #iFileNum, "<" & Cells(7, 2) & ">" & Cells(7, 3) & "</" & Cells(7, 2) & ">"     Print #iFileNum, "<" & Cells(6, 2) & ">" & Cells(6, 3) & "</" & Cells(6, 2) & ">"     Print #iFileNum, "<" & Cells(8, 2) & ">" & Cells(8, 3) & "</" & Cells(8, 2) & ">"     Print #iFileNum, "<" & Cells(6, 4) & ">" & Cells(6, 5) & "</" & Cells(6, 4) & ">"     Print #iFileNum, "<" & Cells(8, 4) & ">" & Cells(8, 5) & "</" & Cells(8, 4) & ">"     Print #iFileNum, "<" & Cells(7, 4) & ">" & Cells(7, 5) & "</" & Cells(7, 4) & ">"                    Print #iFileNum, "<" & Cells(10, 2) & ">"          For i = 1 To 20         '' 空の行になったら終了         If Cells(i + 11, 2) = "" Then Exit For             Print #iFileNum, "<" & elem_1 & ">"             Print #iFileNum, "<" & Cells(11, 2) & ">" & Cells(11 + i, 2) & "</" & Cells(11, 2) & ">"             Print #iFileNum, "<" & Cells(11, 3) & ">" & Cells(11 + i, 3) & "</" & Cells(11, 3) & ">"             Print #iFileNum, "<" & Cells(11, 4) & ">" & Cells(11 + i, 4) & "</" & Cells(11, 4) & ">"             Print #iFileNum, "<" & Cells(11, 6) & ">" & Cells(11 + i, 6) & "</" & Cells(11, 6) & ">"             Print #iFileNum, "</" & elem_1 & ">"     Next i          Print #iFileNum, "</" & Cells(10, 2) & ">"     Print #iFileNum, "<" & Cells(32, 5) & ">" & Cells(32, 6) & "</" & Cells(32, 5) & ">"     Print #iFileNum, "</" & Cells(2, 5) & ">"          If iFileNum > 0 Then Close #iFileNum      End Sub

リスト1　ExcelデータをXML化するマクロ（表示の都合で改行されています。コードはkeihi1.clsから確認できます）

　この例では、シート内の項目名を“<”と“>”で囲んでタグにし、データを開始タグと終了タグで連結させて要素としています。このマクロを実行すると以下のようなXML文書が生成されます。

<?xml version="1.0" encoding="Shift-JIS"?> <経費精算書>   <申請番号>1234-20050401123045</申請番号>   <状態>未承認</状態>   <承認者承認>未</承認者承認>   <経理承認>未</経理承認>   <社員番号>1234</社員番号>   <氏名>宮本　尚志</氏名>   <経費負担部署>マーケティング本部</経費負担部署>   <申請日>2005/4/1</申請日>   <承認者>ウェンディ・ウェンディ</承認者>   <申請内容>名古屋出張に伴う出張費</申請内容>   <経費詳細>     <経費>       <日付>2005/3/25</日付>       <経費種類>交通費</経費種類>       <詳細>東京→名古屋（新幹線）</詳細>       <金額>10780</金額>     </経費>     <経費>       <日付>2005/3/25</日付>       <経費種類>飲食費</経費種類>       <詳細>夕食（タベルナ・バボ）</詳細>       <金額>3000</金額>     </経費>     ……中略……   </経費詳細>   <合計>31490</合計> </経費精算書>

リスト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()  2 > from dual; XDBURITYPE('/HOME/1234-20050401123045.xml').GETXML() ---------------------------------------------------- <?xml version="1.0" encoding="Shift-JIS"?> <経費精算書>   <申請番号>     ……中略…… </経費精算書>

リスト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に格納する裏技

＠ITメールマガジン　新着情報やスタッフのコラムがメールで届きます（無料）