Read from .xlsx in PowerShell (x64) without using Excel

This article shows how to read .xlsx in PowerShell without Excel. In Windows 10 (x64), you will have to do additional work.

• You have to install Microsoft Access Database Engine 2010 Redistributable (AccessDatabaseEngine_X64.exe) when you use Windows PowerShell (x64). You will have to install AccessDatabaseEngine.exe when you use Windows PowerShell (x86).
• You have to use Provider=Microsoft.ACE.OLEDB.12.0 instead of Provider=Microsoft.Jet.OLEDB.4.0.
• You have to use Extended Properties=Excel 12.0 instead of Extended Properties=Excel 8.0.
• ADO can handle unicode characters in .xlsx files. Note that PowerShell ISE, cmd.exe and powershell.exe has some limitations:
  • The console in PowerShell ISE cannot display the characters not included in the default locale.
  • The terminal window for cmd.exe and powershell.exe cannot display the surrogate pairs.

$fileName = "C:\tmp\UnicodeTest.xlsx"
$sheetName = "sheet1$"
$provider = "Provider=Microsoft.ACE.OLEDB.12.0"
$dataSource = "Data Source = $fileName"
$extend = "Extended Properties=Excel 12.0"
$query = "Select * from [$sheetName]"

$conn = New-Object System.Data.OleDb.OleDbConnection("$provider;$dataSource;$extend")
$sqlCommand = New-Object System.Data.OleDb.OleDbCommand($query)
$sqlCommand.Connection = $conn
$conn.open()
$dataReader = $sqlCommand.ExecuteReader()

$list = New-Object System.Collections.ArrayList
While ($dataReader.read())
{
    $list.Add($dataReader[1].ToString()) > $null
}
$list | % { $_ } | Out-File -Encoding UTF8 c:\tmp\UnicodeTest.txt

$dataReader.close()
$conn.close()