Excelシート上のデータを DB にインポートするVBAマクロ

バッチ・マクロのTOPへ

Excelワークシート上のデータを，そのまま直接DBに登録する。

VBAだけで済ませる。（Excel以外のツールやAPIが不要）

たとえばアプリケーションのテスト仕様書がExcelで，テスト項目の中に「特定のデータをインポート」というのがある場合・・・

テストデータ自体がExcel上に書いてあり

書いてあるその場で，シート上のボタンをただ押すだけで，実際にDBにインポートが行なわれる

という具合に，テストが楽にできる。

流れ

手順：

Excelにテストデータを書いておく。
1. 8行2列目から，右方向に順番にDBのカラム名が書いてある。
2. 9行2列目から，下方向に１行ずつテストデータのレコードが書いてある。
3. 9行1列目から，下方向に「何か」書いておく。何か書いてある場合，その行にはテストデータが書いてあるとみなす。
下記のマクロ＋batファイル＋sqlファイルを準備。（※テストデータに関わらず使いまわせる。）
マクロを実行。

コードにより実行される作業内容：

VBAで，ワークシート上のテストデータをCSVに出力する。（文字コードはUTF8）
1. データ中の引用符記号はエスケープされる。
2. データの両端に「"」がつく。
VBAから外部コマンドのパイプでBATファイルを呼び出す。
BATファイルは，準備済みのsqlを実行する。
SQLファイルは，CSVファイルをDBにインポートする。

以下はコード内容。

処理詳細はコメントを参考に。

（１）VBA

まずVBA。

Sub import_records()
    
    ' 設定
    dbcol_col = 8 ' DBカラムの書いてある行
    dbcol_row = 2 ' DBカラムのスキャン開始列
    offset_col = 9 ' DBレコードのスキャン開始行
    emptytest_row = 1 ' レコードが空かどうか判定する列
    csv_name = "temp.csv"
    
    
    ' カラム数読み取りのループ
    dbcol_rownum = 0 ' カラム数
    db_cnt = dbcol_row ' カウンタ
    dbcol_continue_flag = True
    Do While dbcol_continue_flag = True
    
        ' セルが空か
        emptytest_dbcol = Cells(dbcol_col, db_cnt).Value
        If Len(emptytest_dbcol) = 0 Then
            dbcol_continue_flag = False
        Else
            ' 次のカラムへ
            db_cnt = db_cnt + 1
            dbcol_rownum = dbcol_rownum + 1
        End If
        
    Loop
        'MsgBox "カラム数は" & dbcol_rownum
    
    
    ' レコード読み取りのループ
    ' (offset_col行, dbcol_row列) からレコードが存在する
    col = offset_col ' カウンタ
    continue_flag = True
    csv_str = "" ' 書きだし用文字列
    Do While continue_flag = True
        
        ' セルが空か
        emptytest_str = Cells(col, emptytest_row).Value
        If Len(emptytest_str) = 0 Then
            
            ' 終了
            continue_flag = False
        
        Else
            ' この行のレコードの処理
            For i = 0 To dbcol_rownum - 1
                temp_cell = Cells(col, dbcol_row + i).Value
                
                ' サニタイズ
                temp_cell = Replace(temp_cell, """", """""")
                
                ' ""でくくる（空の場合は警告が出る事も）
                csv_str = csv_str & """" & temp_cell & """"
                
                ' 行末でなければカンマ
                If i < dbcol_rownum - 1 Then
                    csv_str = csv_str & ","
                End If
            
            Next i
            ' 改行
            csv_str = csv_str & vbCrLf
            
            ' 次の行へ
            col = col + 1
        End If
    
    Loop
        'MsgBox "読み取りました。スキャンした行の数：" & (col - offset_col)
    
    ' 書き出し
    
    ' 出力パス
    output_path = ThisWorkbook.Path & "\" & csv_name
    Dim ados As Object
    Set ados = CreateObject("ADODB.Stream")
    ados.Open
    ados.Type = 2
    ados.Charset = "UTF-8"
    ados.WriteText csv_str
    ados.SaveToFile output_path, 2
    ados.Close
        'MsgBox "書き出しました。"

    
    ' インポート
    ChDrive ThisWorkbook.Path
    ChDir ThisWorkbook.Path
        'MsgBox CurDir    
    Shell "import_csv.bat"
    MsgBox "インポートしました。"

End Sub

参考：

CSVをUTF-8で保存する
http://www.kernel-net.ne.jp/tech/inde...

（２）bat

そしてバッチ。

複数のワークシートからDBインポートを実行する場合でも，このバッチを書き換えれば一括してDB情報を変更できる。

かりにDBがMySQLだとしよう。

import_csv.bat

mysql -u USERNAME -pPASSWORD --default-character-set=utf8 < import_csv.sql

参考：

MySQL　コマンドラインオプション
http://dev.mysql.com/doc/refman/4.1/j...

また，PostgreSQLでCSVインポートしたい場合は下記を参照。

大量のテストデータを快適に作る７つのコツ - 負荷テストのためにExcel＆VBAをうまく使う
http://language-and-engineering.hatenablog.jp/entry/20080929/1222698370

（３）SQL

import_csv.sql

-- CSVインポート

use DBNAME;
load data local infile "temp.csv" into table TABLENAME fields terminated by ',' enclosed by '"' lines terminated by '\r\n';

参考：

データをCSV（Excel）で入出力する方法
http://good-stream.com/goodstream/dat...

補足

idカラムがauto_incrementだったりすると，CSV上でどう書いても警告が出てしまう。（空欄でも""でもNULLでもWARNING）

Incorrect integer value: 'NULL' for column 'id' at row 1

ただしDBへのINSERTは正常に行われる。

ここは目をつぶろう。

フィールドの型に合わせて頑張ってINSERT文を独自に構築するよりは，はるかに楽なのだから。。

バッチ・マクロのTOPへ