PostgreSQLにExcelからアクセスしてみた

データベースがソフトウェアから使えると、自動化の可能性が広がります。
今回、PostgreSQLExcelからアクセスし、SELECT文を実行してみましたので、覚書として記しておきます。接続にはADODBを使いました。

PostgreSQLのインストール

データベースには、PostgreSQLを選びました。フリーなリレーショナルデータベース(RDB)だからです。
まずはPCにインストールします。あまり細かく書いても、すぐに変わってしまう可能性があるので、概要のみ示します。

  1. PostgreSQL Database Downloadに接続する
  2. 使用しているPCのOSを考慮して、Versionを選ぶ
    → 特別なことがない限り、Versionは数字の大きい新しいバージョンを選ぶ
    → Windowsの場合、Windows x86-64は64ビット用、Windows x86-32は32ビット用
    → 自分のPCのOSやビット数が分からない場合は、デスクトップのPCアイコンを右クリックしてプロパティを選択し、開いたウィンドウを参照する
  3. Downloadボタンをクリックするとダウンロードが始まる
  4. ダウンロードフォルダに「postgresql-13.2-1-windows-x64.exe」などといったファイルが作成される
  5. 念のためウィルス検出ソフトなどでウィルスの無いことを確認してから、ダブルクリックして実行
    → パスワード以外はほぼ全て「Next」をクリック

ODBCのインストール

続いて、ODBC(Open Database Connectivity)をインストールします。
ODBCは、PostgreSQLとExcelの仲立ちになるソフトです。

  1. ODBCのダウンロードサイトに接続する
  2. バージョンとビット数を確認してダウンロードする
    → 僕はWindows 64ビットで、PostgreSQLのバージョン13.2をダウンロードしたので、「psqlodbc_13_00_0000-x86.zip」を選択
  3. 念のためウィルス検出ソフトなどでウィルスの無いことを確認してからzipを展開
  4. 展開されたmsiファイルをダブルクリックしてインストール
    → 途中、かなり強いセキュリティ警告表示が出現
    → 気持ち悪かったが、ウィルス検出ソフトを信じた

データベースを作り、データを入れる

データベースを作る

PostgreSQLをインストールすると、postgresというデータベースが既にできているそうですので、この「データベースを作る」作業は必ずしも必要ではありません。

データベースを作る

データベースを作る

  1. スタート → ProgreSQL 13 → SQL Shell (psql)
  2. パスワード以外はEnterでログインする
  3. CREATE DATABASEコマンドで任意のデータベースを作る(ここではmisc)
  4. 「\q」でいったん抜ける

テーブルを作ってデータを入れる

普通は、テーブルの中にスキーマを作り、その中にテーブルを作るようです。
そこまで考えずにテーブルを作ったら、デフォルトのスキーマである「public」の中にテーブルができました。今回は、この設定で使います。

テーブルを作ってデータを入れる

テーブルを作ってデータを入れる

    1. スタート → ProgreSQL 13 → SQL Shell (psql)
    2. 「Database[postgres]:」の欄をmiscとしてログイン
    3. 以下のSQLを流して、テストテーブルを作ってみる
      CREATE TABLE usage_record(
          no SERIAL PRIMARY KEY,
          id VARCHAR(16) NOT NULL,
          ct TIMESTAMP DEFAULT CURRENT_TIMESTAMP
      );
    4. テストレコードを追加する
      INSERT INTO usage_record (no, id) VALUES (1, 'MISC1');

ODBCの設定

作成したデータベースにODBCで接続できるように設定します。

    1. コントロールパネル → 管理ツール → ODBC データ ソース (64 ビット) → ユーザーDNSタブ → 追加

      ODBCデータソースアドミニストレーター

      ODBCデータソースアドミニストレーター

    2. PostgreSQL ANSI(x64)→完了
      ※ SQL Shell (psql)で、Client Encoding [SJIS]となっていたため

      PostgreSQL ANSI ODBCセットアップ

      PostgreSQL ANSI ODBCセットアップ

    3. データベース名(ここではmisc)、ユーザ名、パスワードを設定して「テスト」
    4. Connection successfulを確認 → OK → 保存 → OK

