エクセルからmysqlに接続

excel の使い所は種々あるだろうが、その多くは各種DBやログを利用した解析やレポート作成ではないだろうか。それには最初に何らかの方法でデータベースからデータをエクセルまで運んでこなければならない。テキストやCSVをデータベースに見立ててODBC接続する方法なども考えられるが、出来れば直接データベースにアクセスして各種条件で抽出したデータをレポートとして利用したい。そこで、非常に普及している mysql　に ado 接続してみることにした。

ubuntu 上の mysql を他のwindows から参照可能にする。

1. my.cnf のローカルのみ接続を解除する。
   

   #bind-address = 127.0.0.1
   
     

2. 接続許可ユーザとマシンのIPを登録、確認。
   

   mysql> grant all privileges on *.* to ユーザ名@外部ホストIP identified by 'パスワード' with grant option;
   mysql> flush privileges;
   mysql> select user from mysql.user
   
     

3. 　mysql のリスタート

   $ sudo /etc/init.d/mysql restart
   
     

windows 上で mysql 接続の準備をする。

1. 　ドライバを以下よりダウンロードする。
   Download Connector/ODBC


2. 　ユーザDSNに接続定義する。
   

mysql と excel の型を比較する。

1. 　以下のコードで接続する。(エラーを起こす型を取り除いた)

   Sub test01()
       Dim con As ADODB.Connection
       Dim rs  As ADODB.Recordset
       
       Dim cntString As String
       Dim sqlStr As String
       
       Dim rowNo As Integer
       Dim colno As Integer
       Dim item As Variant
       
       cntString = "Driver={MySQL ODBC 5.1 DRIVER};" _
               & "SERVER=192.168.11.100;" _
               & "DATABASE=naoki;" _
               & "USER=naoki;" _
               & "PASSWORD=naoki;"
       
       Set con = New ADODB.Connection
       
       On Error GoTo Err
       
       con.Open cntString
       
       sqlStr = "select col02,col03,col04,col05,col06,col07,col08,col09,col10,col11,col12,col13,col15,col16,col17,col21,col23,col25,col27,col28,col29 from test01"
       
       Set rs = con.Execute(sqlStr)
       
       Worksheets("Sheet2").Cells.Clear
       
       rowNo = 1
       colno = 1
       
       Do While rs.EOF = False
           For Each item In rs.Fields
               Worksheets("Sheet2").Cells(rowNo, colno).Value = item.Value
               colno = colno + 1
           Next
           colno = 1
           rowNo = rowNo + 1
           
           rs.MoveNext
       Loop
       
       con.Close
       Set rs = Nothing
       Set con = Nothing
       
   Exit Sub
   
   Err:
       Set rs = Nothing
       Set con = Nothing
       MsgBox (Err.Description)
   End Sub
     

2. 　データベース設定値と取込み結果の比較
   
   

   タイプ	ＤＢ設定値	エクセル取込値
   BIT(8)	1	×
   TINYINT	127	127
   BOOL	1	1
   SMALLINT	32767	32767
   MEDIUMINT	8388607	8388607
   INT	2147483647	2147483647
   BIGINT	9223372036854775807	9.22337E+18
   FLOAT	3.402823466E+38	3.40282E+38
   DOUBLE	1.7976931348623157E+308	1.7977E+308
   DECIMAL	1	1
   DATE	'9999-12-31'	9999/12/31
   DATETIME	'9999-12-3123:59:59'	9999/12/3123:59
   TIMESTAMP	'2037-12-3123:59:59'	2037/12/3123:59
   TIME	'838:59:59'	×
   YEAR	2155	2155
   CHAR	'a'	a
   VARCHAR(255)	'ab1'	ab1
   BINARY(255),@	'ab2'	×
   VARBINARY(255)	'ab3'	×
   TINYBLOB	'ab4'	×
   TINYTEXT	'ab5'	ab5
   BLOB(255)	'ab6'	×
   TEXT(255)	'ab7'	ab7
   MEDIUMBLOB	'ab8'	×
   MEDIUMTEXT	'ab9'	ab9
   LONGBLOB	'abA'	×
   LONGTEXT	'abB'	abB
   ENUM('a','b','c'),	'c'	c
   SET('e','f','g')	'ef'	e,f