VBAでExcelデータをMariaDBに登録する

こんにちは、ザックス株式会社の開発担当のものです。
前回はPHP開発のシーンで使用する『PHPUnit』という単体テスト専用のフレームワークを紹介させていただきました。

今回は、現在もなお多くの企業が利用されているExcelのデータをそのままDBに登録できれば便利かなと思い、簡易的なツールをつくってみました。

長くなりそうなので、最後までお付き合いいただけたら幸いです。

１．はじめに

まず、利用背景を考えます。

飲食系の中小企業
商品開発事業部があり、お客様に販売する商品を開発するためにExcelを使用している
SCMシステムなど、仕入から製造、販売までを管理するシステム導入はまだ行っていない

今回は、VBAというMicrosoft社のOfficeシリーズに搭載されているプログラム言語を使用して、サンプルファイルにある商品名、売価、原価等の値を、MariaDB(データベース管理システム)に登録したいと思います。

2．ゴール

VBAを記載したExcelファイルを開くと、VBAが自動起動し、対象のサンプルファイルの値をDBに登録する。

3．作業環境／利用ツール

Windows 8.1
Microsoft Excel for Office 365 MSO 32bit
xampp for Windows 7.3.13
MariaDB 10.4.11
MariaDB ODBC 3.1 Driver
VBA記載のExcel配置ディレクトリ C:\RecipeApp\
サンプルファイル配置ディレクトリ C:\RecipeApp\
※xamppの使い方は省略させていただきます。また、MariaDBのrootパスワードや日本語の文字化け対応の設定が済んでいることを前提とします。

4．サンプルファイルの用意

以下のサンプルを用意しました。

上記ファイルの各項目『商品名』『売価』(左:税込、右:税抜)『原価額』『原価率』を対象とします。

5．MariaDBにデータ登録用テーブルを作成

xamppを起動し、『Shell』をクリックします。

MariaDBにログインします。

# mysql -u root -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 17
Server version: 10.4.11-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

データ登録用のデータベース(recipeapp)を作成します。

MariaDB [(none)]> create database recipeapp;
Query OK, 1 row affected (0.006 sec)

次にテーブル(recipe)を作成します。

MariaDB [(none)]> create table recipeapp.recipe (
    -> id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    -> name VARCHAR(255) NOT NULL,
    -> price INT NOT NULL,
    -> price_ex INT NOT NULL,
    -> cost DECIMAL(60, 30) NOT NULL,
    -> cost_rate DECIMAL(20, 20) NOT NULL
    -> );
Query OK, 0 rows affected (0.058 sec)

テーブルのカラム定義を確認します。

MariaDB [(none)]> use recipeapp;
Database changed
MariaDB [recipeapp]> show columns from recipe;
+-----------+----------------+------+-----+---------+----------------+
| Field     | Type           | Null | Key | Default | Extra          |
+-----------+----------------+------+-----+---------+----------------+
| id        | int(11)        | NO   | PRI | NULL    | auto_increment |
| name      | varchar(255)   | NO   |     | NULL    |                |
| price     | int(11)        | NO   |     | NULL    |                |
| price_ex  | int(11)        | NO   |     | NULL    |                |
| cost      | decimal(60,30) | NO   |     | NULL    |                |
| cost_rate | decimal(20,20) | NO   |     | NULL    |                |
+-----------+----------------+------+-----+---------+----------------+
6 rows in set (0.022 sec)

Fieldとサンプルファイルの項目の対応は以下のとおりです。

『name』商品名、『price』売価(税込)、『price_ex』売価(税抜)、『cost』原価額、『cost_rate』原価率

6．MariaDBのODBCドライバをインストール

VBAでMariaDBに接続するために、ODBCドライバをインストールします。

https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.5/

一覧から、Excelのbitに合わせてダウンロード、インストールします。
今回は32bitを使用しているので、『mariadb-connector-odbc-3.1.5-win32.msi』になります。
インストール中にChoose Setup Typeという選択肢が表示されますが、『Typical』を選択しておけば、問題ないかと思います。

7．ODBCの設定

WindowsからODBCドライバを使用できるように設定を行います。
『コントロールパネル』、『システムとセキュリティ』にある『管理ツール』をクリックします。

『ODBC データソース(32ビット)』をクリックします。

『追加』をクリックします。

『MariaDB ODBC 3.1 Driver』を選択し、『完了』をクリックします。

