[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に格納する裏技



Database Expert フォーラム 新着記事
@ITメールマガジン 新着情報やスタッフのコラムがメールで届きます(無料)

注目のテーマ

Database Expert 記事ランキング

本日月間