Excelファイルからデータを抽出したい

OrangeLab. 運営チーム
作成日時: - 更新日時:
Avatar

使用ソフトウェア:DataMagic for Windows Ver.2.2.2
以下のリンクより、当Tipsのサンプルスクリプトがダウンロード可能です。
是非ダウンロード頂き、ご活用ください。

Excelファイルからデータを抽出したい

■はじめに

こんにちは、メディアフォースの西です。

Excelは日々のデータ集計業務の中でもよく使用されていると思います。
発注書や顧客名簿のような大量データ、また請求書や見積書など、様々な用途で使われています。

DataMagicでは、入出力にExcel形式のデータを取り扱うことができます。

本連載記事では、表形式のExcelデータを抽出して、データベースへ出力する方法をご紹介します。

■事前準備

変換元となるExcelデータの用意をします。

サンプルデータ

上記サンプルデータを「C:\work\SAMPLE4\」に格納してください。("in.xlsx"というファイル名で保存)



出力先のデータベースの用意をします。

SQLite データベース「SAMPLE_DB」に以下のカラムの名前を持つテーブル「SAMPLE_TBL」を作成します。
DB の漢字コード種はSJIS とします。
  • ORDER_NO
  • DATE
  • CODE
  • NAME
  • QUANTITY
  • UNIT_PRICE
  • SUB_TOTAL

■手順

データ変換に必要な定義情報を作成します。
データ変換の流れは大きく分けると以下になります。
  1. 入力ファイル情報の設定
    • 接続情報
    • アダプタ情報
  2. 出力ファイル情報の設定
    • 接続情報
    • アダプタ情報
  3. データ加工情報の設定

Step01

まずは、「1.入力ファイル情報の設定」を行いましょう。

入力用のExcel ファイルを読み込むための情報を定義します。
DataMagicでは、Excel 形式をDB テーブル形式の一種として扱います。

タブボタンの「アダプタ」をクリックします。
続いて、「データベース」タブにある「DB接続」を指定後、「新規作成」をクリックします。



DB接続情報詳細画面が起動したら、下記画面を参考に設定し、[適用]ボタンをクリックして保存します。



項目名 説明
ID 登録したDB接続情報を識別するためのIDを指定します。
今回はDB接続IDを【SAMPLE4_in】とします。
製品種別 接続先データベースの種別を選択します。
今回は【Excel】を選択します。
ファイル名 入出力の対象とするExcelファイルを指定します。
事前準備で用意した【C:\work\SAMPLE4\in.xlsx】を設定します。
形式 Excelファイルの形式を選択します。
Excelはバージョンによってファイルの仕組みが異なるため、処理対象の形式を指定してください。

Step02

入力データのアダプタ情報(DBテーブル情報)を定義します。

「データベース」タブにある「DBテーブル」を指定後、「新規作成」をクリックします。



DBテーブル情報詳細画面が起動したら、下記画面を参考に設定し、[適用]ボタンをクリックして保存します。



項目名 説明
ID 登録したDBテーブル情報を識別するためのIDを指定します。
今回はDBテーブルIDを【SAMPLE4_in】とします。
DB接続ID 当該DBテーブル情報を使用する際に接続するデータベースのIDを指定します。
Step01で作成したDB接続ID【SAMPLE4_in】を指定します。
入出力区分 入力用の設定か出力用の設定かを選択します。
ここでは【入力】を選択します。
対象シート 項目取得の対象とするシートを指定します。
今回は【読み取るシートを指定】を選択します。
シート名 項目取得時に読み取りの対象とするシート名を指定します。
今回は【input】を設定します。
対象範囲 入出力の対象範囲を、レコード単位で指定するか、または単一のセル単位で指定するかを選択します。
今回は【レコード】を選択します。
エラーセルの扱い 読み込み対象のセルに設定された計算式の結果がエラーだった場合の処理を選択します。
開始行番号 対象範囲に「レコード」を選択した場合に、読み書きを開始する先頭行を指定します。
項目情報 [項目取得]ボタンをクリックして、Excel ファイルから項目情報を取得する、もしくは1項目ずつ手動で指定します。
手動で設定する場合は、空行をダブルクリックして表示されるDBテーブル情報詳細画面から行います。
今回は[項目取得]ボタンから設定します。
終了位置 読み込みを終了する位置条件を、次のドロップダウンリストから指定します。
※今回は【データの終わり】を指定します。

これで、「1.入力ファイル情報の設定」は完了です。

Step03

次に、「2.出力ファイル情報の設定」を行います。

Step01~02の手順に沿って、下記画面のようにDB接続情報とアダプタ情報(DBテーブル情報)を設定します。

<DB接続情報>


項目名 説明
ID DB接続IDを【SAMPLE4_out】とします。
製品種別 今回は【SQLite3】を選択します。
漢字コード種 接続先データベースの漢字コード種を選択します。
今回は【設定しない】を選択します。
DB名 接続するデータベース名を指定します。

<アダプタ情報(DBテーブル情報)>


