How to insert a new row with datatype constraints by using Microsoft.ACE.OLEDB

satob.hatenablog.com

You can make an Excel file (.xlsx) without Excel by using Microsoft.ACE.OLEDB, but the behavior of INSERT is also a little bit weird. The behavior changes according to the CREATE TABLE is executed in the same connection, the suffix of the sheet name ($) in the INSERT statement, and whether the book was modified manually.

When the CREATE TABLE and INSERT are executed in the same connection

The following results are tested with the following script:

$fileName = "C:\tmp\createtest2.xlsx"
$sheetName = 'record'
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand
$sqlCommand.Connection = $conn
$conn.open()

try {
  try {
    # $sqlCommand.CommandText = "CREATE TABLE [${sheetName}] (CHARVALUE CHAR(4), INTVALUE INTEGER)"
    $sqlCommand.CommandText = "CREATE TABLE [${sheetName}$] (CHARVALUE CHAR(4), INTVALUE INTEGER)"
    $sqlCommand.ExecuteNonQuery()
  } catch {
    $PSItem
  }

  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}$] " + 'VALUES ("1234", 123)'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}$] " + 'VALUES ("1234", "ABC")'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}] " + 'VALUES ("1234", 123)'
  $sqlCommand.CommandText = "INSERT INTO [${sheetName}] " + 'VALUES ("1234", "ABC")'
  $sqlCommand.ExecuteNonQuery()

} finally {
  $conn.close()
}

If the CREATE TABLE statement and INSERT statement are executed in the same connection, the result is:

#	Precondition	INSERT INTO [record$] with valid values	INSERT INTO [record$] with invalid values	INSERT INTO [record] with valid values	INSERT INTO [record] with invalid values
1	The book doesn't exist, the table is created with "CREATE TABLE [record]"	Finished without Error, values inserted	Finished without Error, values inserted	Finished without Error, values inserted	OleDbException (*1)
2	The book already exists and the sheet record doesn't exist, the table is created with "CREATE TABLE [record]"	Finished without Error, values inserted	Finished without Error, values inserted	Finished without Error, values inserted	OleDbException (*1)
3	The book and an empty sheet record have been manually created, the table is created with "CREATE TABLE [record$]"	Finished without Error, values inserted	OleDbException (*1)	OleDbException (*2)	OleDbException (*2)
4	The book and the sheet record have been created and a row has been inserted with OLEDB, and not modified manually. The table is created with "CREATE TABLE [record$]"	Finished without Error, values inserted	OleDbException (*1)	OleDbException (*2)	OleDbException (*2)
5	The book and the sheet record have been created with OLEDB and the book was modified manually(*3). The table is created with "CREATE TABLE [record$]"	Finished without Error, values inserted	OleDbException (*1)	OleDbException (*2)	OleDbException (*2)

(*1)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "抽出条件でデータ型が一致しません。"
発生場所 C:\tmp\CreateTest2.ps1:23 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*2)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "オブジェクト 'record' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'record' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
発生場所 C:\tmp\CreateTest2.ps1:24 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*3) Added a new sheet Sheet2.

When the CREATE TABLE and INSERT are executed in the different connections

The following results are tested with the following script:

$fileName = "C:\tmp\createtest2.xlsx"
$sheetName = 'record'
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand
$sqlCommand.Connection = $conn

function Create-Table () {
  try {
    $conn.open()
    $sqlCommand.CommandText = "CREATE TABLE [${sheetName}] (CHARVALUE CHAR(4), INTVALUE INTEGER)"
    $sqlCommand.ExecuteNonQuery()
  } catch {
    $PSItem
  } finally {
    $conn.close()
  }
}

try {
  # Create-Table

  $conn.open()
  $sqlCommand.CommandText = "INSERT INTO [${sheetName}$] " + 'VALUES ("1234", 123)'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}$] " + 'VALUES ("1234", "ABC")'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}] " + 'VALUES ("1234", 123)'
  # $sqlCommand.CommandText = "INSERT INTO [${sheetName}] " + 'VALUES ("1234", "ABC")'
  $sqlCommand.ExecuteNonQuery()

} finally {
  $conn.close()
}

If the CREATE TABLE statement and INSERT statement are executed in the different connections, the result is:

#	Precondition	INSERT INTO [record$] with valid values	INSERT INTO [record$] with invalid values	INSERT INTO [record] with valid values	INSERT INTO [record] with invalid values
1	The book doesn't exist	OleDbException (*1)	OleDbException (*1)	OleDbException (*2)	OleDbException (*2)
2	The book already exists and the sheet record doesn't exist	OleDbException (*3)	OleDbException (*3)	OleDbException (*4)	OleDbException (*4)
3	The book and an empty sheet record have been manually created	OleDbException (*5)	OleDbException (*5)	OleDbException (*2)	OleDbException (*2)
4	The book and the sheet record have been created with OLEDB and not modified manually	Finished without Error, values inserted	Finished without Error, values inserted	Finished without Error, values inserted	Finished without Error, values inserted
5	The book and the sheet record have been created with OLEDB and the book modified manually(*6)	Finished without Error, values inserted	Finished without Error, values inserted	OleDbException (*2)	OleDbException (*2)

(*1)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "オブジェクト 'record$' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'record$' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*2)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "オブジェクト 'record' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'record' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*3)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "'record$' が見つかりません。パラメーターや別名が正しいこと、無効な文字や区切り記号が含まれていないこと、または名前が長すぎないことを確認してください。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*4)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "オブジェクト 'record' が見つかりませんでした。オブジェクトが存在していること、名前やパス名が正しいことを確認してください。'record' がローカル オブジェクトでない場合は、ネットワークの接続を確認するか、サーバー管理者に問い合わせてください。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*5)

"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "クエリの値と出力するフィールドの数が一致しません。"
発生場所 C:\tmp\CreateTest2.ps1:28 文字:3
+   $sqlCommand.ExecuteNonQuery()
+   ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
    + FullyQualifiedErrorId : OleDbException

(*6) Added a new sheet Sheet2.