You can make an Excel file (.xlsx
) without Excel by using Microsoft.ACE.OLEDB
, but the behavior of CREATE TABLE
is a little bit weird. The behavior changes according to the name of the sheet, suffix of the sheet name ($
) in the CREATE TABLE
statement, the existence of the specified sheet, and whether the book was modified manually.
The following results are tested with the following script:
$fileName = "C:\tmp\createtest3.xlsx" # $sheetName = 'record' $sheetName = 'sheet1' $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 } } finally { $conn.close() }
When the sheet name is not sheet1
If the sheet name is not sheet1
(record
in the example above), the result is:
# | Precondition | CREATE TABLE [record$] |
CREATE TABLE [record] |
---|---|---|---|
1 | The book doesn't exist | OleDbException (*1) | Finished without Error, new sheet record is created |
2 | The book already exists and the sheet record doesn't exist |
OleDbException (*1) | Finished without Error, new sheet record is created |
3 | The book and an empty sheet record have been manually created |
Finished without Error, no new sheet created | Finished without Error, new sheet record1 is created |
4 | The book and the sheet record have been created with OLEDB and not modified manually |
Finished without Error, no new sheet created | OleDbException (*2) |
5 | The book and the sheet record have been created with OLEDB and the book modified manually(*3) |
Finished without Error, no new sheet created | Finished without Error, new sheet record1 is created |
(*1)
"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "'record$' が見つかりません。パラメーターや別名が正しいこと、無効な文字や区切り記号が含まれていないこと、または名前が長すぎないことを確認してください。" 発生場所 C:\tmp\CreateTest3.ps1:14 文字:5 + $sqlCommand.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : OleDbException
(*2)
"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "テーブル 'record' は既に存在しています。" 発生場所 C:\tmp\CreateTest3.ps1:15 文字:5 + $sqlCommand.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : OleDbException
(*3) Added a new sheet Sheet2
.
When the sheet name is sheet1
If the sheet name is sheet1
, the result is:
# | Precondition | CREATE TABLE [sheet1$] |
CREATE TABLE [sheet1] |
---|---|---|---|
1 | The book doesn't exist | OleDbException (*4) | Finished without Error, new sheet sheet1 - 1 is created |
2 | The book already exists but the sheet sheet1 doesn't exist |
OleDbException (*4) | Finished without Error, new sheet sheet1 is created |
3 | The book and an empty sheet sheet1 have been manually created |
Finished without Error, no new sheet created | Finished without Error, new sheet sheet11 is created |
4 | The book and the sheet sheet1 have been created/written with OLEDB and not modified manually |
Finished without Error, no new sheet created | OleDbException (*5) |
5 | The book and the sheet sheet1 - 1 have been created with OLEDB and not modified manually |
Finished without Error, no new sheet created | OleDbException (*5) |
6 | The book and the sheet sheet1 have been created/written with OLEDB and the book modified manually(*6) |
Finished without Error, no new sheet created | OleDbException (*5) |
7 | The book and the sheet sheet1 - 1 have been created with OLEDB and the book modified manually(*6) |
OleDbException (*4) | Finished without Error, new sheet sheet1 is created |
(*4)
"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "'sheet1$' が見つかりません。パラメーターや別名が正しいこと、無効な文字や区切り記号が含まれていないこと、または名前が長すぎないことを確認してください。" 発生場所 C:\tmp\CreateTest3.ps1:16 文字:5 + $sqlCommand.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : OleDbException
(*5)
"0" 個の引数を指定して "ExecuteNonQuery" を呼び出し中に例外が発生しました: "テーブル 'sheet1' は既に存在しています。" 発生場所 C:\tmp\CreateTest3.ps1:16 文字:5 + $sqlCommand.ExecuteNonQuery() + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : NotSpecified: (:) [], MethodInvocationException + FullyQualifiedErrorId : OleDbException
(*6) Added a new sheet Sheet2
.