ExcelをCSV, SQL文, JSONに簡単変換(VBA)

vba

はじめに

  • 業務作業の中で、リストやCSV、Excelのデータを要件に基づいて大量に変換したい場合があります。ここでは、可能な限り簡単にデータの変換を行うためのExcel VBAのサンプルを紹介します。
  • ここで紹介するサンプルは、ちょっとした業務作業の効率化のために簡単に変更して使えることを目標にしており、可能な限り短く分かりやすいコードにしています。業務要件で求められるようなエラーハンドリング等の考慮はしていないことにご注意ください。
  • ここで紹介するサンプルは、Windows 10(64ビット)のExcel 2019(32ビット)で動作確認しています。

スクリプト概要

  • シート上のデータを行単位に読み取り、その内容を編集した結果をファイルに出力します。

    • 行の内容に基づいてファイルに出力する内容を編集する行編集関数を用意しています。要件に一番近い行編集関数を呼び出すよう変更し、その行編集関数の内容を要件に合わせて変更してください。
      行編集関数の名称説明
      CSV
      (ProcessRowAsCsv関数)
      ヘッダ行を含むCSVレコードを生成します。ヘッダ行の列、データ行の値は全て引用符付きで出力します。
      テキスト
      (ProcessRowAsTxt関数)
      データ行からコマンドテキストを作成します。出力内容はサンプル(イメージ)です。
      (単純なコマンドであればExcel関数式の方が早いかもしれません。)
      SQL
      (ProcessRowAsSql関数)
      SQLのINSERT文を生成します。
      JSON
      (ProcessRowAsJsn関数)
      JSONを生成します。(各行データをJSONオブジェクト要素とする配列形式)
    • Windows環境での出力ファイルのエンコーディングはShift_JISです。サンプルを修正して、UTF-8に変更することもできます。
  • コピペして最小限の設定で実行できるようになっています。

    • 処理対象となるデータを含むExcel(VBA)にスクリプトを張り付け、「ヘッダ行数」「データ領域」等を変更し、実行するだけです。(xls,xlsxファイルに張り付けて実行することも可能ですが、保存する場合はxlsm形式が必要です。)
    • VBAの編集画面はショートカット「Alt + F11」で開けます。
      メニューから開く場合、[オプション]-[リボンのユーザー設定]で開発タブを表示し、”Visual Basic”をクリックします。詳細はこちらをご覧ください。
  • スクリプトの基本となる考えや処理の概要は次の通りです。

    • ヘッダ行とデータ行を特定し、データ行の行毎に行編集関数ProcessRowAsXXX()を実行します。
    • 定数DataStartA1, DataEndA1に指定された範囲をデータ行とします。ヘッダ行の直前N行(定数HeadRowCount)をヘッダ行とします。
    • ProcessRowAsXXX()の引数として「ヘッダ行」(範囲)、「データ行」(範囲)、「処理対象の行番号」(整数)が渡されます。この関数の返却値がファイルに出力されます。
    • ヘッダ・フッタデータを作成する関数の作成も考えたのですが、関数が複数に分かれると切替が面倒なので、必要であれば行編集関数でヘッダ・フッタを出力する設計にしています。

スクリプト内容

Excel VBAスクリプトの説明です。
完全なソースコードはgithub(「簡単変換サンプル.xlsm」)で公開しています。

基本部分と行編集関数:CSV

  • スクリプトの主要な処理のスクリプトは次の通りです。
    (例としてCSVを出力する行編集関数を含んでいます。)
  • カスタマイズのポイント
    行番号説明
    3-5シートの内容に応じて、ヘッダ行数(定数HeadRowCount)、データ領域(定数DataStartA1、定数DataEndA1)を変更してください。DataEndA1はシートの使用範囲から自動的に決定されますが、変更したい場合は値を指定してください。
    7実行結果は、VBAを張り付けたExcelブックと同じフォルダにoutput.txtとして出力されます。出力先を変更する場合は、定数OutputFileを変更してください。
    10シート名を変更してください。
    30-32要件に近い行編集関数が実行されるよう、不要な行編集関数をコメントアウトしてください。
    39Windows環境ではShift_JISで保存します。UTF-8で保存したい場合、ResaveAsUtf8関数を実行するようコメントアウトを外してください。
    42ファイル作成後にコマンドを実行したい場合、Shell関数が実行されるようコメントアウトを外して、希望のコマンドを指定してください。
    57-79ヘッダ行、データ行の全ての項目を引用符付きで出力する実装になっています。要件に応じて変更してください。

行編集関数:テキスト

  • コマンド等の任意のテキストを生成するための行編集関数です。
    • シート上の複数列の値に基づいて内容に基づいて、コマンド等を作成する際に使用する想定です。
    • サンプルで出力しているコマンドテキストはサンプルであり特に意味はありません。
    • 私の業務作業で、Azure上の多数のリソースの作成や変更を行う場合があります。対象リソースや条件をシートに記載し、纏めてPowerShellコマンドを作成する際に使用する想定です。

行編集関数:SQL

  • SQLのINSERT文を作成する行編集関数です。
    • 18行目の変数tableのテーブル名を適宜変更してください。シート名から取得する場合、”ActiveSheet.Name”を代入してください。
    • INSERT文に出力する値を編集したい場合、EditSqlVal()の内容を変更してください。
    • EditSqlVal()では、char/varchar/nchar/nvarchar/text等の文字列型、date/time/datetime等の日付・時刻型では引用符付きで値を出力します。

行編集関数:JSON

  • JSONを作成する行編集関数です。
    • 値を編集したい場合、EditJsonVal()の内容を変更してください。
    • フィールド名をケバブケース/スネークケース/パスカルケース/キャメルケース等に変換したい場合は、こちらをご覧ください。

UTF-8による再保存

  • Shift_JISで保存されたファイルをUTF-8(BOMなし)で保存し直す関数です。
    • Windows環境ではない場合、6行目のエンコーディングを実行環境のものに変更してください。
    • BOMを付けて出力したい場合、15行目(“.Position = 3”)をコメントアウトしてください。(他にも冗長な部分がありますが、動作に影響はないので無視します。)