はじめに
かなり前になりますが、Excel VBAを使ってSQLite3にアクセスするためのマクロを書きました。
おかげさまで、かなり好評でした。
その後転職などを経て、本Webサイトの管理人たるpanda自身が「Excelのファイルに書かれた大量のデータを見る作業」に従事するようになったので、上記の記事を最初に書いた際には試す機会のなかった…
「Excelの行または列からSQLite3のCREATE TABLE文のテンプレートのようなものを出力するExcel VBAのマクロ(またはアドイン)」
を作ってみることにしました。
守備範囲の決定
まず、最初に作る範囲を決めます。
- 行または列の連続するセルを選択すると、そのセルに入力されている文字列がそのままSQLite3のデータベースのカラム名になるCREATE TABLE句が書かれたテキストファイルで、拡張子が「SQL」となるもの(以下、単に「SQLファイル」と書きます。)が保存される。
- カラムのデータ型はSQLファイルの保存の時点までは”text”固定とし、変更が必要な場合はSQLファイルの保存後に手動で行うこととする。
- マクロはExcelファイル間での可搬性を考慮し、アドインとして作成する。
- セルに入力された文字列の先頭と末尾に1文字以上の空白文字が連続して存在する場合には、それらを除去した結果得られる文字列が入力されたとみなす。
- 1個のセルまたは連続するセル(以下、「セル群」と書きます。)を選択した際に、それらのセル群のうち文字列が入力されていないセルがあった場合には、そのセルは選択されていないものとみなす。
- SQLファイルのファイル名及びSQLファイルに出力されるCREATE TABLE句に含まれるテーブル名はシート名をもとに作成する。
- SQLファイルに記述されたSQL文をSQLite3のデータベースに適用する方法は本マクロ(アドイン)内では行わず、CLI等の手段で人力で行う方法とする。人力で行う方法とすることにより、データベースへの適用前にSQLファイルの内容を目視で確認し、データ型の修正等の必要な修正を行う機会を担保する。
- ファイル名がシート名をもとに作成されるため、ファイル名に日本語の文字列が含まれるファイルが作成される可能性がある。日本語の文字列が含まれるファイル名を持つSQLファイルがSQLite3のCLIでは扱うことができない場合には(※WindowsのSQLite3のCLIが該当するようです。)、DB Browser for SQLite[1]などを利用して扱うことにする。
- テーブル名だけでなくカラム名にも日本語の文字列が含まれる可能性があるので、SQLファイルの内部の文字列コードはUTF-8とする。
プログラムの作成
守備範囲が決まったところで、VBAのプログラムとして以下の2個のプログラムを記述します。
ThisWorkbook
ThisWorkbookに記述するプログラムは以下の通りです(Gist側におけるファイル名の拡張子は適当に設定しています。深い意味はありません)。
Worksheet.SheetBeforeRightClick イベントのハンドラとして記述します。
| Option Explicit | |
| Public WithEvents CreateTableStatementForSQLite3 As Application | |
| Private Sub Workbook_Open() | |
| Set CreateTableStatementForSQLite3 = Application | |
| End Sub | |
| Private Sub Workbook_BeforeClose(Cancel As Boolean) | |
| Set CreateTableStatementForSQLite3 = Nothing | |
| End Sub | |
| Private Sub DeleteControls() | |
| On Error Resume Next | |
| Application.CommandBars("Row").Controls("Create文を作る").Delete | |
| Application.CommandBars("Cell").Controls("選択したセルからCreate文を作る").Delete | |
| On Error GoTo 0 | |
| End Sub | |
| Private Sub Workbook_AddinUninstall() | |
| DeleteControls | |
| End Sub | |
| Private Sub CreateTableStatementForSQLite3_SheetBeforeRightClick(ByVal Sh As Object, ByVal Target As Range, Cancel As Boolean) | |
| DeleteControls | |
| With Application.CommandBars("Row").Controls.Add(Before:=1) | |
| .Caption = "Create文を作る" | |
| .OnAction = "Create_CreateClause" | |
| End With | |
| If Target.Rows.Count = 1 Or Target.Columns.Count = 1 Then | |
| With Application.CommandBars("Cell").Controls.Add(Before:=1) | |
| .Caption = "選択したセルからCreate文を作る" | |
| .OnAction = "Create_CreateClauseFromCells" | |
| End With | |
| End If | |
| End Sub |
by 行番号を選択後に、右クリックで表示されるポップアップメニューから本アドインが起動された場合にはCreate_CreateClause関数が、セル群の選択後に右クリックで表示されるポップアップメニューから本アドインが起動された場合には(行方向または列方向の方向に関係なく)Create_CreateClauseFromCells関数がそれぞれ実行されます。
「Visual Basic for Applications」上でアドインのThisWorkbookを開き、上記のコードをそのままコピー&ペーストしています。
3行目のWithEventsキーワードを設定することで、CreateTableStatementForSQLite3変数でApplicationのインスタンスのイベントを取得できるよう設定し、WorkbookがopenされるときにCreateTableStatementForSQLite3変数にApplicationのインスタンスをセットしています。
スポンサーリンク
また、Workbookがcloseされるときとアドインがアンインストールされる場合には追加したポップアップメニューだけを確実に削除するためのサブルーチンを追加しています。
標準モジュール(Module1)
標準モジュールのプログラムは以下の通りです(Gist側におけるファイル名の拡張子は適当に設定しています。深い意味はありません)。
ThisWorkbookで定義したイベントハンドラで記述したCreate_CreateClause関数及びCreate_CreateClauseFromCells関数を実装しています。
| ' See https://pandanote.info/?p=8075 for details. | |
| 'SQLのCreate文を作る。 | |
| Option Explicit | |
| Sub Create_CreateClause() | |
| Dim columnname() As Variant | |
| columnname = Array() | |
| Dim rownum As Integer | |
| rownum = Range(Selection.Address).Row | |
| Dim rightMost As Integer | |
| rightMost = Cells(rownum, Columns.Count).End(xlToLeft).Column | |
| 'MsgBox rightMost | |
| Dim a As Integer | |
| For a = 1 To rightMost | |
| Dim s As String | |
| s = Trim(Cells(rownum, a).Value) | |
| If s <> "" Then | |
| ReDim Preserve columnname(UBound(columnname) + 1) | |
| columnname(UBound(columnname)) = s & " text" | |
| End If | |
| Next | |
| Write_CreateClause columnname | |
| End Sub | |
| '選択したCellからSQLのCreate文を作る。 | |
| Sub Create_CreateClauseFromCells() | |
| Dim columnname() As Variant | |
| columnname = Array() | |
| 'MsgBox UBound(columnname) | |
| Dim r As Range | |
| Set r = Range(Selection.Address) | |
| Dim s As String | |
| If r.Rows.Count = 1 Then | |
| Dim rownum As Integer | |
| rownum = r.Row | |
| Dim a, rightMost As Integer | |
| rightMost = r.Column + r.Columns.Count - 1 | |
| For a = r.Column To rightMost | |
| s = Trim(Cells(rownum, a).Value) | |
| If s <> "" Then | |
| ReDim Preserve columnname(UBound(columnname) + 1) | |
| columnname(UBound(columnname)) = s & " text" | |
| End If | |
| Next | |
| Else | |
| Dim colnum As Integer | |
| colnum = r.Column | |
| Dim lowerMost As Integer | |
| lowerMost = r.Row + r.Rows.Count - 1 | |
| For a = r.Row To lowerMost | |
| s = Trim(Cells(a, colnum).Value) | |
| If s <> "" Then | |
| ReDim Preserve columnname(UBound(columnname) + 1) | |
| columnname(UBound(columnname)) = s & " text" | |
| End If | |
| Next | |
| End If | |
| Write_CreateClause columnname | |
| End Sub | |
| Sub Write_CreateClause(ByVal columnname As Variant) | |
| If UBound(columnname) < 0 Then | |
| MsgBox "データがないため、SQL文を作成できません。", vbExclamation | |
| Exit Sub | |
| End If | |
| Dim sql As String | |
| Dim tablename As String | |
| tablename = ActiveSheet.Name | |
| sql = "create table " & tablename & "(" & Join(columnname, ",") & ");" | |
| ' MsgBox sql | |
| Dim FileName As Variant | |
| FileName = Application.GetSaveAsFilename(InitialFileName:="Create_" & tablename & ".sql", FileFilter:="SQLファイル,*.sql") | |
| If FileName = False Then | |
| Exit Sub | |
| End If | |
| Dim objFso As Object | |
| Set objFso = CreateObject("Scripting.FileSystemObject") | |
| With objFso | |
| If Not .FileExists(FileName) Then | |
| .CreateTextFile (FileName) | |
| End If | |
| Dim byteTmp As Variant | |
| With CreateObject("ADODB.Stream") | |
| .Charset = "UTF-8" | |
| .Open | |
| .WriteText sql | |
| .Position = 0 | |
| .Type = 1 | |
| .Position = 3 | |
| byteTmp = .Read | |
| .Close | |
| .Open | |
| .Write byteTmp | |
| .SetEOS | |
| .SaveToFile FileName, 2 | |
| .Close | |
| End With | |
| End With | |
| Set objFso = Nothing | |
| MsgBox "SQLのCREATE文を作成しました。" | |
| End Sub |
実装上、特に考慮が必要だった点は以下の通りです。
- 最初の実装でははイベントハンドラから選択されたセル群のアドレスを引数して受け取って処理を行う実装としていましたが、これがアドインとして保存したマクロでは動作しませんでした。そこで、Selectionプロパティから選択されたセル群のアドレスを取得するように変更しています(7行目)。この変更に伴い、Create_CreateClause関数及びCreate_CreateClauseFromCells関数に引数として渡されたセル群のアドレスは使用しない実装にいったん変更後、引数自体を削除しています。
- Excelは特に文字コードを指定しないとShift-JISの日本語の文字列を出力してしまいます。そこで、BOMなしのUTF-8に変換して出力していますが、いったんBOMありのUTF-8の文字列に変換してからBOMを削除しています(85-99行目)。
「Visual Basic for Applications」上では以下の手順で標準モジュールの挿入及び記述を行います。
- 標準モジュールを追加したいプロジェクトのアイコンを右クリックします。
- ポップアップメニューが表示されますので、「挿入」→「標準モジュール」を選択します。
- ↓のような画面が表示されますので、上記のプログラムをコピー&ペーストします。(画面表示直後にテンプレート的なコードが挿入されるかもしれませんが、それらのテンプレートは削除します)。
アドインとして保存
プログラムができたところで、アドインとして保存し、いったんExcelを終了します。
動作確認
動作確認の準備
Excelを再度起動し、マクロを有効に設定した新規のExcelファイルに前節で保存したアドインを読み込ませた後、適当な文字列を行及び列方向に入力して、動作確認を行います。
なお、動作確認をするにあたり、
↑のようなテスト用のデータを用意しました。
行を選択する
行を選択した場合の動作確認は以下の手順で行います。
- 前節で作成したテスト用のExcelファイルの4行目の行番号を右クリックします。
- ポップアップメニューの一番上に「Create文を作る」というメニュー(下図の赤矢印)が表示されますので、これを選択します。
- SQLファイルに出力されるCREATE TABLE句がポップアップウィンドウに表示されます(下図)ので、ポップアップウィンドウの「OK」ボタンをクリックします。
- ファイル保存ダイアログが表示されます(下図)ので、ファイル名を確認し、「保存」ボタンをクリックします。
- CREATE文を作成した旨のポップアップウィンドウが表示されます(下図)ので、「OK」ボタンをクリックします。
- お好みで保存したファイルの中身を確認します。以下の確認例ではファイル名が「Create_シート1.sql」になっていることと、ファイル内で使用されている文字コードがUTF-8であることが確認できます。
セルを選択する
セルを選択した場合の動作確認は以下の手順で行います。
セルを縦方向に連続的に選択した場合
セル群を縦方向に連続的に選択後に右クリックすると、ポップアップメニューが表示されますが、その一番上に「選択したセルからCreate文を作る」というメニューが現れます(下図の赤矢印)。
これをクリックすると前節の手順3のポップアップウィンドウが表示されます。以降の手順は前節の手順3以降と同様の手順で動作確認ができます。
セルを横方向に選択した場合
セル群を横方向に連続的に選択後に右クリックすると、ポップアップメニューが表示されますが、その一番上に「選択したセルからCreate文を作る」というメニューが現れます(下図の赤矢印)。
これをクリックすると前々節の手順3のポップアップウィンドウが表示されます。以降の手順は前々節の手順3以降と同様の手順で動作確認ができます。
生成されたSQLファイルの動作確認(Linux Only)
※Windows 10上で、かつファイル名に日本語の文字列が含まれている場合の動作確認につきましては、次節をご参照ください。
動作確認の結果生成されたSQLファイルが動作することをLinux(Fedora)上ではシェルプロンプト及びSQLite3のCLIから以下の例のように確認できます(ファイル名、テーブル名またはカラム名の少なくとも1つに日本語の文字列が含まれている場合でもエラーなく実行できるようです)。
SQLite3の.readコマンドは特にエラーがないと何もメッセージを表示せずに終わってしまいますが、
上記の手順に続けて…
と実行すると、(MariaDB等のような表形式ではありませんが、)テーブル作成時のCREATE TABLE句の内容を見ることができます。
DB Browser for SQLiteでSQLファイルに記述されたSQL文を読み込ませるための手順
Windows 10用のSQLite3ではSQLファイルのファイル名に日本語の文字列が含まれていない場合にはテーブル名またはカラム名に日本語の文字列を含む場合であっても前節の手順で動作確認ができますが、SQLファイルのファイル名に日本語の文字列が含まれている場合には、CLI以外のツールの助けを借りる必要があります。
DB Browser for SQLiteでは、以下の手順でSQLファイルに記述されたSQL文を読み込ませることができます。
- DB Browser for SQLiteを起動します。
- ツールバーの”New Database”または”Open Database”をクリックし、データベースに接続します(“New Database”を選択した場合は、データベースが新規に作成され、作成されたデータベースに接続されます)。
- 画面が以下のように切り替わりますので、「Execute SQL」タブ(下図の赤矢印)をクリックします。
- 画面が以下のように切り替わりますので、「Open SQL file(s)」ボタン(下図の赤矢印)をクリックします。
- ファイル選択ダイアログが開きますので、動作確認の結果生成されたSQLファイルを選択します。
- DB Browser for SQLiteに手順5で選択したファイルが読み込まれ、画面の表示が以下のように切り替わりますので、読み込まれた内容を確認後、「Execute all/selected SQL」ボタンをクリックします。
- 結果表示のためのフィールド(下図の赤矢印)に”Result: query executed successfully.”のようなメッセージが表示されれば実行成功です。
ここまでの手順が完了後に「Database Structure」タブをクリックすると、「シート1」という名前のテーブルが作成されていることを確認することができます(下図)。
まとめ
ここまでの考察で、Excelからカラム名に使えそうな文字列を抽出し、それを使ってSQLite3のデータベースにテーブルを作ることが可能であることが確認できました。
カラム名の選択がExcelのセルの選択の操作でできますので、Excelの表形式やCSV形式で記述されている項目名つきの集計データをSQLite3データベースのデータベースに格納する作業や、Excelの表で記述されたデータベース定義書のようなものから項目名を拾い出して、テーブル作成のためのSQL文を作る作業を効率良く、かつ正確に行うために役立てることができそうです。
#SQLite3 で日本語のカラム名が使えるかどうか試してみたところ、使えるみたいですね。
他のRDBMSでは使えるのかどうかわかりませんが、ローマ字でカラム名を定義するのと比べると可読性は良さそう。#lifeinyokohama pic.twitter.com/t9yQAeU0RS— pandanote.info (@Pandanote_info) October 30, 2021
本Webサイトの管理人たるpanda個人としてはSQLite3自体が日本語の文字列を含むテーブル名及びカラム名の使用が可能であるならば、可読性の観点及びデータベースの構造やデータベースに蓄積されているデータについての理解の促進を図る観点から、日本語の文字列をローマ字に変換した結果得られる文字列を無理に使うべきではないと考えています(テーブル名及びカラム名をどうしてもアルファベットにしたいのであれば、英語とすべきと考えています)。
プログラミングを日本語で行うよりも、データベースのテーブル名やカラム名に日本語の文字列を採用する方がハードルが低いと思うのですが、それさえもなかなかITシステムの設計方針として採用されることがなさそうなので、個人的に細々と試していこうと考えています。
この記事は以上です。
Emoji
on Emacs and Fedora 29 & 30: 絵文字を使いたいと思ったらいろいろと設定しまくることになったので、メモしてみた。 
Windows 10 HomeのEmacs(26.2)からrgrepを実行したときに、ripgrepが起動されるように設定してみた。 
Play framework (2.7.3)を使って構築したWebアプリケーションのカスタムエラーページにmessagesファイルで設定した文字列を表示する。 
Scalaのバージョンを新しくしたら、Metalsの更新も忘れずに行うべきであると信じる件(付録つき)。 