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: 41
      Replies: 113
      Points: 647
      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:

      [/crayon]

      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:

      [/crayon]

      This is the generated csv file columns:

      [/crayon]

      here is the code:

      nHidden 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)[/crayon]

      related

    • #227137
      Participant
      Topics: 9
      Replies: 678
      Points: 2,683
      Helping Hand
      Rank: Community Hero

      Hello,

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

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

    • #227140
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      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: 9
      Replies: 678
      Points: 2,683
      Helping Hand
      Rank: Community Hero

      Glad I could help!

    • #227149
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      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: 9
      Replies: 678
      Points: 2,683
      Helping Hand
      Rank: Community Hero
    • #227155
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      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: 9
      Replies: 678
      Points: 2,683
      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.)

      This is a nicer approach in my opinion.

      Both create the same result.

    • #227164
      Participant
      Topics: 41
      Replies: 113
      Points: 647
      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
  • The topic ‘Why is this function skipping every other column?’ is closed to new replies.