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

cataster · April 29, 2020, 4:29pm

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

krzydoug · April 29, 2020, 7:26pm

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.

cataster · April 29, 2020, 7:29pm

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

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

krzydoug · April 29, 2020, 7:35pm

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!

cataster · April 29, 2020, 7:47pm

[quote quote=223932]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!

[/quote]
Sounds good, thanks!

cataster · April 30, 2020, 12:06am

[quote quote=223932]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!

[/quote]

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

cataster · April 30, 2020, 12:46am

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

krzydoug · April 30, 2020, 12:56am

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

cataster · April 30, 2020, 1:00am

<p style=“padding-left: 40px;”>[quote quote=224040]I got the list of hidden column headers like this.

</p>

PowerShell

6 lines

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;">[/quote] Oh interesting approach! Is value2 a method you found on MS docs?</p> Also, how can I print thr $column name?

krzydoug · April 30, 2020, 1:02am

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.

krzydoug · April 30, 2020, 1:13am

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

cataster · April 30, 2020, 10:21am

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

krzydoug · April 30, 2020, 1:48pm

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.

cataster · May 26, 2020, 1:15pm

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

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

krzydoug · May 26, 2020, 1:35pm

You may have to add me to payroll soon.

cataster · May 26, 2020, 1:41pm

[quote quote=231151]You may have to add me to payroll soon.

[/quote]
hahaha yessir

Topic		Replies	Views
How to skip export of empty rows? PowerShell Help	11	289	May 26, 2020
Excel to CSV Conversion - Issue PowerShell Help	1	214	January 7, 2022
Exporting CSV file to XLS PowerShell Help	7	212	October 12, 2016
powershell to csv getting columns PowerShell Help	2	211	February 25, 2019
How to import specific column from all the excel files and export it as a table PowerShell Help	4	198	April 8, 2018

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

Related Topics