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."

A note on the scripts in this blog

Featured

When this Blog was migrated from Windows Live to WordPress some things did not translate well or at all. The most noticeable are “\”; that’s the “\” as in c:\ or in C:\Scripts\PS1. which just isn’t there anymore. They also put “the and sign” nbsp; in the place of spaces and tabs and `< and `> with the “and sign” gt and the “and sign” lt.

I don’t know why or how, but if you copy these scripts, you will need to check these very closely.

Thank you for your support.

How do I Update an Excel Spreadsheet from another Sheet.

I am working on a project to change several thousand server from Symantec AntiVirus to Symantec End Point Protection. We keep track of the servers in an Excel spreadsheet that I call “Master.xlsx” and the updates are in another Spreadsheet called “Update-Date.xlsx”. The trick is to update the Master once a week with those servers that have changed from SAV to SEP. And to limit the update to the first 14 columns. There may be better or more elegant ways to do this, but here is my solution:

#<---- Begin Powershell Script ------------------------------------------>
$d=Show-Inputbox -message "Enter Update Spredsheet Name" `
-title "Update File" -default "Update-Date.xlsx"
$e=Show-Inputbox -message "Enter the Update Tab Name" `
-title "Tab Name" -default "10-23-2010"
$xlCellTypeLastCell = 11
$path = "D:\SavToSep\MASTER.xlsx"

$xl = New-Object -ComObject excel.application
$xl.visible = $True
$xl.DisplayAlerts = $False
$wb = $xl.Workbooks.open("D:\SavToSep\MASTER.xlsx")
$ws = $wb.WorkSheets.item("Servers")
$wb1 = $xl.Workbooks.open("D:\SavToSep\$D.xlsx")
$ws1 = $wb1.WorkSheets.item($E)
$ws1.activate()

$Rng = $ws1.UsedRange.Cells
$row = $Rng.Rows.Count
Write-Host "Row: " $row

