How to pass more than 1 value from pipeline?

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"

RelatedIfSolved

Don’t think you fully understand the pipeline. You’re not specifying what to do with a zero. Tell it what parameter to set to false. Try this:

Get-ChildItem -Path $currentDir -Filter *.xlsx | Convert-ExcelSheetsToCsv -$AppendFileName $false

The parameter should be specified for strongly typed code. You can define a position, like this:

    param(
        [Parameter(Mandatory, ValueFromPipelineByPropertyName,Position=1)]
        [ValidateNotNullOrEmpty()]
        [Alias('FullName')]
        [string]$Path,
        [Parameter(Mandatory = $false, ValueFromPipeline, ValueFromPipelineByPropertyName,Position=0)]
        [bool]$AppendFileName
    )

That would work, but it’s always best practice to specify the parameter and not assume position aligns to parameters.

[quote quote=223335][/quote]

Thank you Robe.Sspecifying the parameter didnt work still, complained about the same error i got before “The input object cannot be bound to any parameters …”

but specifying the position worked!

[Parameter(Mandatory = $false, ValueFromPipeline, ValueFromPipelineByPropertyName,Position=0)]

Get-ChildItem -Path $currentDir -Filter *.xlsx | Convert-ExcelSheetsToCsv 0

could i ask you something regarding the excel process occurring? its working great! but here is whats happening:

I have say file1.xlsx that i received from my colleague to parse into this function. I see that it has 7 sheets, but when i run this function, its generating more than 7 csv files, some of them containing the name “OLD_”. Are these hidden sheets probably? or maybe prior versions of the sheets?

How can i make it so that its just generating the sheets i am seeing officially in the file?

excel file sheets im seeing:

xlsx file

csv files generated:

highlighted ones are not supposed to be generated…

highlighted

only the unhighlighted files are the ones im seeing in the excel wb and should be generated only

Explore the object and properties to figure out how to filter:

$excel = New-Object -ComObject Excel.Application -Property @{
    Visible       = $false
    DisplayAlerts = $false
}

$workbook = $excel.Workbooks.Open('C:\Scripts\test.xlsx')
foreach ($worksheet in $workbook.Worksheets) {
    $worksheet
}

Output:

Application                       : System.__ComObject
Creator                           : 1480803660
Parent                            : System.__ComObject
CodeName                          :
_CodeName                         :
Index                             : 1
Name                              : Sheet1
Next                              : System.__ComObject
OnDoubleClick                     :
OnSheetActivate                   :
OnSheetDeactivate                 :
PageSetup                         : System.__ComObject
Previous                          :
ProtectContents                   : False
ProtectDrawingObjects             : False
ProtectionMode                    : False
ProtectScenarios                  : False
Visible                           : -1
Shapes                            : System.__ComObject
TransitionExpEval                 : False
AutoFilterMode                    : False
EnableCalculation                 : True
Cells                             : System.__ComObject
CircularReference                 :
Columns                           : System.__ComObject
ConsolidationFunction             : -4157
ConsolidationOptions              : {False, False, False}
ConsolidationSources              :
DisplayAutomaticPageBreaks        : False
EnableAutoFilter                  : False
EnableSelection                   : 0
EnableOutlining                   : False
EnablePivotTable                  : False
FilterMode                        : False
Names                             : System.__ComObject
OnCalculate                       :
OnData                            :
OnEntry                           :
Outline                           : System.__ComObject
Rows                              : System.__ComObject
ScrollArea                        : 
StandardHeight                    : 14.3
StandardWidth                     : 8.47
TransitionFormEntry               : False
Type                              : -4167
UsedRange                         : System.__ComObject
HPageBreaks                       : System.__ComObject
VPageBreaks                       : System.__ComObject
QueryTables                       : System.__ComObject
DisplayPageBreaks                 : False
Comments                          : System.__ComObject
Hyperlinks                        : System.__ComObject
_DisplayRightToLeft               : False
_AutoFilter                       :
DisplayRightToLeft                : False
Scripts                           : System.__ComObject
Tab                               : System.__ComObject
MailEnvelope                      : System.__ComObject
CustomProperties                  : System.__ComObject
SmartTags                         : System.__ComObject
Protection                        : System.__ComObject
ListObjects                       : System.__ComObject
EnableFormatConditionsCalculation : True
_Sort                             : System.__ComObject
PrintedCommentPages               : 0
CommentsThreaded                  : System.__ComObject
AutoFilter                        :
Sort                              : System.__ComObject
NamedSheetViewCollection

There is a Visible property, -1 is non-hidden and 0 is hidden. You can add this as a parameter if you wanted to turn it on or off, but basically:

foreach ($worksheet in ($workbook.Worksheets | Where{$_.Visible -eq -1})) {
    $worksheet | Select Name, Visible
}

or a parameter would be more like:

foreach ($worksheet in ($workbook.Worksheets)) {
    #Add switch param for exlude hidden
    if ($ExcludeHidden) {
        if ($worksheet.Visible -eq -1) {
            $worksheet
        }
    }
    else {
        $worksheet
    }
}

[quote quote=223386][/quote]

Thank you Rob! This is the code i have now. currently, this generates the csv files nicely, but the files also contain all the data in the sheet. How do i specify to export only the column headers/names without data?

$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, Position=1)]
[ValidateNotNullOrEmpty()]
[Alias('FullName')] #FullName is the property of the Path, so this option 'ValueFromPipelineByPropertyName' must be specified otherwise it will not find the full path required for excel files (i.e. C:\Users\...) and result in an error like this for this line $workbook = $excel.Workbooks.Open($Path): 'Sorry, we couldn't find PLP Flattened Dimensions.xlsx. Is it possible it was moved, renamed or deleted?'. The path its expecting is the full path: (i.e. C:\Users\...)
[string]$Path,
[Parameter(Mandatory = $false, Position=0)]
[bool]$AppendFileName,
[Parameter(Mandatory = $false, Position=2)]
[bool]$ExcludeHiddenSheets
)
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 <# | Where {$_.Visible -eq -1} #>)) {
if ($ExcludeHiddenSheets) {
if ($worksheet.Visible -eq -1) {
$ws = $worksheet
}
}
else {
$ws = $worksheet
}

if ($AppendFileName) {
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "${filename}_$($ws.Name).csv"
}
else {
$name = Join-Path -Path $csvPATH <# $root #> -ChildPath "$($ws.Name).csv"
}

try {
$ws.SaveAs($name, 6) #6 to ignore formatting and convert 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 | Convert-ExcelSheetsToCsv -AppendFileName 0 -ExcludeHiddenSheets 1 #0 for false, so that filename of excel file isnt appended, and only sheet names are the names of the csv files

Really appreciate your help!