『Name:』には、任意の値を入れてください。
今回はMariaDB_Localと入力して、『Next >』をクリックします。

以下項目の説明になります。
『Server Name:』接続先のサーバーホスト名(IP)
『Port:』接続先のPort番号
『User name:』DBのユーザー名
『Password:』DBのパスワード
『Database:』接続先のDB
接続先のDBは、recipeappを選択します。
一通り入力できましたら、『Test DSN』をクリックすると接続確認ができます。
問題なければ、『Next >』をクリックして進み、『Connection Character Set:』にutf8mb4を入力します。
最後まで進んでいき、設定を完了します。

8．データ登録用VBAを用意

まず、Excelを起動し、『開発』タブから『Visual Basic』をクリックします。
Visual Basicのメニューにある『ツール』『参照設定』から『Microsoft ActiveX Data Objects 2.8 Library』にチェックを入れて、『OK』をクリックします。
これでADOを使用できる準備ができました。
Excelを開いた時に、VBAを実行するように『ThisWorkbook』の中に以下のコードを記載します。

Option Explicit

Private Sub Workbook_Open()
    Dim wb As Workbook
    Dim ws As Worksheet
    Dim name As String
    Dim price As Integer
    Dim price_ex As Integer
    Dim cost As Currency
    Dim cost_rate As Single
    Dim cn As ADODB.Connection
    Dim sql As String

    'sample.xlsxを開き1番目のシートを変数に入れる
    Set wb = Workbooks.Open(Filename:="C:\RecipeApp\sample.xlsx", UpdateLinks:=0)
    Set ws = wb.Worksheets(1)

    'sample.xlsxの1番目のシートから各値を変数に入れる
    name = ws.Range("A3").Value
    price = ws.Range("D3").Value
    price_ex = ws.Range("E3").Value
    cost = ws.Range("G3").Value
    cost_rate = ws.Range("I3").Value
    
    'ADOでMariaDBに接続する
    Set cn = New ADODB.Connection
    cn.ConnectionString = "DSN=MariaDB_Local;"
    cn.Open
    
    'SQL文を実行する
    sql = "insert into recipe(name,price,price_ex,cost,cost_rate)value('" & name & _
    "'," & price & "," & price_ex & "," & cost & "," & cost_rate & ")"
    cn.Execute sql
    
    MsgBox ("recipeテーブルに登録しました")
    cn.Close
    Set cn = Nothing
    wb.Close
End Sub

最後に名前を付けて保存を行います。
RecipeInsert.xlsm
保存する際は『Excel マクロ有効ブック(*.xlsm)』を選択して保存します。

以上で実行する準備ができました。

9．実行結果

それでは、『RecipeInsert.xlsm』をダブルクリックします。

『recipeテーブルに登録しました』とメッセージが表示されますので、『OK』をクリックします。

ファイルを閉じて、xamppのshellから確認したいと思います。
※xamppのshellの設定で現在のコードページがcp932かどうか確認してください。
もし違う場合は、以下のコマンドを実行してcp932の状態にします。

chcp 932

# mysql -u root -p
Enter password: ********
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 15
Server version: 10.4.11-MariaDB mariadb.org binary distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> use recipeapp;
Database changed
MariaDB [recipeapp]> show variables like 'char%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | C:\xampp\mysql\share\charsets\ |
+--------------------------+--------------------------------+
8 rows in set (0.001 sec)

MariaDB [recipeapp]> set names cp932;
Query OK, 0 rows affected (0.000 sec)

MariaDB [recipeapp]> select * from recipe;
+----+----------------------+-------+----------+--------------------------------
---+------------------------+
| id | name                 | price | price_ex | cost
   | cost_rate              |
+----+----------------------+-------+----------+--------------------------------
---+------------------------+
|  1 | 鮭と青のりのおむすび                |   120 |      109 | 21.7800000000000
00000000000000000 | 0.19965000000000000000 |
+----+----------------------+-------+----------+--------------------------------
---+------------------------+
1 row in set (0.000 sec)

サンプルファイルの値がDBに登録されていることが確認できました。

10．最後に

VBAを使ってExcelにあるデータをDBに登録する例を説明してきました。

ビジネス環境の競争が激しい変化に対応するためには、データとデジタル技術の活用がポイントになります。

弊社では、データドリブン経営のノウハウを生かし、企業のデジタルトランスフォーメーションを推進しています。
まずは、お気軽にお問い合わせください。

目次