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)
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s