項目名 説明
ID 今回はDBテーブルIDを【SAMPLE4_out】とします。
DB接続ID Step03で作成したDB接続ID【SAMPLE4_out】を指定します。
入出力区分 ここでは【出力】を選択します。
指定方法 入出力用SQL文の生成方式を選択します。
今回は【簡易指定】を選択します。
テーブル名 入出力の対象となるテーブル名を指定します。
今回の出力テーブルを【SAMPLE_TBL】とします。
項目情報 [項目取得]ボタンをクリックして、DBテーブルから項目情報を取得する、もしくは1項目ずつ手動で指定します。
今回は[項目取得]ボタンから設定します。
更新方法 データベース出力時に更新する方法を指定します。
今回は【INSERTを行う】を指定します。

これで、「2.出力ファイル情報の設定」は完了です。

Step04

入出力ファイル情報の設定が終わったら「3.データ加工情報の設定」を行います。

ここでは、どのようなタイプの入力ファイルを、どのようなタイプのファイルとして出力するのか、また、どういった条件のデータを出力するかなど、データの変換を行うための条件を設定します。

タブボタンから「データ加工」を指定し、操作メニューから「新規作成」をクリックします。


Step05

まず、入力ファイルの設定を行います。

データ加工情報設定画面が表示されます。
任意のIDを入力し、「入力ファイルアイコン」をダブルクリックします。



項目名 説明
ID 今回はデータ加工情報IDを【SAMPLE4】とします。

Step06

下記画面のように入力ファイルの情報を設定します。

<アダプタ別タブ>


項目名 説明
形式 どのようなタイプの入力の形式を行うか選択します。
今回は【DBテーブル】を選択します。
ID DBテーブルIDを設定します。
Step02で作成した【SAMPLE4_in】を設定します。
数値バイトオーダ 入力ファイルの数値バイトオーダを選択します。
数値項目NULL値の扱い 入力データにNULL値があった場合、空文字として扱うか、0に置き換えるかを選択します。
入力0レコードの扱い 入力データの0レコードだった場合の動作を選択します。

<コード変換タブ>


項目名 説明
漢字コード種 入力ファイルの漢字コード種を指定します。
今回は【UTF-8】を指定します。
Unicodeバイトオーダ 入力ファイルのバイトオーダを指定します。

これで、入力ファイルの設定は完了です。

Step07

次に、データの抽出条件の設定を行います。
入力設定画面の設定が終わると新たに抽出情報が作成されます。

「抽出条件アイコン」をダブルクリックします。


Step08

抽出条件設定画面が表示されます。

下記画面を参考に設定を行い、[OK]ボタンをクリックして保存します。
※ 今回は、すべてのレコードを抽出する条件としています。



これで、データの抽出条件の設定は完了です。

Step09

次に、出力ファイルの設定を行います。

「出力ファイルアイコン」をダブルクリックします。


Step10

出力設定画面のアダプタ別タブを設定し、[OK]ボタンをクリックして保存します。

<アダプタ別タブ>


項目名 説明
形式 今回は【DBテーブル】を選択します。
ID Step03で作成した【SAMPLE4_out】を設定します。
数値バイトオーダ 出力ファイルの数値バイトオーダを選択します。
入力データサイズが0バイトの場合の扱い 入力データ中の空文字(0バイト)の変換方法を指定します。
データタイプが日付タイプ(D)、文字タイプ(M、N、W)、および数字文字タイプ(Z)の場合の
変換方法を指定できます。

これで、出力ファイルの設定は完了です。

Step11

次に、入力ファイル・出力ファイルの項目間のマッピング処理を定義します。

抽出条件と出力条件を結び、「マッピングアイコン」をダブルクリックします。


Step12

マッピング情報設定画面が表示されます。

下記画面を参考に、入力項目と出力項目のマッピング線を結びます。
マッピングが完了したら、[OK]ボタンをクリックしてマッピング情報を登録します。


Step13

最後に、データ加工情報設定画面の[適用]ボタンをクリックします。



これで、「3. データ加工情報の設定」は完了です。

Step14

それでは実行してみましょう。データ加工情報設定画面上部の実行をクリックします。



データ加工実行画面が表示されるので、出力ファイル生成モードを確認し[実行]ボタンをクリックします。
正常終了したら出力ファイルを確認してください。



※ 出力ファイル生成モード
パラメータ 説明
新規作成 指定した出力ファイルを新規に作成します。すでに同じ名前のファイル名が存在する場合は、エラーになります。
置き換え 指定したファイルと置き換え(上書き)ます。
追加 指定した既存のファイルに出力データを追加します。

Step15

Step3で指定した【SAMPLE_TBL】にデータが出力されます。

出力されたデータを確認します。



※下記は上記出力結果を明示的に表現しています

■さいごに

いかがでしたか?

今回は表形式のExcelデータでしたが、帳票形式のExcelデータも取り扱えます。
Step02で設定する「DBテーブル情報詳細画面」で、対象範囲を【単一セル】を選択し、読み取るセルを指定します。



詳しい設定方法に関しては、「運用マニュアル」を参照して下さい。

サンプルデータファイル

この記事は役に立ちましたか?
0人中0人がこの記事が役に立ったと言っています

コメント