How to stop OLEDB to create "(sheet name)1" sheet automatically

Question

Background

I am writing a PowerShell script to write some data to an Excel file (.xlsx) with Microsoft.ACE.OLEDB like this:

$fileName = "C:\tmp\createtest.xlsx"
$sheetName = "record"
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$ddlSQL = "CREATE TABLE [$sheetName] (ID CHAR(4), NAME VARCHAR(20))"

$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")

$sqlCommand = New-Object System.Data.OleDb.OleDbCommand
$sqlCommand.Connection = $conn
$conn.open()

$sqlCommand.CommandText = $ddlSQL
$sqlCommand.ExecuteNonQuery()
...
$conn.close()

Problem

When you create C:\tmp\createtest.xlsx with an empty sheet named record manually, the CREATE TABLE statement creates the record1 sheet automatically.

I want to stop this behavior and let the CREATE TABLE statement throw an exception like ordinary RDBMS.

Question

Is there any way to stop OLEDB to create the (sheet name)1 sheet automatically when the Excel file has a sheet that has the same name?

Answer 1

I found a solution. Execute CREATE TABLE with a suffix $ for table name:

$ddlSQL = "CREATE TABLE [${sheetName}$] (ID CHAR(4), NAME VARCHAR(20))"

If the book and the sheet record already exists, this statement will be finished without error. If the book or the sheet record doesn't exist, This statement throws OleDbException. Either way, no new sheet will be created, so you can test the existence of the sheet safely.

If you want to use the sheet record regardless of existing sheet and avoid to create record1 sheet automatically, you can do it like this:

$checkExistenceSQL = "CREATE TABLE [${sheetName}$] (ID CHAR(4), NAME VARCHAR(20))"
$ddlSQL = "CREATE TABLE [$sheetName] (ID CHAR(4), NAME VARCHAR(20))"

$conn.open()
try {
  try {
    # Check existing sheet and open if it exists
    $sqlCommand.CommandText = $checkExistenceSQL
    $sqlCommand.ExecuteNonQuery() > $null
  } catch {
    try {
      # Create new sheet if it doesn't exist
      $sqlCommand.CommandText = $ddlSQL
      $sqlCommand.ExecuteNonQuery() > $null
    } catch {
      throw $PSItem
    }
  }

  $insertSQL = "INSERT INTO [${sheetName}$] VALUES (...)"
  $sqlCommand.CommandText = $insertSQL
  $sqlCommand.ExecuteNonQuery() > $null
} finally {
  $conn.close()
}

Note that you have to execute CREATE TABLE first even if the sheet already exists because it affects the effectiveness of the datatype constraint. Also, you have to suffix the table name with $ in the INSERT statement, because it fails if the sheet record already exists. See https://satob.hatenablog.com/entry/2021/11/24/003818 and https://satob.hatenablog.com/entry/2021/11/25/012835 for details.