How can I use Windows Powershell to copy Selected CSV Files to one Excel WorkBook?


# Script name: Copy-CsvToExcel.ps1
# Created on: 3/21/2011
# Author: OldDog1
# Purpose: How can I use Windows Powershell to
# copy Selected CSV Files to one Excel WorkBook?
#-----------------------------------------------------
function Release-Ref ($ref) {
([System.Runtime.InteropServices.Marshal]::ReleaseComObject(
[System.__ComObject]$ref) -gt 0)
[System.GC]::Collect()
[System.GC]::WaitForPendingFinalizers()
}
#-----------------------------------------------------
$xlPasteValues = -4163          # Values only, not formulas
$xlCellTypeLastCell = 11        # to find last used cell

$xl = new-object -comobject excel.application
$xl.Visible = $True
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.Add()
$i = 1
# The Get_ChildItem returns the entire Path (G:\Scrips\myfile.csv)

$collection = Get-ChildItem G:\Scripts\* -include *.csv

$length = 4

foreach ($item in $collection) {
$wb1 = $xl.Workbooks.Open("$item")
$array = $item.ToString()
$delim = "\"
$SheetName = $array.split($delim) # here i split off the G:\scripts\
$s = $SheetName[2]
$sn = $s.split(".") # Here I split off the .csv
$nsn = $sn[0]
$ws1 = $wb1.worksheets | where {$_.name -eq $nsn}
Write-Host $item $nsn # here I make sure I did it right
$used = $ws1.usedRange
$used.Select()
$used.copy()
$wb.Activate()
$ws = $wb.Sheets.Add()
$ws2 = $wb.worksheets | where {$_.name -eq "sheet$i"}
[void]$ws2.Range("A1").PasteSpecial(-4163)
$ws2.name = $nsn # Here I name the new sheet
$i++
$wb1.Close() # Close the .CSV file and move on to the next one
}

# $xl.quit()

# $a = Release-Ref($ws)
# $a = Release-Ref($wb)
# $a = Release-Ref($xl)

Find and Delete a Column using an Array in Excel


Here I am looking for column names that I no longer need and deleting the whole column when I find them in my spreadsheet. My spreadsheet has 26 columns so I search from Col 1 to Col 26. Because I am deleting a column, I have to subtract 1 from $i, otherwise it skips a column.

$array = ("CI_ID","SERIALNUMBER","TIER1","TIER2","TIER3","MANUFACTURERNAME","PRODUCT_NAME")

for ($i=1;$i -le 26; $i++) {
$z = $ws1.cells.item(1,$i).value()
$b = $array -contains $z
# If the array ($array) contains the cell value ($z), delete the entire column
If ($b) {
$selection = $ws1.cells.item(1,$i)
[void]$selection.Select()
[void]$selection.entireColumn.delete()
Write-Host "I've deleted $z"
$i = $i - 1
}
} 


Powershell How do I sort two columns in Excel ?

For some reason sorting two columns is much harder than sorting one.

$xlSortOnValues = $xlSortNormal = 0
$xlTopToBottom = $xlSummaryBelow = 1
$xlAscending = 1
$xlDescending = 2

[void]$ws1.Activate()
$last = $ws1.UsedRange.SpecialCells(11).Address($False,$false)
$range1 = $ws1.range("A2:$last" )
$range2 = $ws1.range("D2")
$range3 = $ws1.range("A2")
#two-column sort ---> works
$ws1.sort.sortFields.clear()
[void]$ws1.sort.sortFields.add($range2, $xlSortOnValues, $xlAscending,`
$xlSortNormal)
[void]$ws1.sort.sortFields.add($range3, $xlSortOnValues, $xlAscending,`
$xlSortNormal)
$ws1.sort.setRange($range1)
$ws1.sort.header = $xlNo
$ws1.sort.orientation = $xlTopToBottom
$ws1.sort.apply()

Using –Match in excel

I needed to match a part of a cell in Excel and delete the row if a match was found. I was looking for server names that had –Old at the beginning or end of the name or -new or zz. or -inactive or -delete or two or more periods (..)


[void]$ws1.Activate()
$last = $ws1.UsedRange.SpecialCells(11).Address($False,$false)
$range1 = $ws1.range("A2:$last" )
$range2 = $ws1.range("A2") </span>

<span style="font-family: Comic Sans MS;"># one-column sort --&gt; works
[void]$range1.sort($range2, $xlAscending) </span>

<span style="font-family: Comic Sans MS;">$used = $ws1.usedRange
$Rng = $ws1.range("A:A")
$oSearch = $Null
$ySearch = $Rng.Find($oSearch)
$zSearch = $ySearch.Row
$R = $zSearch</span>

<span style="font-family: Comic Sans MS;">For ($i = $R; $i -ge 2; $i--)  {
  Write-Progress -Activity "Searching $R Old's..." `
     -PercentComplete ($i/$R*100) -CurrentOperation `
     "$i of $R to go" -Status "Please wait."
      $Rng = $ws1.Cells.Item($i, 1).value()
If ($Rng -match '\bold\b|-new|zz\.|-INACTIVE|-DELETE|\.\.+') {
# Match at Begining and end of word '\b   \b' &lt;-- looking for old? '\bold\b'
# Finds -new or zz. or -inactive or -delete or two or more periods (..)
# in Match a . is the same as *, so you must put a \ before a period \.
# othewise it will catch every charactor.
   Write-Host $Rng
   $Range = $ws1.Cells.Item($i, 1).EntireRow
     [void]$Range.Select()
     [void]$Range.Delete()
     $R = $R - 1
      }
    }
     Write-Progress -Activity "Searching -Old's..." `
  -Completed -Status "All done."