Powershell 2.0 write back to xlsx.

PowerShell Help
1

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!

2

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.

3

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

4

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.

https://excelpslib.codeplex.com/

5

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.