I am trying to convert an excel file containing multiple sheets to csv files.
$currentDir = $PSScriptRoot
$csvPATH = Join-Path -Path $currentDir -ChildPath CSV_Files
New-Item -ItemType Directory -Force -Path $csvPATH | out-null
function Convert-ExcelSheetsToCsv {
param(
[Parameter(Mandatory, ValueFromPipelineByPropertyName)]
[ValidateNotNullOrEmpty()]
[Alias('FullName')]
[string]$Path,
[Parameter(Mandatory = $false, ValueFromPipeline, ValueFromPipelineByPropertyName)]
[bool]$AppendFileName
)
Begin {
$excel = New-Object -ComObject Excel.Application -Property @{
Visible = $false
DisplayAlerts = $false
}
}
Process {
$root = Split-Path -Path $Path
$filename = [System.IO.Path]::GetFileNameWithoutExtension($Path)
$workbook = $excel.Workbooks.Open($Path)
foreach ($worksheet in $workbook.Worksheets) {
if ($AppendFileName) {
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "${filename}_$($worksheet.Name).csv"
}
else {
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "$($worksheet.Name).csv"
}
try {
$worksheet.SaveAs($name, 6) #6 to ignore formatting and covert to pure text, otherwise, file could end up containing rubbish
} catch {
Write-Error -Message "Failed to save csv! Path: '$name'. $PSItem"
}
}
}
End {
$excel.Quit()
$null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
}
}
Get-ChildItem -Path $currentDir -Filter *.xlsx 0 | Convert-ExcelSheetsToCsv
This is giving me the following error:
Get-ChildItem : A positional parameter cannot be found that accepts argument '0'.
or if i put the 0 (for false) after like this: Get-ChildItem -Path $currentDir -Filter *.xlsx | Convert-ExcelSheetsToCsv 0
i get this error:
Convert-ExcelSheetsToCsv : The input object cannot be bound to any parameters for the command either because the command does not take pipeline input or the
input and its properties do not match any of the parameters that take pipeline input.
basically, i am trying to have an option where if $AppendFileName
is false, then the generated csv files will only be named by the sheet name, which is this else statement
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "$($worksheet.Name).csv"
Position needs to be specified.
[Parameter(Mandatory, ValueFromPipelineByPropertyName,Position=1)]
[Parameter(Mandatory=$false,ValueFromPipeline,ValueFromPipelineByPropertyName,Position=0)]
and then:
Get-ChildItem -Path $currentDir -Filter *.xlsx | Convert-ExcelSheetsToCsv 0