VisualBasicではADO接続でExcelをデータベースと同じように読み込むことができます。
データベースのように条件を指定することができて便利ですが、データベースとExcelでは違うところがあります。それはExcelは自由にデータ入力ができることです。
AdoでのExcel読み込みはデータ型を自動認識しますが、間違えたデータを入力するとどのように読み込まれるのか？気になりますよね。
そこで実際に有りそうなケースとして、｢日付項目に文字列が入力されたら」どのように読み込まれるか？
実際のプログラムで検証しました。
Visual Basic コーディングサンプル
Visual Basicは以下のようにコーディングしました。
Private Sub okBtn_Click(sender As Object, e As EventArgs) Handles okBtn.Click
Dim cn As ADODB.Connection
Dim sFilePath As String
cn = New ADODB.Connection
sFilePath = "d:\data.xls"
'# 接続情報
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" &
"Data Source=" & sFilePath & ";" &
"Extended Properties=""Excel 12.0;HDR=Yes;"""
'# 接続
cn.Open()
Dim rs As ADODB.Recordset
Dim sSql As String
rs = New ADODB.Recordset
sSql = "select * from [sheet1$]"
rs.Open(sSql, cn, ADODB.CursorTypeEnum.adOpenStatic)
Do Until rs.EOF
Debug.Print(rs!NO.value _
& " " & rs!日付.value _
& " " & rs!コード.value _
& " " & rs!数量.VALUE)
rs.MoveNext()
Loop
rs.Close()
cn.Close()
End Subループ処理中に｢Debug.Print」で出力しています。
テストデータ
以下の簡単なExcelデータを準備しました。
- ｢NO」、｢日付」、｢コード」、｢数量」の４項目
- データは10件
- ３件目と５件目の日付の部分に文字列を入力
実行結果
以下のような実行結果です。
10件のデータを読み込むことはできましたが、日付に文字列を入力した｢3行目」と｢5行目」は正しく表示されていません。
日付が多い列は日付タイプと認識されるため、文字列は認識できないようです。
それで？文字列の場合は何が入ってくるのか
日付タイプの列に文字列が含まれる場合は正しく読めないことが分かりましたが、実際に何が入ってくるのか？
｢Nullか空白文字列」が予測ます。
どちらなのか、プログラムを改造して検証してみます。
改造後のプログラム内容
ループ部分を以下のように変更して、Nullか空白文字列の場合に文字列を設定して出力します。
rs.Open(sSql, cn, ADODB.CursorTypeEnum.adOpenStatic)
Do Until rs.EOF
'空白文字列の場合
If rs!日付.value Is Nothing Then
wStr = "Nothing"
'Nullの場合
ElseIf IsDBNull(rs!日付.value) Then
wStr = "Null"
'それ以外
Else
wStr = " "
End If
Debug.Print(rs!NO.value _
& " " & wStr _
& " " & rs!コード.value _
& " " & rs!数量.VALUE)
rs.MoveNext()
Loop
実行結果
Nullが検出されました。
日付列に文字列を入れるとNullが読み込まれます。
文字列を指定してデータが読めるか確認
SQLのWHERE条件に｢文字列」を指定すると、どのような結果になるのか試してみます。
以下がSQLです。
sSql = "select * from [sheet1$] WHERE 日付 = '緊急'"｢データ型が一致しません」というエラーになりました。
Nullを指定してデータが読めるか確認
SQLのWHERE条件に｢Null」を指定すると、どのような結果になるのか試してみます。
以下がSQLです。
sSql = "select * from [sheet1$] WHERE 日付 IS NULL"日付列に文字列を入れた２件を読むことができますが、もちろん文字列自体は正しく表示されません。
まとめ
ExcelデータをAdoで読むときに、日付に文字列が入った場合の検証を行いました。
日付列に文字列が入っていると正しく読めないため、Nullになります。
それを理解した上でコーディングするか、Excel側で入力規制を行うことが必要です。
