SQL
Excel
VBA
マクロ

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

はじめに

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

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

0. 目次

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

1. 準備

1.1 前提

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

 今回は

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

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

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

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

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

「EMPLOYEE」シート

EMPLOYEEシート.png

「GOODS」シート

GOODSシート.png

「PARTNER」シート

PARTNERシート.png

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

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. 結果

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

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

image.png

4. 終わりに

VBAで働き方改革

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

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

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