Insert CSV data into next available corresponding cell in Excel using Powershell

Question

I have a query exporting to CSV weekly which contains the same headers and column order as my excel data sheet. The powershell script will be on an event timer and i'd like it to grab the CSV and import the data in the next available cells with the corresponding header. I do not want it to overwrite the existing data, essentially its a growing table.

Environment: Powershell V3 w/ISE Steroids & Excel 2013

Here is my current code which is not working:

Add-Type -AssemblyName Microsoft.Office.Interop.Excel

$Excel = New-Object -ComObject Excel.Application
$xlLastCell = [Microsoft.Office.Interop.Excel.Constants]::xlLastCell
$xlFile = 'C:\Users\me\Desktop\Test\Agents Stats 2016.xlsx'
$csvFile = 'C:\Users\me\Desktop\Test\Data\AgentStats.csv'

$Excel.Visible = $true

$ExcelWordBook = $Excel.Workbooks.Open($xlFile)
$ExcelWorkSheet = $Excel.WorkSheets.item('rawdata')
$ExcelWorkSheet.activate()

$objRange = $ExcelWorkSheet.UsedRange
$lastRow = $objRange.SpecialCells($xlLastCell).Row
$ExcelWorkSheet.cells($lastRow,1).Select()

$AgentStats = Import-Csv -Path C:\Users\me\Desktop\Test\Data\AgentStats.csv
$AgentStats.foreach{
$lastRow += 1
$ExcelWorkSheet.cells($lastRow,1).value = $psitem.COUNT
$ExcelWorkSheet.cells($lastRow,2).value = $psitem.STATUS
$ExcelWorkSheet.cells($lastRow,3).value = $psitem.OPERATOR
$ExcelWorkSheet.cells($lastRow,4).value = $psitem.PRODUCT
$ExcelWorkSheet.cells($lastRow,5).value = $psitem.WEEK
}

$ExcelWordBook.Save()
$ExcelWordBook.Close()
$Excel.Quit()

I'm receiving the following errors and I can't figure out why:

At line:19 char:20 + $AgentStats.foreach{ + ~ Unexpected token '{' in expression or statement. + CategoryInfo : ParserError: (:) [], ParentContainsErrorRecordException + FullyQualifiedErrorId : UnexpectedToken

[16,1] Method invocation failed because [System.__ComObject] doesn't contain a method named 'cells'.

[21,3] Method invocation failed because [System.__ComObject] doesn't contain a method named 'cells'.

[22,3] Method invocation failed because [System.__ComObject] doesn't contain a method named 'cells'.

[23,3] Method invocation failed because [System.__ComObject] doesn't contain a method named 'cells'.

[24,3] Method invocation failed because [System.__ComObject] doesn't contain a method named 'cells'.

[25,3] Method invocation failed because [System.__ComObject] doesn't contain a method named 'cells'.

I've googled these errors for the past day and I've fooled around with the "Foreach" to not avail. However the "cells com object" is legitimate and does exist, I can't for the life of me figure out why that error is occurring.

Thanks in advance for the help, i'm new to Powershell.

restless1987 restless1987 · Accepted Answer · 2016-03-15T15:20:26

1) You used the wrong paranthesis:

$AgentStats.foreach( #Not the squiggelies
$lastRow += 1
$ExcelWorkSheet.cells($lastRow,1).value = $psitem.COUNT
$ExcelWorkSheet.cells($lastRow,2).value = $psitem.STATUS
$ExcelWorkSheet.cells($lastRow,3).value = $psitem.OPERATOR
$ExcelWorkSheet.cells($lastRow,4).value = $psitem.PRODUCT
$ExcelWorkSheet.cells($lastRow,5).value = $psitem.WEEK
)

2) I think your object-declaration is not correct:

$ExcelWordBook = $Excel.Workbooks.Open($xlFile)
$ExcelWorkSheet = $ExcelWordbooks.item('rawdata') #here, use your excelwordbooks-object
$ExcelWorkSheet.activate() #Not necessary

Insert CSV data into next available corresponding cell in Excel using Powershell

1 Answers