【VB.NET】ADODBを使ってExcelデータをSQL操作してみた

前記述で紹介したツール

【実績】WEBからデータ(Excel)ダウンロードと加工まで自動化

の開発の際
Excel操作をADODBで行った際
つまずいた事を備忘録として残しておきます！

MEMO

開発環境
Windows10 64bit
VisualStudio2019

VisualStudioでの参照設定

ADODBが使える様にまず参照設定しておく！

ソリューションエクスプローラーの「参照」を右クリック
「参照の追加」から参照マネージャーを開く。

「COM」から
①Microsoft ActiveX Data Objects 6.1 Library
②Microsoft OLEDB Service Component 1.0 Type Library

にチェックを入れて設定しておく！

ADODBでExcel操作

ADODBでExcelに接続

とりあえず操作したいExcelに接続する！
基本的なコードはこんな感じ。

Dim cn As ADODB.Connection
Dim sFilePath As String

cn = New ADODB.Connection
sFilePath = "対象ファイルのフルパス"

'# 接続情報
cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" &
                      "Data Source=" & sFilePath & ";" &
                      "Extended Properties=""Excel 12.0;HDR=Yes;"""

'#　接続
cn.Open()

Dim cn As ADODB.Connection

Dim sFilePath As String

cn = New ADODB.Connection

sFilePath = "対象ファイルのフルパス"

'# 接続情報

cn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;" &

"Data Source=" & sFilePath & ";" &

"Extended Properties=""Excel 12.0;HDR=Yes;"""

'#　接続

cn.Open()

■Provider

Microsoft.ACE.OLEDB.12.0

これはOffice32bit、64bitどちらも対応しているそうです！

もうひとつ
Microsoft.Jet.OLEDB.4.0
これは32bitのみ対応だそうです。

■Data Source

ここは操作したいファイルのフルパスを指定。

■Extended Properties

Excel 12.0

これはファイルの種類を指定。
Excel2007以降は12.0。

HDR=Yes

読み込むファイルの1行目を
ヘッダー項目として読み込むYesかNoか。
ここではYesで設定。

SQLでExcelを操作

接続設定が済んだら
操作したいSQL文を作って実行！
実行結果をレコードセットに返します。

Dim rs As ADODB.Recordset
Dim sSql As String

rs = New ADODB.Recordset
'# SQL生成
sSql = "SQL文"

rs.Open(sSql, cn, adOpenDynamic, adLockOptimistic, adCmdText)

Dim rs As ADODB.Recordset

Dim sSql As String

rs = New ADODB.Recordset

'# SQL生成

sSql = "SQL文"

rs.Open(sSql, cn, adOpenDynamic, adLockOptimistic, adCmdText)

合わせて読みたい記事

SQLを使ったCSVへのINSERT処理はADOよりDAOが高速！

【VBA】ADODB接続中のExcelシートの情報をADOXで取得！

SQLでの日付は#で囲む

SQLのWHERE句で日付の範囲指定をする際
日付を#で囲まないとうまく処理できませんでした！

例えば

WHERE [日付] = ‘2019/08/11’

ではなく

WHERE [日付] = ‘#2019/08/11#‘

とこんな感じです！

WHERE句のダミー

SQL文を作る中でWHERE句のダミーが必要でした！

WHERE 1

これで解決！

例えば条件分岐してSQL文を作るとします。

条件が以下３つあるとします。
①日付指定
②ID指定
③文字列指定

そしてSQL文のパターンが以下３つあるとします。
(1) パターン1：WHERE ① AND ②
(2) パターン2：WHERE ① AND ② AND ③
(3) パターン2：WHERE ② AND ③

問題は(3)のせいでそろえて書けない！

WHERE句の初めは①を指定して
それ以降は“AND 条件文”でそろえて作りたい！

ここで(3)のWHERE句の初めを「WHERE 1」とし
その後”AND 条件文”で②と③を指定すればOK！

adUseClientの指定

SQL文を実行しレコードセットに返す際
これを指定してないとうまく処理しませんでした！

実行結果のデータを
クライアント(ローカル)側にしっかり返すために
以下の様に指定した後に実行結果を受け取りましょう！

Dim rs As ADODB.Recordset
Dim sSql As String

rs = New ADODB.Recordset
'# SQL生成
sSql = "SQL文"

'# ↓ここでクライアント側を指定
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient

rs.Open(sSql, cn, adOpenDynamic, adLockOptimistic, adCmdText)