スポンサーリンク

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


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

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


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

  • テストデータ自体がExcel上に書いてあり
  • 書いてあるその場で,シート上のボタンをただ押すだけで,実際にDBにインポートが行なわれる

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

流れ


手順:

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


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

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

以下はコード内容。

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

(1)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...

(2)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インポートしたい場合は下記を参照。

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

(3)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文を独自に構築するよりは,はるかに楽なのだから。。