Working with csv and xlsx files using powershell Part-2

Continuing from where we left in part-1, we’ll dive further in this topic.

Copy a range of cells from one excel sheet and paste in another

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$xl1 = New-Object -ComObject Excel.Application
$xl1.visible = $true
$wb1 = $xl1.workbooks.open($xlsx3)
$xl1.Worksheets.item(1).name = "Worksheet1"
$ws1 = $wb1.worksheets.Item(1)

$ws1.Activate()

$lr1 = $ws1.UsedRange.rows.count
$r1 = $ws1.Range("A1:F$lr1")
$r1.copy()

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$xl2 = New-Object -ComObject Excel.Application
$xl2.visible = $true
$wb2 = $xl2.workbooks.open($xlsx4)
$ws2 = $wb2.worksheets.Item(1)
$ws2.activate()

$r2 = $ws2.Range("A1:F$lr1")
$ws2.Paste($r2)

$wb1.save()

$wb2.save()
$xl1.Workbooks.Close()

$xl2.Workbooks.Close()
$xl1.Quit()

$xl2.Quit()

We can change the cell selection as per our need, for e.g.

$lr1 = ($ws1.UsedRange.rows.count)-1

This will count all the rows ‘r’ and subtract the last row. As a result the variable ‘$lr1’ will store the numeric value ‘r-1’. After this we can just mention the selction range as

$r1 = $ws1.Range("A1:F$lr1")

This will select all the cells containing data starting from ‘A1’ cell upto the second last row. Instead of two steps, it can be combined into one as,

$r1 = $ws1.Range("A1:F$($ws1.UsedRange.rows.count-1)")

We can paste the selection to any place in the excel by altering the range as below.

$lr2 = $r1.Rows.Count

This will store the count of rows contained in the copy selection that we have made and store it in variable ‘$lr2’.

$r2 = $ws2.Range("A2:F$lr2")
$ws2.Paste($r2)

Note that the source and destination cell range should be exactly same.

Now that we are discussing about excel com object and powershell, so Powershell Excel Cookbook Ver 2 can’t go without mention. It has almost everything one can get across while working to automate excel with PowerShell.

Inserting values in specific cells

$ws1.cells.item(1,2) = "Hello"

This will store the string ‘Hello’ in cell ‘B1’. The range (1,2) correspond to cell at the intersection of first row and second column.

Sorting a column

$rsort1=$ws1.usedrange
$rsort2 = $ws1.Range("A2")
[void]$rsort1.sort($rsort2, 1)

First we will select all the cells where data is present and store this range in variable ‘rsort1’. After this we will pick the cell from the respective column from which onwards, we want the sorting to be done. So in this case, cells in ‘A’ column starting from ‘2’ row will be sorted in ascending manner due to parameter ‘1’.

Summing values in each column and displaying the sum at the bottom of respective column

$r1 = $ws1.usedRange
$row1 = $r1.rows.count
$Sumrow1 = $row1 + 1
$r2 = $ws1.Range("B2:B$row1")
$r3 = $ws1.Range("C2:C$row1")
$r4 = $ws1.Range("D2:D$row1")
$r5 = $ws1.Range("E2:E$row1")
$r6 = $ws1.Range("F2:F$row1")
$functions = $xl1.WorkSheetfunction
$ws1.cells.item($Sumrow1,1) = "Total"
$ws1.cells.item($Sumrow1,2) = $functions.sum($r2)
$ws1.cells.item($Sumrow1,3) = $functions.sum($r3)
$ws1.cells.item($Sumrow1,4) = $functions.sum($r4)
$ws1.cells.item($Sumrow1,5) = $functions.sum($r5)
$ws1.cells.item($Sumrow1,6) = $functions.sum($r6)
[void]$r1.entireColumn.Autofit()

First we will store the count of all the rows conatining data and store in variable $row1. Assuming that the first row contains the headers, we select from 2nd row to the end. After this we make use of ‘worksheetfunction’ of excel ‘xl1’ object that we have created to call ‘sum’ function. After this we will sum the values in columns ‘A’ to ‘F’ and store at their bottom. After the operation, we can ‘autofit’ the entire data range. There is another way of adjusting the column width as below, by specifying the column width.

