Append Excel file with import-csv

This topic contains 23 replies, has 3 voices, and was last updated by Profile photo of H Man H Man 1 year, 11 months ago.

  • Author
    Posts
  • #21261
    Profile photo of H Man
    H Man
    Participant

    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()
    
    
  • #21265
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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.

  • #21271
    Profile photo of H Man
    H Man
    Participant

    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

  • #21275
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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()
    
  • #21279
    Profile photo of H Man
    H Man
    Participant

    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 😉

  • #21284
    Profile photo of H Man
    H Man
    Participant

    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

  • #21285
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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

  • #21305
    Profile photo of H Man
    H Man
    Participant

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

    Thx again Tim!!

  • #21340
    Profile photo of H Man
    H Man
    Participant

    Hey Tim quick ?

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

  • #21349
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    Hide as in the whole of Excel becomes invisble, or just the worksheet? Both of them work the same way, by setting a [i]visible[/i] 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
    
  • #21373
    Profile photo of H Man
    H Man
    Participant

    Thanks Tim

  • #21374
    Profile photo of H Man
    H Man
    Participant

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

  • #21385
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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

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

    for some more details.

  • #21387
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    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.

  • #21398
    Profile photo of H Man
    H Man
    Participant

    Thanks so much!

  • #21404
    Profile photo of H Man
    H Man
    Participant

    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 🙂

  • #21413
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

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

    [i]You need to specify the full namespace in PowerShell, which you don't in Excel VBA[/i]

    [b]Excel[/b]
    ActiveCell.FormulaR1C1 = "a"

    [b]PowerShell [/b]
    (using the example from above with $excel already defined)
    $excel.Application.ActiveCell.FormulaR1C1 = "a"

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

  • #21414
    Profile photo of H Man
    H Man
    Participant

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

    • #21419
      Profile photo of Tim Pringle
      Tim Pringle
      Participant

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

  • #21420
    Profile photo of H Man
    H Man
    Participant

    Well if we don't speak before Happy New Year

  • #21422
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    you too mate.

  • #21439
    Profile photo of H Man
    H Man
    Participant

    Hey Tim one other quick ?

    How do you hide a specfic columns in a worksheet

  • #21503
    Profile photo of Tim Pringle
    Tim Pringle
    Participant

    lol, this is turning into a bit of an ultra thread. If this was Technet, I'd have 150000000 points and a handwritten gold embossed card from Satya and Bill 😉

    Using the variables from before, you'd do something like below. It selects a cell that is on the same column to hide, then sets the Hidden attribute for the column concerned.

    $ExcelWorkSheet.cells(5,5).select()
    $excel.Application.Selection.EntireColumn.Hidden = $true
    
  • #21547
    Profile photo of H Man
    H Man
    Participant

    lol,You deserve 150000000 points!

    Thanks so Much

    Happy New Year

You must be logged in to reply to this topic.