LoginSignup
8

More than 3 years have passed since last update.

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

Last updated at Posted at 2017-11-26

はじめに

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

(※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したい所存です。

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
8