$ws1.Columns.Item('a').columnWidth = 20

Sometimes if the datatype of values is set to text/general, the summing operation might fail. So we have to set the datatype prior to this, as follows.

$ws1.Columns.Item('a').Numberformat="0"
$ws1.Columns.Item('b').Numberformat="0"
$ws1.Columns.Item('c').Numberformat="0"
$ws1.Columns.Item('d').Numberformat="0"
$ws1.Columns.Item('e').Numberformat="0"
$ws1.Columns.Item('f').Numberformat="0"

Adding chart to worksheet

Lets select chart and chart type first that we will put in the worksheet.

$ch = $ws1.shapes.addChart().chart
$ch.chartType = 58

Then we will set the data range that we want to chart. We can also give a suitable title to the chart.

$ch.setSourceData($range1)
$ch.HasTitle = $true
$ch.ChartTitle.Text = "My chart"

Now we will select the cell range where we want to copy the chart. If we want to adjust the chart size, it can be done by alterig the range as below.

$RngToCover = $ws1.Range("A$($range2.Rows.Count + 5):F$($range.Rows.Count + 23)")
$ChtOb = $ch.Parent
$ChtOb.Top = $RngToCover.Top
$ChtOb.Left = $RngToCover.Left
$ChtOb.Height = $RngToCover.Height
$ChtOb.Width = $RngToCover.Width

Cell formatting

Below are some basic formatting operations that can be done on a cell range.

$range.select()
$range.HorizontalAlignment = -4108
$range.VerticalAlignment = -4107
$range.BorderAround(1,4,1)
$range.WrapText = $false
$range.Orientation = 0
$range.AddIndent = $false
$range.IndentLevel = 0
$range.ShrinkToFit = $false
$range.ReadingOrder = -5002
$range.MergeCells = $false
$range.Borders.Item(12).Weight = 2
$range.Borders.Item(12).LineStyle = 1
$range.Borders.Item(12).ColorIndex = -4105

Working with csv and xlsx files using powershell Part-1

This is a part of series of posts to discuss various operations that can be done on .csv and .xlsx files using powershell.

Changing delimiter of csv file:

Import-Csv -Delimiter "," -Path $p|Export-Csv -Delimiter ";" -Path $p1 -NoTypeInformation

Replacing a character from a column in csv file:

Import-Csv -Delimiter ";" -Path $p1 -Header "1","2","3"|
Select-Object *| ForEach-Object {
"{0};{1};{2}" -f $_.1,($_.2).replace("Mr.",""),$_.3>> $p2
}

Removing header from a csv file:

Import-Csv -Delimiter ";" -Path $p2 -Header "1","2","3"|
Select-Object -Skip 1 >> $p3
}

Inserting headers in a headerless csv file:

import-csv -Delimiter ";" -Path $p4 -Header "Team","Player name","Status"|
Export-Csv -Delimiter ";" -Path $p5 -NoTypeInformation

Converting csv into xlsx file:

# Create a new Excel workbook with one empty sheet
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$excel = New-Object -ComObject excel.application
$excel.DisplayAlerts=$false
$workbook = $excel.Workbooks.Add(1)
$worksheet = $workbook.worksheets.Item(1)

# Build the QueryTables.Add command and reformat the data
$TxtConnector = ("TEXT;" + $p5)
$Connector = $worksheet.QueryTables.add($TxtConnector,$worksheet.Range("A1"))
$query = $worksheet.QueryTables.item($Connector.name)
$query.TextFileOtherDelimiter = $delimiter
$query.TextFileParseType = 1
$query.TextFileColumnDataTypes = ,1 * $worksheet.Cells.Columns.Count
$query.AdjustColumnWidth = 1

# Execute & delete the import query
$query.Refresh()
$query.Delete()

# Save & close the Workbook as XLSX.
$workbook.SaveAs($xlsx)
$excel.Quit()

Filter columns of an excel file:

Now we will make use of a module PSExcel which makes data manipulation very easy.

