How to ignore hidden excel columns and export only visible ones to csv?

Welcome Forums General PowerShell Q&A How to ignore hidden excel columns and export only visible ones to csv?

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

      Ok, so there are threads like this to export only visible rows to csv, but how about columns? do i need a loop? or can it be done as easily as visible worksheets?

      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 #> $_.Name -ne 'Security' -and $_.Name -ne 'Notes' })) {
      <# WIP if($ExcludeHiddenColumns) { if ($worksheet.Column.Visible -eq -1) { $worksheet.sheets.columns.entirecolumn.hidden=$true } } #>
      
      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"
      }
      }
      }
      

      Particularly, this is the part im working on to tell the script to export only the visible columns if specified by the boolean param $ExcludeHiddenColumns, but not sure the best way to approach this..

       <# WIP
      if($ExcludeHiddenColumns) {
      if ($worksheet.Column.Visible -eq -1) {
      $worksheet.sheets.columns.entirecolumn.hidden=$true
      }
      }

      related

      • This topic was modified 3 weeks, 5 days ago by cataster16.
    • #223923
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      I have to ask, why work on the CSV through Excel com object instead of directly with it in powershell? It just complicates it so if it’s not a requirment, I recommend working natively with CSV.

      • This reply was modified 3 weeks, 5 days ago by Doug Maurer. Reason: Had Outlook on the brain apparently
    • #223926
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      I have to ask, why work on the CSV through outlook com object instead of directly with it in powershell? It just complicates it so if it’s not a requirment, I recommend working natively with CSV.

      Good question. Its because the input is an excel file, so my task now is to convert it to csv, given the above code, it works great! I just wish it doesnt include the hidden columns

    • #223932
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      Well you could save the excel file to csv and then easily filter the columns out that shouldn’t be there. But, I’m sure there is a way to do what you are wanting. If someone hasn’t answered when I get some time, I will see what I can find!

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

      Well you could save the excel file to csv and then easily filter the columns out that shouldn’t be there. But, I’m sure there is a way to do what you are wanting. If someone hasn’t answered when I get some time, I will see what I can find!

      Sounds good, thanks!

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

      Well you could save the excel file to csv and then easily filter the columns out that shouldn’t be there. But, I’m sure there is a way to do what you are wanting. If someone hasn’t answered when I get some time, I will see what I can find!

      After a lot of trial and error, i figured it out!

      foreach ($worksheet in ($workbook.Worksheets | Where { <# $_.Visible -eq -1 #> $_.Name -ne 'Security' -and $_.Name -ne 'Notes' })) {        
          if($ExcludeHiddenColumns) {
              $ColumnsCount = $worksheet.UsedRange.Columns.Count
              for ($i=1; $i -le $ColumnsCount; $i++)
              {
                  $column = $worksheet.Columns.Item($i).EntireColumn #$worksheet.sheets.columns.entirecolumn.hidden=$true
                  if ($column.hidden -eq $true)
                  {
                      $column.Delete()
                      #$i = $i - 1
                  }
              }
          }
      }

       

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

      Well you could save the excel file to csv and then easily filter the columns out that shouldn’t be there. But, I’m sure there is a way to do what you are wanting. If someone hasn’t answered when I get some time, I will see what I can find!

      Sounds good, thanks!

      Now that i resolved that issue, the question is: how do i print the column name being deleted? i tried the following but its not printing out anything

      $($worksheet.Columns.Item($i).EntireColumn.Name)

      or $column.Name

      or $worksheet.Columns.Item($i).Name

    • #224040
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      I got the list of hidden column headers like this.

      $hiddencolumnheaders = foreach($column in $worksheet.UsedRange.Columns){
          if($column.hidden -eq $true){
              $column.value2 | select -first 1
          }
      }
      
    • #224043
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      <p style=”padding-left: 40px;”>

      I got the list of hidden column headers like this.

      </p>

      PowerShell
      6 lines

      <textarea class=”ace_text-input” style=”opacity: 0; height: 17.9048px; width: 7.2381px; left: 45px; top: 0px;” spellcheck=”false” wrap=”off”></textarea>

      1
      2
      3
      4
      5
      6
      $hiddencolumnheaders = foreach($column in $worksheet.UsedRange.Columns){
      if($column.hidden -eq $true){
      $column.value2 | select -first 1
      }
      }
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      <p style=”padding-left: 40px;”>

      Oh interesting approach! Is value2 a method you found on MS docs?</p>
      Also, how can I print thr $column name?

    • #224055
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      No I found it from just using get-member on the worksheet and sub properties/methods

      
      $worksheet | gm
      
      $worksheet.someproperty | gm
      
      

      I’m not sure in the context you’re in how to print the name.

    • #224061
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      How about this?

      foreach ($worksheet in ($workbook.Worksheets | Where { <# $_.Visible -eq -1 #> $_.Name -ne 'Security' -and $_.Name -ne 'Notes' })) {
          if($ExcludeHiddenColumns) {
              $ColumnsCount = $worksheet.UsedRange.Columns.Count
              for ($i=1; $i -le $ColumnsCount; $i++)
              {
                  $column = $worksheet.Columns.Item($i).EntireColumn #$worksheet.sheets.columns.entirecolumn.hidden=$true
                  if ($column.hidden -eq $true)
                  {
                      $columnname = $column.cells.item(1,$i).value2
                      "Column {0} was deleted!" -f $columnname
                      $column.Delete()
                      #$i = $i - 1
                  }
              }
          }
      }
      
      • This reply was modified 3 weeks, 5 days ago by Doug Maurer.
    • #224196
      Participant
      Topics: 39
      Replies: 108
      Points: 620
      Rank: Major Contributor

      this works! but you should edit it to (1,$i) because it threw error for the period (1.$i)

      Unexpected token ‘1.$i’ in expression or statement.

      btw, i tried the other code

      $hiddencolumnheaders = foreach($column in $worksheet.UsedRange.Columns){
      if($column.hidden -eq $true){
      $column.value2 | select -first 1
      }
      }

      and it didnt do anything…nothing was selected and it still exported the hidden columns to the csv file generated…only the code i posted seems to work/output something…

    • #224298
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      Yes, that should be a comma, good catch! In the last code you said you tested, all it should’ve done was capture the column names of hidden columns in the $hiddencolumns variable. It doesn’t output anything.

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

      Yes, that should be a comma, good catch! In the last code you said you tested, all it should’ve done was capture the column names of hidden columns in the $hiddencolumns variable. It doesn’t output anything.

      hey Doug

      its been a while and thanks for your past help 🙂

      i have a new thread related to this, would you kindly check it out? Since youve helped me out with this program, i figured i’d ping you about it since this thread is related to it.

      https://powershell.org/forums/topic/how-to-skip-export-of-empty-rows/

    • #231151
      Participant
      Topics: 3
      Replies: 324
      Points: 1,056
      Helping Hand
      Rank: Community Hero

      You may have to add me to payroll soon.

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

      You may have to add me to payroll soon.

      hahaha yessir

Viewing 15 reply threads
  • You must be logged in to reply to this topic.