CODEWITHSUNDEEP
excelpowershell
Vilq
Vilq

Reputation: 601

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

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?

Upvotes: 0

Views: 2170

Answers (1)

Vilq
Vilq

Reputation: 601

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

}

Upvotes: 0

Related Questions