ExcelからPostgreSQLにアクセスする

Excelにマクロを設定し、PostgreSQLにアクセスします。

Excelにマクロを設定する

  1. Excelを立ち上げる
  2. 開発 → Visual Basic → プロジェクトエクスプローラ → VBAProject(Book1)を右クリック → 挿入 → ユーザーフォーム
  3. フォームのオブジェクト名を「FrmPostgreSQL」とする
  4. FrmPostgreSQLにチェックボックスとテキストボックスを1つずつ配置する

    フォーム

    フォーム

  5. チェックボックスのオブジェクト名を「chkIsString」とする
  6. テキストボックスのオブジェクト名を「txtSQL」とする
  7. テキストボックスのMultiLine属性を「True」にする
  8. 「F7」キーを押してフォームのエディタを表示し、次のコードを貼り付ける
    ※「パスワード」は自分で設定したパスワードに変更のこと

    Option Explicit
    
    '-----------------------------------------------------------------------
    ' Summary : txtSQLテキストボックスでキーを押したときの処理
    ' Caution : 参照設定からMicrosoft ActiveX Data Objects x.x Libraryを選択
    '-----------------------------------------------------------------------
    Private Sub txtSQL_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, _
            ByVal Shift As Integer)
        ' エラーメッセージ
        Dim strErr As String
        
        ' キーコードによって分岐
        Select Case KeyCode
            ' Enterキーの場合
            Case &HD
                ' ADODBを使用するための変数宣言
                Dim cnn As New ADODB.Connection
                Dim rec As ADODB.Recordset
                
                ' エラーが発生しても次の処理に移る
                On Error Resume Next
                ' 設定したODBCデータソースを使ってDBを開く
                Call cnn.Open("DSN=PostgreSQL30;" & _
                        "UID=postgres;PWD=パスワード;Database=misc")
                ' エラーチェック
                If Err.Number <> 0 Then
                    strErr = Err.Description
                    GoTo PostHandler
                End If
                ' テキストボックスに記されたSQLを内部変数に保持する
                Dim strSQL As String
                strSQL = Me.txtSQL.Text
                ' SQLを実行する
                Set rec = cnn.Execute(strSQL)
                ' エラーチェック
                If Err.Number <> 0 Then
                    strErr = Err.Description
                    GoTo PostHandler
                End If
                ' エラー処理を無効にする
                On Error GoTo 0
                
                ' Recordsetの状態を確認
                If rec.State <> adStateClosed Then
                    ' 開いている場合には、先ず、シートをクリア
                    Cells.Clear
                    ' カラム名を表示
                    Dim j As Long
                    For j = 1 To rec.Fields.Count
                        Cells(1, j) = rec.Fields(j - 1).Name
                    Next j
                    ' 文字列チェックボックスの状態を確認
                    If Me.chkIsString Then
                        ' チェックのある場合はシート全体を文字列書式にする
                        Cells.NumberFormatLocal = "@"
                        ' 行番号用の変数を準備
                        Dim i As Integer
                        i = 2
                        ' Recordsetの最後までループ
                        Do While rec.EOF = False
                            ' フィールドに値をセット
                            For j = 1 To rec.Fields.Count
                                Cells(i, j).Value = "'" & _
                                        rec.Fields(j - 1).Value
                            Next j
                            ' 次のレコードに移る
                            rec.MoveNext
                            ' 次の行に移る
                            i = i + 1
                        Loop
                    Else
                        ' シートに一括で入力する
                        Call Range("A2").CopyFromRecordset(rec)
                    End If
                    ' 列幅を整える
                    Columns("A:" & Split( _
                            Columns(rec.Fields.Count).Address, "$")(2)) _
                            .EntireColumn.AutoFit
                Else
                    ' Recordsetが閉じている場合はDelete等が成功している筈
                    Call MsgBox("成功しました", vbOKOnly + vbInformation, _
                            "txtSQLKeyDown")
                End If
            Case Else
        End Select
        
    PostHandler:
        ' ADODBオブジェクトが開いている場合は閉じる
        If rec Is Nothing = False Then _
                If rec.State <> adStateClosed Then rec.Close
        Set rec = Nothing
        If cnn Is Nothing = False Then _
                If cnn.State <> adStateClosed Then cnn.Close
        Set cnn = Nothing
        If strErr <> "" Then
            ' エラーの場合はエラーの内容を表示する
            Call MsgBox(Err.Description, vbOKOnly + vbCritical, "txtSQLKeyDown")
        End If
    End Sub
    
    '-----------------------------------------------------------------------
    ' Summary : フォームの初期化
    '-----------------------------------------------------------------------
    Private Sub UserForm_Initialize()
        ' キャプションの設定
        Me.Caption = "FrmPostgreSQL"
        ' 取得データを文字列に強制的に変換はしない
        Me.chkIsString.Value = False
    End Sub
  9. プロジェクトエクスプローラ → VBAProject(Book1)を右クリック → 挿入 → 標準モジュール
  10. 次のコードを貼り付ける
    Option Explicit
    
    '-----------------------------------------------------------------------
    ' Summary : FrmPostgreSQLフォームをモードレスで開く
    '-----------------------------------------------------------------------
    Public Sub UseFrmPostgreSQL()
        ' FrmPostgreSQLフォームをモードレスで開く
        FrmPostgreSQL.Show (vbModeless)
    End Sub
  11. ツール → 参照設定 →「Microsoft ActiveX Data Objects x.x Library」を選択
    ※ 僕は6.1 Libraryを選択
  12. デバッグ →「VBAProjectのコンパイル」を選び、エラーの無いことを確認
  13. 適当な名前を付けて、拡張子を「xlsm」で保存

