Why is this function skipping every other column?

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

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?

[quote quote=227137][/quote]
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.

Glad I could help!

[quote quote=227146]Glad I could help!

[/quote]
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

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)
}

[quote quote=227152][/quote]
thanks! i remembered later it was Trim() :slight_smile:

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?

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.

[quote quote=227158]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.

[/quote]
skip 7…i like that! Thanks!