【VBA】Excelに保存されたテーブルデータからCREATE, INSERT文を生成するマクロ

はじめに

　
　2017年もやがて終わりに近づいていますが、いかがお過ごしでしょうか。 @shIbaInu42 です。
　今回はVBA練習がてらにマクロを使ってみました。
　DB情報がExcelに保存されているということを前提(例)として、テーブル名、カラム名、データの情報を元に
　CREATE、INSERT文をマクロで生成する手順を１章から紹介します。
　(但し、CREATE文は型情報を付け忘れたので、ご参考です。。。)

　使い道はないかもしれませんが、ドコかの何方かのためになれば幸甚 of 幸甚 です。

0. 目次

• 1. 準備
  • 1.1. 前提
  • 1.2. 環境の確認
• 1. 実装
• 1. 結果
• 1. 終わりに

1. 準備

1.1 前提

　構築するDBのテーブル名やカラム名、また保存するデータをExcelで保存していることとします。
　(そのようなケースはないかもしれませんが、、、ジャパニーズSIerではよく見る気がしないでもない。。。)

　今回は

• EMPLOYEEテーブル: とある会社の従業員のデータ
• GOODSテーブル: とある会社が扱う商材のデータ
• PARTNERテーブル: とある会社のパートナー企業のデータ

　の3つのテーブルがそれぞれのシートに記載されています。

　最初のシートにはテーブルの一覧(※1)が記載されており、
　2〜4シート目にはそれぞれのテーブルのカラムと保存するデータ(※2)が記載されています。
　最後、5シート目にSQLを生成する出力先のシートとして「Output」シート(※3)を設けました。

(※1) テーブルの一覧が記載された「CompanyInfo」シート

(※2) 各テーブル情報が記載された「EMPLOYEE」、「GOODS」、「PARTNER」シート

「EMPLOYEE」シート

「GOODS」シート

「PARTNER」シート

*(3) マクロ実行後にSQLを出力する「Output」シート

1.2 環境の確認　

　VBAを作成するにあたり、アプリケーションはOSに合わせてMac用Excelを用いました。　　
　詳細は次の通りです。　　

アプリケーション: Microsoft Excel for Mac (version 15.40)  

2. 実装

　なお、生成されるSQLはpostgreSQLを想定しており、INSERT文は明示的にカラム名を示さない方式です。

CREATE文とINSERT文を生成

'Extract, Transform, Load for TABLE information
Sub etlExecute()
    Dim i As Integer
        
    ' For appending to array, Re-Extracting from array
    Dim col As Integer
    Dim row As Integer
    
    ' Ouput position on "Output" Worksheet
    Dim opRowCount As Integer
    Dim opColCount As Integer
    
    ' Position Num of last worksheet Output (this also means Number of Worksheets)
    Dim outputSheetNum As Long

    opRowCount = 1
    opColCount = 1
    outputSheetNum = Worksheets.Count
    
    ' Cheking each data sheet
    For i = 2 To outputSheetNum - 1

        Worksheets(i).Activate
        Dim colNames(100) As String
        Dim keyNames(100) As String
        Dim valNames(100) As String
        Dim header As String
        header = "//// CREATE + INSERT: "
        
        col = 1
        row = 1
        
        ' Creating TABLE
        Dim tableName As String
        tableName = Worksheets(i).Name

        Do Until Cells(1, col) = ""
            colNames(col) = Cells(1, col)
            col = col + 1
        Loop
        col = col - 1
        
        ' Adding CREATE statement on Output Worksheet
        Worksheets(outputSheetNum).Cells(opRowCount, opColCount) = header + tableName
        opRowCount = opRowCount + 1
        Worksheets(outputSheetNum).Cells(opRowCount, opColCount) = "CREATE TABLE"
        Worksheets(outputSheetNum).Cells(opRowCount, opColCount + 1) = tableName + " ("
        
        Dim j As Integer
        For j = 1 To col
            opRowCount = opRowCount + 1
            If j = col Then
                Worksheets(outputSheetNum).Cells(opRowCount, opColCount) = indent + colNames(j)
            Else
                Worksheets(outputSheetNum).Cells(opRowCount, opColCount) = indent + colNames(j) + ","
            End If
        Next
        opRowCount = opRowCount + 1
        Worksheets(outputSheetNum).Cells(opRowCount, opColCount) = ");"
        opRowCount = opRowCount + 1
        
        ' Inserting each data into TABLEs
        Do Until Cells(row, 1) = ""
            keyNames(row) = Cells(row, 1)
            valNames(row) = Cells(row, 2)
            row = row + 1
        Loop
        row = row - 1

        For j = 2 To row
            opRowCount = opRowCount + 1
            Worksheets(outputSheetNum).Cells(opRowCount, opColCount) = "INSERT INTO " + tableName + " VALUES('"
            Worksheets(outputSheetNum).Cells(opRowCount, opColCount + 1) = keyNames(j)
            Worksheets(outputSheetNum).Cells(opRowCount, opColCount + 2) = "'', '"
            Worksheets(outputSheetNum).Cells(opRowCount, opColCount + 3) = valNames(j)
            Worksheets(outputSheetNum).Cells(opRowCount, opColCount + 4) = "'');"
        Next
        opRowCount = opRowCount + 2
       
    Next i
End Sub

#3. 結果

　２章で記載したマクロを実行すると次のように「Output」シートに
　CREATEとINSERT文が生成されます。
　なお、冒頭でも述べていますがCREATE文には型定義が抜けていますのでご容赦願います。。。m(_ _)m

　出力先をExcel以外にするか、「Output」シートに出力された内容をテキストエディタを用いて体裁を整えれば
　そのままSQLとして実行可能になります。

#4. 終わりに

VBAで働き方改革

　VBAはとあるベンチャーでのインターンシップ以来触っていないのですが、
　Excelはどの企業でも用いると思いますし、機械的作業はマクロ化して業務効率上げた方が良いと思うので、
　今後も慣れていく必要がありますね。(マクロ作成に時間を要しても本末転倒ですね。。。)

夏休みの宿題が終わらない

　夏にDash Buttonハックや8bitミュージックを乱数生成するプロジェクト(メンバ1人)を発足しましたが、
　完了せず。。。冬までかかる見通しです。
　そのうちうｐしたい所存です。