ExcelからPostgreSQLにアクセスする

いよいよPostgreSQLにアクセスします

  1. 前節で保存した「xlsm」シートを開く
  2. マクロ →  UseFrmPostgreSQL → 実行
  3. 例では、「usage_record」というテーブルを作ったので、テキストボックスに下記コマンドを打って「Enter」
    select * from usage_record order by no
SQL実行結果

SQL実行結果

データが取れれば成功です。delete文やInsert文も使えますので、試してみてください。

余談:Excel自体をデータベースとして使ってはいけないのか?

Excelをデータベースとして使うことができます。
しかし、次のような問題点があります。

  1. (ADODBでは?)複雑なSQLが使えない場合がある
  2. (ADODBでは?)delete文が使えない
  3. (僕のPCのExcelでは)1,048,576行までしか使えない

10年ほど前は、Excelシートに対してADODBを使い、SQLで検索をかけたりもしていました。しかし、ちょっと複雑なSQLを投げると、すぐに「そんな複雑なSQLは使えません」というようなエラーが返ってきた覚えがあります。久しく使っていないので、もしかすると、現在は改善されたかもしれません。

また、ADODBでは、delete文が使えないようです。

更に、僕のPCでは、Excelの最大行数が1,048,576行です。これは、一日1,000レコード書き込めば、3年持たない計算です。多いようで少ない行数です。

これらの問題点が致命的でなければ、Excelをデータベースとして使うことができると考えます。でも、ADODBを使うより、普通にVBAでアクセスする方が簡単だと思います。

まとめ

ExcelからPostgreSQLにアクセスするために、PostgreSQLのインストールからの手順を示しました。

Select文で検索した結果がExcelシートにコピーされると、解析する際に便利です。このExcelマクロを改造して、いろいろなデータベースに接続できるようにしたり、SQLの履歴を残したりするなど、使い勝手を良くしてご活用ください。

コメント

タイトルとURLをコピーしました