ADODBでExcelを操作する時の列毎の型判定の基準

Excelでデータ集計をする際には真っ先にSQLでの集計を連想してしまいます。

しかし操作する対象がデータベースではなくExcelなので、思わぬところで蹴躓いてしまいます。

今回はデータの型判定についてです。

データベースの型

Excelでも書式設定等で列毎の型を設定することがありますが、データベースでも同じように型を設定します。

一例として以下のような型が存在します。

型	内容
Integer	整数
Varchar	可変長文字列
Date	日付

データベースでは指定した型以外のデータをその列に入力することが出来ない制約があります。

Ecxelのデータ型とは

Excelシートには書式設定はあってもデータベースのような厳密なデータ型は存在しません。

書式設定で日付を指定していたとしても、数値はもちろん文字を入力することも出来ます。

(データの入力規則で多少は制約をかけることが出来ますが、コピペで…

Excelの型判定

ADODBでExcelをデータベースのように扱う時には、Excelのような曖昧なデータ型ではなくデータベースのように制約を伴う型の判定が行われます。

では緩い制約で1列の中に様々な型が存在するExcelのデータ型判定はどのようにされるのでしょうか。

Excel テーブルは、従来のデータベースとは異なり、列に直接データ型を指定する方法がありません。代わりに、列の中の一定数の行が OLE DB プロバイダによりスキャンされ、そのフィールドのデータ型が推測されます。スキャンされる行数は、デフォルトでは 8 行ですが、接続文字列の拡張プロパティで、MAXSCANROWS 設定に 1 ～ 16 の値を指定することでスキャンされる行数を変更できます。

ADO を使用して Excel ブックのデータの読み取りおよび書き込みを行う方法 (ExcelADO) https://support.microsoft.com/ja-jp/help/278973/excelado-demonstrates-how-to-use-ado-to-read-and-write-data-in-excel-w

ExcelやADODBで直接列毎の型を指定するのではなく、列の最初の８件のデータ内容から自動でデータ型判定されます。

以下のようなデータを用意してテストしてみました。

f:id:ExcelLover:20200201183613j:plain

コードは以下のとおりです。

Option Explicit

Sub DataTypeTest()
     Dim myCon As ADODB.Connection
     Set myCon = New ADODB.Connection
     myCon.Provider = "Microsoft.ACE.OLEDB.12.0"
     myCon.Properties("Extended Properties") = "Excel 12.0;IMEX=1"
     myCon.Open ThisWorkbook.FullName
     
     Dim myRS As ADODB.Recordset
     Set myRS = New ADODB.Recordset
     
     Dim mySQL As String
     mySQL = "select * from [" & wsData.Name & "$]"
     myRS.Open mySQL, myCon
     wsPaste.Range("A1").CopyFromRecordset myRS
End Sub