Append Excel file with import-csv

I have an existing workbook that I would like to append a worksheet with in the book starting from the next available row

I would like to import a csv file with exaclty the same headers as the book, this would be the data source

Any Suggestions

$csv = Join-Path $env:TEMP "input.csv"
$xls = Join-Path $env:TEMP "output.xlsx"

$xl = New-Object -COM "Excel.Application"
$xl.Visible = $true

$wb = $xl.Workbooks.Add()
$ws = $wb.Sheets.Item(1)

$ws.Cells.NumberFormat = "@"

$i = 1
Import-Csv $csv | % {
  $j = 1
  foreach ($prop in $_.PSObject.Properties) {
    if ($i -eq 1) {
      $ws.Cells.Item($i, $j++).Value = $prop.Name
    } else {
      $ws.Cells.Item($i, $j++).Value = $prop.Value
    }
  }
  $i++
}

$wb.SaveAs($xls, 51)
$wb.Close()

Hey fella,

If you go on the basis that your headers in the CSV match up exactly with the existing spreadsheet, then I guess a way to start when you need to append is going to be to identify the last row.

Once you’ve opened up the existing spreadsheet, then you could add something like this :-

Add-Type -AssemblyName Microsoft.Office.Interop.Excel
$xlLastCell = [Microsoft.Office.Interop.Excel.Constants]::xlLastCell
$objRange = $ws.UsedRange
$lastRow = $objRange.SpecialCells($xlLastCell).Row
$xl.cells($lastRow,1).Select()

I’m a visual junkie, so usually add the Select method when I’m putting data in spreadsheets from a script. So last last isn’t necessary.

Once you’ve got the $lastRow variable, then all you should need to do is use that as the beginning value in your For…Each loop, and add 1 each time.

Hey Tim!

Thats exactly what im looking to do . Now how do i get the CSV file exported to the $lastRow?

I was looking at something like this

$ExcelPath = $xls
$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$ExcelWordBook = $Excel.Workbooks.Open($xls)
$ExcelWorkSheet = $Excel.WorkSheets.item("rawdata")
$ExcelWorkSheet.activate()

$lastRow = $ExcelWorkSheet.UsedRange.rows.count + 1
$Excel.Range("A" + $lastrow).Activate()
$ExcelWorksheet.Paste()

But i would need to copy the csv 1st tten paste

can you please shoe me how to export the csv with the foreach loop you mentioned

As Always thank you

OK, here’s something to try. I tested this out on a really unimaginative excel and csv file. I’ve attached both, just remember to rename them as required.

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

$Excel = New-Object -ComObject Excel.Application
$xlLastCell = [Microsoft.Office.Interop.Excel.Constants]::xlLastCell
$xlFile = 'C:\Temp\Singers.xlsx'
$csvFile = 'C:\Temp\singers.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()

$singers = Import-Csv -Path C:\temp\singers.csv
$singers.foreach{
  $lastRow += 1
  $ExcelWorkSheet.cells($lastRow,1).value = $psitem.Salutation
  $ExcelWorkSheet.cells($lastRow,2).value = $psitem.Forename
  $ExcelWorkSheet.cells($lastRow,3).value = $psitem.Surname
}

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

wow! Tim that worked Perfectly

Amazed as usual and thanks sooooooooooooo much

I am going to work on my project now I will let you know how it come out

If you ever make it to Miami, Fl One Steak Dinner on me :wink:

i thing Tim

my work book does skip columns

One thing the way the book is set up i have to skip some columns… its spread out

I am getting this error

WARNING: One or more headers were not specified. Default names starting with “H” have
been used in place of any missing headers.

how should I handle it if I have to go from lets say A B C … and then F G … H

Steaks always good!

If your headers are split, then all you really need to do different should be to specify the different columns e.g.

#Column A
$ExcelWorkSheet.cells($lastRow,1).value = $psitem.Salutation
#Column D
$ExcelWorkSheet.cells($lastRow,4).value = $psitem.Forename
#Column F
$ExcelWorkSheet.cells($lastRow,6).value = $psitem.Surname

I hope it’s you but I just sent you a linkden request

Thx again Tim!!

Hey Tim quick ?

Do you know of a way to hide the sheet when complete?

Hide as in the whole of Excel becomes invisble, or just the worksheet? Both of them work the same way, by setting a visible property to $false to hide them

If it’s the whole of Excel, then just use $Excel.Visble = $false
It’s it’s just one worksheet, you can only hide it if there already exists another worksheet in the same document. Then all you do i set the visble property of the worksheet to false.

$ExcelWorkSheet = $Excel.WorkSheets.item('sheet1')
$ExcelWorkSheet.activate()
$ExcelWorkSheet.Visible = $false

Thanks Tim

One more thing , any way to password protect/unprotect the book/sheet to prevent editing?

From the first example, you could do something like this after you’ve created the $wb variable

$wb.Protect(‘yourpassword’,$true,$true)

$wb.Unprotect(‘yourpassword’)

There’s multiple options for protecting parts of excel and its documents. Take a look at

http://msdn.microsoft.com/en-us/library/office/ff193800%28v=office.15%29.aspx

for some more details.

I’d meant to say, you can also get Excel to generate the VBA code for a good number of options, which you can then adapt to PowerShell as required. I’ve attached a file with some pictures of the steps below. Rename it to .ZIP to get the files.

  • Right Click on the Ribbon
  • Select Customize Ribbon
  • Tick the Developer Option (If you don’t see it on the right hand side, select Main Tabs on the left hand side, Click Developer, Click Add, then put a tick in it, and click OK) - Step 1
  • Click on the Developer ribbon menu item
  • There’s now a Record Macro option. Click on it - Step 2
  • You’ll now be prompted to give the macro a name. Choose one and click on OK - Step 3
  • Do what you need to do
  • Select Stop Macro - Step 4
  • Hold down ALT and press F11. This takes you to the VBA screen
  • Expand the tree until you can see Module1 below the Modules folder. Double click on it.
  • On the right hand side, you can now see the VBA code. - Step 5

Then, you’ll just likely need to make a few changes to PowerShellize (just made that word up…) it, and it should be good to go.

Thanks so much!

wow Awsome Technique

can you show me how you would " PowerShellize" some vba code to run in a Powershel Script

I saw the pics in your zip file

thanks Tim :slight_smile:

Writing an article about that in the near future, but some stuff for now.

You need to specify the full namespace in PowerShell, which you don’t in Excel VBA

Excel
ActiveCell.FormulaR1C1 = “a”

PowerShell
(using the example from above with $excel already defined)
$excel.Application.ActiveCell.FormulaR1C1 = “a”

Excel already knows the name and values of constants, while you need to declare them differently in PowerShell
[Microsoft.Office.Interop.Excel.Constants]::xlLastCell

Sounds very interesting please let me know when your finished with your article. Where will the article be posted?

Will get it finished first, and then see from there. :slight_smile:

Well if we don’t speak before Happy New Year