Why is this function skipping every other column?

Welcome Forums General PowerShell Q&A Why is this function skipping every other column?

Viewing 8 reply threads
  • Author
    Posts
    • #227107
      Participant
      Topics: 39
      Replies: 109
      Points: 623
      Rank: Major Contributor

      I am importing an xlsx file, renaming the columns by adding a ‘_sfx’ to the end, and exporting the sheet to a csv file of its own. The intention is not necessarily to modify the xlsx file, just the csv file getting generated from this xlsx file.

      example xlsx sheet file columns:

      Total MYTrim,Model Year,MY Trim,MYTrim Code

      I am noticing the output is working on just 2 columns, and the rest are being skipped/not recognized somehow

      Renaming column [Total MYTrim] in [MYTrim] worksheet…

      Column [Total MYTrim] was Renamed to [Total MYTrim__sfx] ! Processing further columns (if any)…

      Renaming column [MY Trim] in [MYTrim] worksheet…

      Column [MY Trim] was Renamed to [MY Trim__sfx] ! Processing further columns (if any)…

      Renaming column [] in [MYTrim] worksheet…

      Column [] was Renamed to [__sfx] ! Processing further columns (if any)…

      Renaming column [] in [MYTrim] worksheet…

      Column [] was Renamed to [__sfx] ! Processing further columns (if any)…

      Proceeding with Export of [MYTrim] worksheet to CSV operation…

      The expected csv file columns:

      Total MYTrim_sfx,Model Year_sfx,MY Trim_sfx,MYTrim Code_sfx

      This is the generated csv file columns:

      Total MYTrim__sfx,Model Year,MY Trim__sfx,MYTrim Code,__sfx,,__sfx

      here is the code:

      $columnNameSuffix = '_sfx'
      $Path = 'C:\MyFolder\Book2.xlsx'
      
      function Rename-WorkSheetColumns ($wrksht, $colNameSuffix) {
          $ColumnsCount = $wrksht.UsedRange.Columns.Count
          for ($i=1; $i -le $ColumnsCount; $i++)
          {
              $column = $wrksht.Columns.Item($i).EntireColumn #$wrksht.sheets.columns.entirecolumn.hidden=$true
              $columnname = $column.cells.item(1,$i).value2
      
              if ($column.hidden -eq $true) { 
                  if ($wrksht.Visible -eq 0) #worksheet hidden
                  {
                      "rnHidden column [{0}] found in hidden [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
                  }
                  else {
                      "rnHidden column [{0}] found in [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
                  }
              }
              else {
                  if ($wrksht.Visible -eq 0) #worksheet hidden
                  {
                      "rnRenaming column [{0}] in hidden [{1}] worksheet..." -f $columnname, $($wrksht.name)
                  }
                  else {
                      "rnRenaming column [{0}] in [{1}] worksheet..." -f $columnname, $($wrksht.name)
                  }
              }
      
              if ($columnname -notlike "*$colNameSuffix*") {          
                  try {
                      $column.cells.item(1,$i).value2 = $columnname + '_' + $colNameSuffix
                      $columnNewName = $column.cells.item(1,$i).value2
      
                      "rnColumn [{0}] was Renamed to [{1}] ! Processing further columns (if any)...rn" -f $columnname, $columnNewName
                  }
                  catch {
                      "rnFailed to Rename column [$columnname] in [$($wrksht.name)] worksheet! $($error[0])rn$($error[0].InvocationInfo.PositionMessage)rn$($error[0].ScriptStackTrace)"
                      #$_ | Select *
                  }
              }
              else {
                  "rnColumn [{0}] already contains '{1}' in its name! Skipping to other columns (if any)...rn" -f $columnname, $colNameSuffix
              }
              #$i = $i - 1
          }
          "rnProceeding with Export of [{0}] worksheet to CSV operation...rn" -f $($wrksht.name)
      }
      
      $csvPATH = Join-Path -Path 'C:\MyFolder' -ChildPath CSV_Files
      New-Item -ItemType Directory -Force -Path $csvPATH | out-null
      
      "rnTerminating Excel process (if any) to prevent 'Can't access' (file in use) exception..."
      
      Get-Process 'exce[l]' | Stop-Process -Force #this is to avoid "Can't access" exception if file is opened or in use
      
      $excel = New-Object -ComObject Excel.Application -Property @{
          Visible       = $false
          DisplayAlerts = $false
      }
      
      $filename = [System.IO.Path]::GetFileNameWithoutExtension($Path)
      
      "rnGenerating CSV files from '$Path' Worksheet(s)..."
      
      $workbook = $excel.Workbooks.Open($Path)
      
      foreach ($worksheet in ($workbook.Worksheets)) {
      
          Rename-WorkSheetColumns $worksheet $columnNameSuffix
      
          $ws = $worksheet
      
          $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
      
              #"rn'$name' generated successfully!"
          } 
          catch {
              "rnFailed to save csv! Path: '$name'. $($error[0])rn$($error[0].InvocationInfo.PositionMessage)rn$($error[0].ScriptStackTrace)"
          }
      }
      
      $excel.Quit()
      $null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)

      related

    • #227137
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      Hello,

      I’ve modified your function some. Give it a shot.

      function Rename-WorkSheetColumns ($wrksht, $colNameSuffix) {
          
          foreach($column in $wrksht.usedrange.columns)
          {
              $columnname = "$($column.cells.item(1,1).value2)" 
      
              if ($column.hidden -eq $true) { 
                  if ($wrksht.Visible -eq 0) #worksheet hidden
                  {
                      "rnHidden column [{0}] found in hidden [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
                  }
                  else {
                      "rnHidden column [{0}] found in [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
                  }
              }
              else {
                  if ($wrksht.Visible -eq 0) #worksheet hidden
                  {
                      "rnRenaming column [{0}] in hidden [{1}] worksheet..." -f $columnname, $($wrksht.name)
                  }
                  else {
                      "rnRenaming column [{0}] in [{1}] worksheet..." -f $columnname, $($wrksht.name)
                  }
              }
      
              if ($columnname -notlike "*$colNameSuffix*") {          
                  try {
                      $column.cells.item(1,1).value2 = "$($columnname)_$colNameSuffix"
                      $columnnewname = "$($column.cells.item(1,1).value2)" 
      
                      "rnColumn [{0}] was Renamed to [{1}] ! Processing further columns (if any)...rn" -f $columnname, $columnNewName
                  }
                  catch {
                      "rnFailed to Rename column [$columnname] in [$($wrksht.name)] worksheet! $($error[0])rn$($error[0].InvocationInfo.PositionMessage)rn$($error[0].ScriptStackTrace)"
                      #$_ | Select *
                  }
              }
              else {
                  "rnColumn [{0}] already contains '{1}' in its name! Skipping to other columns (if any)...rn" -f $columnname, $colNameSuffix
              }
              #$i = $i - 1
          }
          "rnProceeding with Export of [{0}] worksheet to CSV operation...rn" -f $($wrksht.name)
      }
      

      What’s with the rn in the beginning and end of the messages? Were you intending to add extra line breaks?

    • #227140
      Participant
      Topics: 39
      Replies: 109
      Points: 623
      Rank: Major Contributor

      DUDE! You are such a live saver! Ive been trying so many things for HOURS now! OMG

      p.s. I use rn’s for formatting purposes. makes the output look clearner/nicer.

    • #227146
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      Glad I could help!

    • #227149
      Participant
      Topics: 39
      Replies: 109
      Points: 623
      Rank: Major Contributor

      Glad I could help!

      hey if you dont mind, i have one more question. so as part of the export to csv files, some of the column names contain spaces at the end it seems, so i would end up with something like this:

      Renaming column [Total Material  ] in [Fact] worksheet…

      Column [Total Material ] was Renamed to [Total Material  _sfx] ! Processing further columns (if any)…

      notice the space after Material? is there a way to ignore that? instead of Total Material  _sfx it should beTotal Material_sfx

    • #227152
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero
      function Rename-WorkSheetColumns ($wrksht, $colNameSuffix) {
          
          foreach($column in $wrksht.usedrange.columns)
          {
              $columnname = "$($column.cells.item(1,1).value2)".Trim()
      
              if ($column.hidden -eq $true) { 
                  if ($wrksht.Visible -eq 0) #worksheet hidden
                  {
                      "rnHidden column [{0}] found in hidden [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
                  }
                  else {
                      "rnHidden column [{0}] found in [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
                  }
              }
              else {
                  if ($wrksht.Visible -eq 0) #worksheet hidden
                  {
                      "rnRenaming column [{0}] in hidden [{1}] worksheet..." -f $columnname, $($wrksht.name)
                  }
                  else {
                      "rnRenaming column [{0}] in [{1}] worksheet..." -f $columnname, $($wrksht.name)
                  }
              }
      
              if ($columnname -notlike "*$colNameSuffix*") {          
                  try {
                      $column.cells.item(1,1).value2 = "$($columnname)_$colNameSuffix"
                      $columnnewname = "$($column.cells.item(1,1).value2)" 
      
                      "rnColumn [{0}] was Renamed to [{1}] ! Processing further columns (if any)...rn" -f $columnname, $columnNewName
                  }
                  catch {
                      "rnFailed to Rename column [$columnname] in [$($wrksht.name)] worksheet! $($error[0])rn$($error[0].InvocationInfo.PositionMessage)rn$($error[0].ScriptStackTrace)"
                      #$_ | Select *
                  }
              }
              else {
                  "rnColumn [{0}] already contains '{1}' in its name! Skipping to other columns (if any)...rn" -f $columnname, $colNameSuffix
              }
              #$i = $i - 1
          }
          "rnProceeding with Export of [{0}] worksheet to CSV operation...rn" -f $($wrksht.name)
      }
    • #227155
      Participant
      Topics: 39
      Replies: 109
      Points: 623
      Rank: Major Contributor

      thanks! i remembered later it was Trim() 🙂

      hey if i may ask, one more thing, how can i enhance the rename function to say  rename all columns only AFTER column 7 for example?

    • #227158
      Participant
      Topics: 3
      Replies: 340
      Points: 1,120
      Helping Hand
      Rank: Community Hero

      I can think of a couple of ways. One, you can do it manually. Set a count, skip/add until you get to the desired column (number.)

      $count = 0
      Foreach($column in $wrksht.usedrange.columns)
      {
          if($count -lt 7){
              $count++
              continue
          }
          ... rest of code
      }
      

      This is a nicer approach in my opinion.

      foreach($column in ($wrksht.usedrange.columns | Select-Object -Skip 7))
      {
          ... rest of code
      }
      

      Both create the same result.

    • #227164
      Participant
      Topics: 39
      Replies: 109
      Points: 623
      Rank: Major Contributor

      I can think of a couple of ways. One, you can do it manually. Set a count, skip/add until you get to the desired column (number.)

      PowerShell
      10 lines

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

      1
      2
      3
      4
      5
      6
      7
      8
      9
      10
      $count = 0
      Foreach($column in $wrksht.usedrange.columns)
      {
      if($count -lt 7){
      $count++
      continue
      }
      rest of code
      }
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      This is a nicer approach in my opinion.

      PowerShell
      5 lines

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

      1
      2
      3
      4
      5
      foreach($column in ($wrksht.usedrange.columns | Select-Object Skip 7))
      {
      rest of code
      }
      XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX

      Both create the same result.

      skip 7…i like that! Thanks!

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