Error in Powershell Trying to Convert Excel Spreadsheet .xlsx to .csv

Hello,

I am tasked with creating a Powershell script that will run via a SQL Server Agent Job (SQL Server 2014). The script will convert all Excel spreadsheets in a folder from from .xlsx to .csv. The script is erroring out.

Below is both the raw script as well as the run output with the error. If someone could point me in the right direction I would greatly appreciate it.

Thanks in advance.

Robert

Error:

ForEach-Object : Unable to find type [Microsoft.Office.Interop.Excel.XlFileFormat]. Make sure that the assembly that
contains this type is loaded.
At line:11 char:15
+ $ExcelFiles | ForEach-Object {
+               ~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XlFileFormat:TypeName) [ForEach-Object], Runtime
   Exception
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.ForEachObjectCommand

Script:

$ErrorActionPreference = 'Stop'

Function Convert-CsvInBatch
{
	[CmdletBinding()]
	Param
	(
		[Parameter(Mandatory=$true)][String]$Folder
	)
	$ExcelFiles = Get-ChildItem -Path $Folder -Filter *.xlsx -Recurse

	$excelApp = New-Object -ComObject Excel.Application
	$excelApp.DisplayAlerts = $false

	$ExcelFiles | ForEach-Object {
		$workbook = $excelApp.Workbooks.Open($_.FullName)
		$csvFilePath = $_.FullName -replace "\.xlsx$", ".csv"
		$workbook.SaveAs($csvFilePath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV)
		$workbook.Close()
	}

	# Release Excel Com Object resource
	$excelApp.Workbooks.Close()
	$excelApp.Visible = $true
	Start-Sleep 5
	$excelApp.Quit()
	[System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApp) | Out-Null
}

Select allOpen in new window

#
# 0. Prepare the folder path which contains all excel files
$FolderPath = "C:\Users\username\Downloads\TEST"

Convert-CsvInBatch -Folder $FolderPath

Output with Error:

Windows PowerShell
Copyright (C) 2014 Microsoft Corporation. All rights reserved.

PS C:\Users\username> $ErrorActionPreference = 'Stop'
PS C:\Users\username>
PS C:\Users\username> Function Convert-CsvInBatch
>> {
>> [CmdletBinding()]
>> Param
>> (
>> [Parameter(Mandatory=$true)][String]$Folder
>> )
>> $ExcelFiles = Get-ChildItem -Path $Folder -Filter *.xlsx -Recurse
>>
>> $excelApp = New-Object -ComObject Excel.Application
>> $excelApp.DisplayAlerts = $false
>>
>> $ExcelFiles | ForEach-Object {
>> $workbook = $excelApp.Workbooks.Open($_.FullName)
>> $csvFilePath = $_.FullName -replace "\.xlsx$", ".csv"
>> $workbook.SaveAs($csvFilePath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV)
>> $workbook.Close()
>> }
>>
>> # Release Excel Com Object resource
>> $excelApp.Workbooks.Close()
>> $excelApp.Visible = $true
>> Start-Sleep 5
>> $excelApp.Quit()
>> [System.Runtime.Interopservices.Marshal]::ReleaseComObject($excelApp) | Out-Null
>> }
>>
PS C:\Users\username> #
PS C:\Users\username> # 0. Prepare the folder path which contains all excel files
PS C:\Users\username> $FolderPath = "C:\Users\username\Downloads\TEST"
PS C:\Users\username>
PS C:\Users\username> Convert-CsvInBatch -Folder $FolderPath
ForEach-Object : Unable to find type [Microsoft.Office.Interop.Excel.XlFileFormat]. Make sure that the assembly that
contains this type is loaded.
At line:11 char:15
+ $ExcelFiles | ForEach-Object {
+               ~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (Microsoft.Offic...el.XlFileFormat:TypeName) [ForEach-Object], Runtime
   Exception
    + FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.ForEachObjectCommand