How to pass more than 1 value from pipeline?

Welcome Forums General PowerShell Q&A How to pass more than 1 value from pipeline?

Viewing 4 reply threads
  • Author
    Posts
    • #223326
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      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

    • #223335
      Participant
      Topics: 12
      Replies: 1610
      Points: 2,508
      Helping Hand
      Rank: Community Hero

      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.

    • #223350
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      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

      • This reply was modified 4 weeks ago by cataster16.
      • This reply was modified 4 weeks ago by cataster16.
      • This reply was modified 4 weeks ago by cataster16.
      • This reply was modified 4 weeks ago by cataster16.
    • #223386
      Participant
      Topics: 12
      Replies: 1610
      Points: 2,508
      Helping Hand
      Rank: Community Hero

      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
          }
      }
      
    • #223401
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      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!

      • This reply was modified 4 weeks ago by cataster16.
      • This reply was modified 4 weeks ago by cataster16.
      • This reply was modified 4 weeks ago by cataster16.
Viewing 4 reply threads
  • You must be logged in to reply to this topic.