for ($i = 2; $i -le $row; $i++) {
$wb1.activate()
$ws1.activate()
$x = $ws1.Cells.Item($i, 1).Value()
Write-Host "i: " $i
$Range = $ws1.range("a${i}:n$i")
$Range.Copy()
$wb.activate()
$ws.activate()
$mainRng = $ws.usedRange
$mainRng.Select()
$objSearch = $mainRng.Find($x)

	If ($objSearch -eq $Null) {
	$used = $ws.usedRange
	$lastCell = $used.SpecialCells($xlCellTypeLastCell)
	$lastrow = $lastCell.row
	$NewRow = $lastrow + 1
	#[void]$ws.Cells.Item($NewRow,1).Paste()
	[void]$ws.Cells.Item($NewRow,1).PasteSpecial(-4122)
	[void]$ws.Cells.Item($NewRow,1).PasteSpecial(-4163)
	Write-Host "New Row: " "A"$NewRow

}Else {
        $objSearch.Select()
	$R = $objSearch.row
	Write-Host "R: " $R
	#[void]$ws.Cells.Item("A$R").Paste()
	[void]$ws.Range("A$R").PasteSpecial(-4122)
	[void]$ws.Range("A$R").PasteSpecial(-4163)
	}
$wb1.Save()
$wb.Save()

$rc=Show-Msgbox -message "Do you want to view the Spredsheet?" `
-icon "Question" -button "YesNoCancel" -title "Hey $env:username!!"

Switch ($rc) {
"Yes" {$xl.Visible = $True }
# close and release resources
"No" {$wb.close($false)
 $xl.quit()
 spps -n excel
 }
 "cancel" {"When in doubt, punt."}
}

# <------------- End Script ----------------------------------------------->;

More Fun with Dates (and Times)

# COMMENT: More Fun with Dates (and Times)
#  I got this from the Microsoft TechNet Site, but I thought it was too good not to share.

# ===================================
# $a = Get-Date# Accessing Other Date Properties Using Get-Date
$a = Get-Date
"Day: " + $a.Day
"Month: " + $a.Month
"Year: " + $a.Year
"Hour: " + $a.Hour
"Minute: " + $a.Minute
"Second: " + $a.Second$a = (Get-Date).DayFunction ShortDate {
$a = Get-Date
"Date: " + $a.ToShortDateString()
"Time: " + $a.ToShortTimeString()

}

Function DST {
$a = Get-Date
"Daylight Saving Time: " + $a.IsDaylightSavingTime()
"Day of Week: " + $a.DayOfWeek
"Day of Year: " + $a.DayOfYear
}

Function Zulu {
$a = Get-Date
"Universal Time: " + $a.ToUniversalTime()
}
# Simple Date Arithmetic
Function AddDays {
$a = Get-Date
$a.AddDays(90)
}
Function SubDays {
$a = Get-Date
$a.AddDays(-90)
}
# PowerShell Bonus: Getting an Array of Day/Month Names
Function DayNames {
$a = New-Object system.globalization.datetimeformatinfo
$b = $a.DayNames
$b
}

Function MonthNames {
$a = New-Object system.globalization.datetimeformatinfo
$b = $a.MonthNames
$b
}

$Date = (Get-Date -format "MM-dd-yyyy") #&lt;-- Date looks like 03-09-2011. Gr8 for file names

How do I Import a Text file to Excel via Powershell

You can try this:

$XL.workbooks.Open($file)

But that does not always work, Especially if you have some delimiter that you want to use.
I opened up Excel and recorded a macro to see how Excel does it.
Here is the Macro it recorded with some editing:
 

# Sub Macro2()
# ‘
# ‘ Macro2 Macro
# ‘
#
# ‘
#     Workbooks.OpenText Filename:=”Z:\MBSA_ReportExcluded.txt”,
#     Origin:=437, _
#     StartRow:=1,
#     DataType:=xlDelimited,
#     TextQualifier:=xlDoubleQuote, _
#     ConsecutiveDelimiter:=[True,False],
#     Tab:=True,
#     Semicolon:=False,
#     Comma:=False,
#     Space:=False,
#     Other:=False,
#     FieldInfo:=Array(Array(1, 1), Array(2, 1)), _
#     TrailingMinusNumbers:=True
# End Sub

I looked up the Excel constants (xlDelimited, xlDoubleQuote) and they are both equal to 1.
So I tried several different ways in Powershell and this one works for a Tab delimited file.
 

$oXL = New-Object -comobject Excel.Application
$oXL.Visible = $true
$oXL.DisplayAlerts = $False
$file = “Z:\MBSA_ReportExcluded.txt”
$oXL.workbooks.OpenText($file,437,1,1,1,$True,$True,$False,$False,$False,$False)

The first $True sets whether you want consecutive delimiters to be treated as one.

(This is useful mostly with space delimited files.)

The last five $True/$False are the ones that select the delimiter.

#  1   Tab:=True,
#  2   Semicolon:=False,
#  3   Comma:=False,
#  4   Space:=False,
#  5   Other:=False,

How do I change the size or position of my chart with Powershell

 
# <---- Start Code ---------------------------------------> 
$xl = New-Object -comobject Excel.Application
# Show Excel
$xl.visible = $true
$xl.DisplayAlerts = $False
# Open a workbook
$wb = $xl.workbooks.add() 
#Create Worksheets
$ws = $wb.Worksheets.Item(1) # Opens Excel and 3 empty Worksheets
1..8 | % { $ws.Cells.Item(1,$_) = $_ } # adds some data
1..8 | % { $ws.Cells.Item(2,$_) = 9-$_ } # adds some data
$range = $ws.range("a${xrow}:h$yrow") # sets the Data range we want to chart
# create and assign the chart to a variable
#$ch = $xl.charts.add() # This will open a new sheet
$ch = $ws.shapes.addChart().chart # This will put the Chart in the selected WorkSheet
$ch.chartType = 58
$ch.setSourceData($range)
$RngToCover = $ws.Range("D5:J19") # This is where we want the chart
$ChtOb = $ch.Parent # This selects the current Chart
$ChtOb.Top = $RngToCover.Top # This moves it up to row 5
$ChtOb.Left = $RngToCover.Left # and to column D 
$ChtOb.Height = $RngToCover.Height # resize This sets the height of your chart to Rows 5 - 19
$ChtOb.Width = $RngToCover.Width # resize This sets the width to Columns D - J
<------------- End Code --------------------------------------> 

Thanks to Marco Shaw and to this article he pointed me to:
Read more: Resizing and Moving Excel Charts with VBA http://peltiertech.com/Excel/ChartsHowTo/ResizeAndMoveAChart.html#ixzz0seQImNEL