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

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

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

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

# 1. 準備
## 1.1 前提

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

　今回は

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

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

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

**(※1)** テーブルの一覧が記載された「CompanyInfo」シート
![CompanyInfoシート.png](https://qiita-image-store.s3.amazonaws.com/0/45731/2acd6176-bcd2-efa7-6217-996ed51ff2ee.png)

**(※2)** 各テーブル情報が記載された「EMPLOYEE」、「GOODS」、「PARTNER」シート
### 「EMPLOYEE」シート
![EMPLOYEEシート.png](https://qiita-image-store.s3.amazonaws.com/0/45731/f0d1bb04-975b-0768-8bb0-d2657e3814df.png)

### 「GOODS」シート
![GOODSシート.png](https://qiita-image-store.s3.amazonaws.com/0/45731/0a7d5431-44d2-e71e-3b2e-0c830d3e7c5d.png)

### 「PARTNER」シート
![PARTNERシート.png](https://qiita-image-store.s3.amazonaws.com/0/45731/76c7b3e3-d882-cb20-f248-cc448c9bb0b4.png)

**(*3)** マクロ実行後にSQLを出力する「Output」シート
![Outputシート.png](https://qiita-image-store.s3.amazonaws.com/0/45731/41123f71-926d-717d-34a3-9eca6fa8c162.png)

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

```  
アプリケーション: Microsoft Excel for Mac (version 15.40)  
```
   
# 2. 実装

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

```Java: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として実行可能になります。

![image.png](https://qiita-image-store.s3.amazonaws.com/0/45731/38f97044-b8a7-2ed6-442e-9e93cbfef695.png)

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

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