Feeds:
Posts
Comments

Posts Tagged ‘FileCopy’

I had a request to take data from a CSV file that was being automatically downloaded and copy a section of the data into an existing spreadsheet. The spreadsheet had 3 tabs and the copied data had to go to the tab labeled “Raw Data”. Then one of the other tabs would perform calculations and analysis on that raw data. I know I could manually copy the data but this process is already partially automated and the number of rows and columns involved were huge. There are at least 60 columns and over 10,000 rows of data that needed to be copied. So, to Powershell I go!!

I created a Powershell Script that would open both files and then select the data from the csv file and copy it.  Then the script would go to the Excel file and to the required tab before pasting the info.

Here is the final script.

$pathxls = “\\fileserver\data\myshare\sheets\workbook.xls”
$pathcsv = “\\fileserver\data\myshare\sheets\Overview.csv”
$Excel = New-Object -ComObject excel.application
$Excel.visible = $false
$Workbook = $excel.Workbooks.open($pathcsv)
$Workbook2 = $excel.Workbooks.open($pathxls)
$Worksheet = $Workbook.WorkSheets.item(“Overview”)
$Worksheet.activate()
$range = $WorkSheet.Range(“A2:BC2”).CurrentRegion
$range.Copy() | out-null
$Worksheet2 = $Workbook2.Worksheets.item(“RAW DATA”)
$worksheet2.activate()
$range2 = $Worksheet2.Range(“A3:A3”)
$Worksheet2.Paste($range2)
$workbook2.Save()
$workbook.close($false)
$Excel.Quit()
[gc]::collect()
[gc]::WaitForPendingFinalizers()

Now let’s dissect it a bit.

First we declare the variables for the two files $pathxls and $pathcsv and assign the path and file to each variable.

Then we declare a new com object which is an excel application.

We tell it that we don’t want Excel to be visible during the run of the script.

And we then set two workbook variables for the two files.

In the first file (the CSV) we tell it to go to the worksheet labled “Overview” and then make it the activate sheet.  Once there we select a range and copy it.

Then we jump over to the second worksheet and select the tab called “Raw Data” and make it the active sheet.

Once that is done we can paste in the data we’ve copied and then we can save the Excel workbook.  We close the csv file and tell it NOT to save since we don’t need to and then we can quit Excel.

Rather simple and to the point.  It gets the job done.  Now I can either run this manually or set it as a scheduled task.

 

 

Read Full Post »

I was reading Joe Keohan’s Blog about copying files via PowerShell and that got me to thinking about a process I already had.  Each night we copy files from a shared folder and post them to our intranet site for internal use.  We currently are using a batch file to handle this but I thought it was time to rewrite it in PowerShell. 

I had two main processes that I needed to handle.  The first was to copy the files to the local folder for the intranet and the second was to send an email to someone with those files attached so he can open them on his mobile device. 

So the first step of my script was to define some variables to stand in for my file locations. 

$SourceDir = "\\SVR1\data\Reports\Trading\*.pdf"
$DestDir = "c:\inetpub\intranet\research\reports\"
$BlotterSource = "\\SVR1\data\Reports\Blotters\*.pdf"

Then I use the Test-Path cmdlet to determine if the reports exist at the source location.  Normally this cmdlet would return True if the files exist and False if they do not.  But I use it in an If statement to say if it evaluates to True then do something else.  The something else is to start the file copy. 

 

If (Test-path $SourceDir )
{
    Copy-Item $SourceDir -Destination $DestDir -Force
}

If (Test-path $BlotterSource)
{
    Copy-Item $BlotterSource -Destination $DestDir -Force
}

I do this twice, once for one location and then again for the second location. 

And once my script is finished copying the files I use the send-mailmessage cmdlet to email the files to the recipient.  Since I have multiple files I’d like to attach I use a Get-ChildItem cmdlet to get all the file names and pipe them into the send-mailmessage cmd. 

This is what the finished script looks like. 

$SourceDir = "\\SVR1\data\Reports\Trading\*.pdf"

$DestDir = "c:\inetpub\intranet\research\reports\"

$BlotterSource = "\\SVR1\data\Reports\Blotters\*.pdf"

If (Test-path $SourceDir )

{ Copy-Item $SourceDir -Destination $DestDir -Force }

If (Test-path $BlotterSource)

{ Copy-Item $BlotterSource -Destination $DestDir -Force }

Get-ChildItem $DestDir | Where {-NOT $_.PSIsContainer} | foreach {$_.fullname} | send-mailmessage -from "Reporting <reporting@anyone.com>" -to "Jonathan <jonathan@anyone.com>", "Rob <rob@anyone.com>" -Cc "Greg <greg@anyone.com"> -subject "Daily Research Reports" -smtpServer mailserver.anyone.com -Body "Here are the daily research files for your review"

Now that the script is done I just schedule it via Windows Task Scheduler and we’re all set.  Now the files will get copied and emailed quickly and efficiently.

Read Full Post »