0

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
            {
                "`r`nHidden column [{0}] found in hidden [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
            }
            else {
                "`r`nHidden column [{0}] found in [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
            }
        }
        else {
            if ($wrksht.Visible -eq 0) #worksheet hidden
            {
                "`r`nRenaming column [{0}] in hidden [{1}] worksheet..." -f $columnname, $($wrksht.name)
            }
            else {
                "`r`nRenaming 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

                "`r`nColumn [{0}] was Renamed to [{1}] ! Processing further columns (if any)...`r`n" -f $columnname, $columnNewName
            }
            catch {
                "`r`nFailed to Rename column [$columnname] in [$($wrksht.name)] worksheet! $($error[0])`r`n$($error[0].InvocationInfo.PositionMessage)`r`n$($error[0].ScriptStackTrace)"
                #$_ | Select *
            }
        }
        else {
            "`r`nColumn [{0}] already contains '{1}' in its name! Skipping to other columns (if any)...`r`n" -f $columnname, $colNameSuffix
        }
        #$i = $i - 1
    }
    "`r`nProceeding with Export of [{0}] worksheet to CSV operation...`r`n" -f $($wrksht.name)
}

$csvPATH = Join-Path -Path 'C:\MyFolder' -ChildPath CSV_Files
New-Item -ItemType Directory -Force -Path $csvPATH | out-null

"`r`nTerminating 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)

"`r`nGenerating 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

        #"`r`n'$name' generated successfully!"
    } 
    catch {
        "`r`nFailed to save csv! Path: '$name'. $($error[0])`r`n$($error[0].InvocationInfo.PositionMessage)`r`n$($error[0].ScriptStackTrace)"
    }
}

$excel.Quit()
$null = [System.Runtime.InteropServices.Marshal]::ReleaseComObject($excel)
Cataster
  • 3,081
  • 5
  • 32
  • 79
  • i would export the columns to a CSV and then do the rename. that seems likely to be a bit easier to debug if things go sideways ... [*grin*] – Lee_Dailey May 10 '20 at 23:30
  • @Lee_Dailey I suppose thats one way i can do it...really curious though why this is partially working – Cataster May 10 '20 at 23:31
  • yep, i am curious ... but i don't have MSOffice, only LibreOffice, so i can't test any of it. – Lee_Dailey May 11 '20 at 00:39
  • @Lee_Dailey i understand, thanks for checking it out briefly regardless :) – Cataster May 11 '20 at 00:54
  • @Lee_Dailey actually, maybe you can help me in the suggestion you gave. I found how to rename columns in csv, but all the examples i found have the column name herdcoded. could you post an example of how i can import a csv file, change all column names (adding a suffix)? – Cataster May 11 '20 at 01:12
  • take a look at the `.PSObject.Properties.Name` hidden property stuff that PoSh adds to most objects. for example, this >>> `(Import-Csv 'C:\Temp\Test.Csv')[0].PSObject.Properties.Name` <<< will give you the column names for the object at position zero. – Lee_Dailey May 11 '20 at 01:46
  • Probably related: [Not all properties displayed](https://stackoverflow.com/a/44429084/1701026). – iRon May 11 '20 at 08:48

1 Answers1

0

This is the correct function!

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
            {
                "`r`nHidden column [{0}] found in hidden [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
            }
            else {
                "`r`nHidden column [{0}] found in [{1}] worksheet. Renaming..." -f $columnname, $($wrksht.name)
            }
        }
        else {
            if ($wrksht.Visible -eq 0) #worksheet hidden
            {
                "`r`nRenaming column [{0}] in hidden [{1}] worksheet..." -f $columnname, $($wrksht.name)
            }
            else {
                "`r`nRenaming 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)" 

                "`r`nColumn [{0}] was Renamed to [{1}] ! Processing further columns (if any)...`r`n" -f $columnname, $columnNewName
            }
            catch {
                "`r`nFailed to Rename column [$columnname] in [$($wrksht.name)] worksheet! $($error[0])`r`n$($error[0].InvocationInfo.PositionMessage)`r`n$($error[0].ScriptStackTrace)"
                #$_ | Select *
            }
        }
        else {
            "`r`nColumn [{0}] already contains '{1}' in its name! Skipping to other columns (if any)...`r`n" -f $columnname, $colNameSuffix
        }
        #$i = $i - 1
    }
    "`r`nProceeding with Export of [{0}] worksheet to CSV operation...`r`n" -f $($wrksht.name)
}

Thank you Doug Maurer on powershell.org!

Cataster
  • 3,081
  • 5
  • 32
  • 79