Powershell 2.0 write back to xlsx.

This topic contains 4 replies, has 4 voices, and was last updated by Profile photo of Martin Nielsen Martin Nielsen 2 years, 5 months ago.

  • Author
    Posts
  • #16513
    Profile photo of Doms
    Doms
    Participant

    I am trying to have Powershell read an xlsx for username info, convert to a csv (to be imported) and then write back something to the xlsx so next time it won't reimport the same users.

    I don't want to delete the users in the xlsx but am thinking to add a date column or some other data and have powershell write this data in the column. But then I would have to have my script ignore the date field if it is not null maybe?

    Current xlsx columns headers
    A B C
    1 Full Name Personal Email write back data after import
    2 John Doe Jdoe@gmail.com
    3 Don Juan Djuan@gmail.com

    Convert to csv code (This part is working fine.)

    $File = "C:\Scripts\Excel\Accounts.xlsx"
    $Savepath1 = "C:\Scripts\Csv\Employee Accounts.csv"
    $SheetName1 = "Employee Accounts"
    $ObjExcel = New-Object -ComObject Excel.Application
    $Objexcel.Visible = $false
    $Objworkbook=$ObjExcel.Workbooks.Open($File)
    $Objworksheet=$Objworkbook.worksheets.item($Sheetname1)
    $Objworksheet.Activate()
    $Objexcel.application.DisplayAlerts= $False
    $Objworkbook.SaveAs($SavePath1,6)
    $Objworkbook.Close($True)
    $ObjExcel.Quit()

    Here is my current import-csv code

    $EmployeeAccounts = Import-Csv "C:\Scripts\CSV\Employee Accounts.csv" | Where-Object { $_.Fullname -and $_.PersonalEmail}

    If I added a script ran date column do i have to add more code to my import-csv where object "new date info" is empty or null so the users with script ran dates would be ignored?

    Things to consider:
    There might be additional concatenated info in additional fields added to the xlsx. Therefore excel might count these as used rows if the fields have the concatenated formulas in them. So I only want to write the data to the new column if there is a username and email address in columns A & B.

    Confused what the best way to do this is.

    Thanks!

  • #16519
    Profile photo of Adam Bertram
    Adam Bertram
    Participant

    It's been my experience that, if you can get away with it, it's MUCH easier to read/write to a CSV vs. using a COM object to manipulate an Excel file.

  • #16520
    Profile photo of Doms
    Doms
    Participant

    I agree but the original xlsx has more going on that csv won't allow. formulas, tables with dropdowns to pick usertype etc.

  • #16535
    Profile photo of Daniel Krebs
    Daniel Krebs
    Participant

    I understand you would like to work with the original Excel file. My suggestion would be to look into using Excel to get the data, update as required and save back to the same file. Unfortunately I haven't found a full example to work directly with Excel files using PowerShell but below links might get you started.

    http://import-powershell.blogspot.co.uk/2012/03/excel-part-1.html

    https://excelpslib.codeplex.com/

  • #16537
    Profile photo of Martin Nielsen
    Martin Nielsen
    Participant

    I'd suggest that you try looking into feeding data to a SQL database, then have Excel consume that data, instead of trying to have PowerShell modify Excel documents directly. There really isn't any smart PowerShell-Excel interactivity as far as I know. Especially considering any sort of COM interaction has a ton of works-only-on-my-PC gotchas.

You must be logged in to reply to this topic.