How to use powershell to select range and dump that to csv file

Question

Actually, this is a version of question here: How to use powershell to select and copy columns and rows in which data is present in new workbook.

The goal is to grab certain columns from multiple Excel workbooks and dump everything to one csv file. Columns are always the same.

I'm doing that manually:

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false
$counter = 0

$input_folder = "C:\Users\user\Documents\excelfiles"
$output_folder = "C:\Users\user\Documents\csvdump"

Get-ChildItem $input_folder -File |
Foreach-Object {
    $counter++
    $wb = $xl.Workbooks.Open($_.FullName, 0, 1, 5, "")
    try {
        $ws = $wb.Worksheets.item('Calls') # => This specific worksheet
        $rowMax = ($ws.UsedRange.Rows).count
        for ($i=1; $i -le $rowMax-1; $i++) {
            $newRow = New-Object -Type PSObject -Property @{
                'Type'                  = $ws.Cells.Item(1+$i,1).text
                'Direction'             = $ws.Cells.Item(1+$i,2).text
                'From'                  = $ws.Cells.Item(1+$i,3).text
                'To'                    = $ws.Cells.Item(1+$i,4).text
            }
        $newRow | Export-Csv -Path $("$output_folder\$ESO_Output") -Append  -noType -Force 
            }
        }

    } catch {
        Write-host "No such workbook" -ForegroundColor Red
        # Return
    }
}

Question:

This works, but is extremely slow because Excel has to select every cell, copy that, then Powershell has to create array and save row by row in output csv file.

Is there a method to select a range in Excel (number of columns times ($ws.UsedRange.Rows).count), cut header line and just append this range (array?) to csv file to make everything much faster?

Fastest method is probably to have Excel copy the range to a new sheet, and then saving said sheet to CSV — Mathias R. Jessen
– Mathias R. Jessen, Commented Sep 30, 2021 at 10:34
You mean run VBA script from Powershell? Is there a safe method to do that? — Vilq
– Vilq, Commented Sep 30, 2021 at 10:41

Vilq · Accepted Answer · 2021-09-30 12:59:31Z

So that's the final solution

Script is 22 times faster!!! than original solution.

Hope somebody will find that useful :)

PasteSpecial is to filter out empty rows. There is no need to save them into csv

$xl = New-Object -ComObject Excel.Application
$xl.Visible = $false
$xl.DisplayAlerts = $false
$counter = 0

$input_folder = "C:\Users\user\Documents\excelfiles"
$output_folder = "C:\Users\user\Documents\csvdump"

Get-ChildItem $input_folder -File |
Foreach-Object {
    $counter++
    try {
        $new_ws1 = $wb.Worksheets.add()
        $ws = $wb.Worksheets.item('Calls')
        $rowMax = ($ws.UsedRange.Rows).count
        $range = $ws.Range("A1:O$rowMax")
        $x = $range.copy()
        $y = $new_ws1.Range("A1:O$rowMax").PasteSpecial([System.Type]::Missing,[System.Type]::Missing,$true,$false)
        $wb.SaveAs("$($output_folder)\$($_.Basename)",[Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSVWindows)

    } catch {
        Write-host "No such  workbook" -ForegroundColor Red
        # Return
    }

}
$xl.Quit()

Part above will generate a bunch of csv files.

Part below will read these files in separate loop and combine them together into one.

-exclude is an array of something I want to omit

Remove-Item to remove temporary files

Answer below is based on this post: https://stackoverflow.com/a/27893253/6190661

$getFirstLine = $true
Get-ChildItem "$output_folder\*.csv" -exclude $excluded | foreach {

    $filePath = $_

    $lines = Get-Content $filePath  
    $linesToWrite = switch($getFirstLine) {
           $true  {$lines}
           $false {$lines | Select -Skip 1}
    }

    $getFirstLine = $false
    Add-Content "$($output_folder)\MERGED_CSV_FILE.csv" $linesToWrite

    Remove-Item $_.FullName

}

Collectives™ on Stack Overflow

How to use powershell to select range and dump that to csv file

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related