import-module "C:\Program Files (x86)\WindowsPowerShell\Modules\PSExcel-master\PSExcel"

[threading.thread]::CurrentThread.CurrentCulture = 'en-US'

Import-XLSX $xlsx -sheet Sheet1 |
Where-Object {($_.'Team' -in @("KKR","KVIP","CSK"))`
-and ($_.'Status' -notin @("bench","not selected"))}|
Export-XLSX $xlsx1

Create Pivot table from a worksheet of an excel file:

Import-XLSX $xlsx1 -sheet Sheet1 |
Export-XLSX $xlsx2 -PivotRows "Team" -PivotColumns "Status" -PivotValues "Player name" -ChartType pie

Merging 2 excel worksheets over a common column

$lt = Import-XLSX $xlsx1 -Sheet Sheet2 -header "Name","Employee Number","Department"

$rt = Import-XLSX $xlsx2 -Sheet Worksheet2 -header "Name","Salary"

Join-Object -Left $lt -Right $rt -LeftJoinProperty "Name" -RightJoinProperty "Name" -type AllInBoth -RightProperties "Salary"|
Export-XLSX $xlsx3 -WorksheetName Mergedoutput

The other options of joining are:

1> -AllInLeft

2> -AllInRight

3> -AllInBoth

4> -OnlyIfInBoth

I’ll try to explore more operations that can be done specifically with xlsx files in part-2 of this series.

Outlook Automation

Although Microsoft does not recommend unattended automation of its office products, we can leverage powershell at automating some common outlook related operations.

Searching for a particular subject line and then downloading its attachments

$date = get-date -format d

$filepath = "C:\attachments\"
$attr = $date+"_report.csv"
$path = Join-Path $filepath $attr

$olFolderInbox = 6
[threading.thread]::CurrentThread.CurrentCulture = 'en-US'
$outlook = new-object -com outlook.application;
$ns = $outlook.GetNameSpace("MAPI");
$inbox = $ns.GetDefaultFolder($olFolderInbox)
$messages = $inbox.items

foreach($message in $messages){
$msubject = $message.subject
write-host "Messages Subject: $msubject"

if ($msubject.contains("Daily Report")){
$message.attachments|foreach {
Write-Host $_.filename
Write-Host "Attachment is saved at $path"
$_.saveasfile(($path))}
$TargetFolder = $inbox.Folders.Item('Processed')
$message.Move($TargetFolder)
break
}else{continue}
}

Prior to running above script we must create a folder “Processed” under “Inbox” of Outlook. Also we may have a hard time scheduling this on task scheduler as the account from which the script runs, should be logged in.

Sending Mail

$From = "abc@company.com"
$to = "xyz@company.com"
$Cc = "pqr@company.com"

$Subject = "Daily Report"
$Body = "PFA The Excel Report containing details."
$SMTPServer = "server01.company.corp"

Send-MailMessage -From $From -to $To -Cc $Cc -Subject $Subject `
-Body $Body -SmtpServer $SMTPServer -Attachments $path

write-host "Mail sent with report attached"

In above script we have not made use of COM object. As a result we can use this as unattended script scheduled on task scheduler. Also the execution is fast.

Now the same task can also be done via COM object as can be seen below.

$Username = "MyUserName";
$Password= "MyPassword";

function Send-ToEmail([string]$email, [string]$attachmentpath){

    $message = new-object Net.Mail.MailMessage;
    $message.From = "YourName@gmail.com";
    $message.To.Add($email);
    $message.Subject = "subject text here...";
    $message.Body = "body text here...";
    $attachment = New-Object Net.Mail.Attachment($attachmentpath);
    $message.Attachments.Add($attachment);

    $smtp = new-object Net.Mail.SmtpClient("smtp.gmail.com", "587");
    $smtp.EnableSSL = $true;
    $smtp.Credentials = New-Object System.Net.NetworkCredential($Username, $Password);
    $smtp.send($message);
    write-host "Mail Sent" ; 
    $attachment.Dispose();
 }
Send-ToEmail  -email "reciever@gmail.com" -attachmentpath $path;

The above script has been taken from a solution posted in how to send email with powershell