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](https://stackoverflow.com/a/44396396/712649) — Mathias R. Jessen, Sep 30 '21 at 10:34
You mean run VBA script from Powershell? Is there a safe method to do that? — Vilq, Sep 30 '21 at 10:41

score 0 · Accepted Answer · answered Sep 30 '21 at 12:59

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

}

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

1 Answers1