How to build a failsafe Custom Property update script using PowerShell

I like this as a little side project in my (not so) free time, but can I get some clarity on a few things?

Questions:

1. Are you married to the XLS format, because opening that in PowerShell can be difficult (because it relies on the Excel COM object).  If you can do the same with a CSV, you'd be better off since it's natively supported and could easily be edited with Excel.
2. Can you give a short example of what the source file would look like?  I don't want to make assumptions.
3. Are we only talking about node custom properties? 

The pseudocode I'm thinking is this:

• Open a new text file for the log (C:\Make\cp_updates_yyyy-MM-dd.log) to log what's happening.
• Open CSV file.
• Build connection to Orion server.
• For each row:
  
  • Run a Get-SwisData query the Orion.Nodes.CustomProperties bound to that IP address (assume we only have one return)
  • Check to see if the CP's match.  If so, take note (for logging), but do nothing else.
  • If the CP's don't match, then execute Set-SwisObject with the CP's that need to be updated (also log these changes).
• Write a 'complete' message to the log.

In the Windows Scheduled Task, create a new custom task, which uses powershell.exe as the executable and -file C:\Path\ToScript.ps1 as the arguments.  Schedule it to run at a convenient time daily.

You could also write the inverse of this to backup the current custom properties to a file.

Hi KMSigma.SWI,

Soz for the very long time to reply. We've been back and forth internally contemplating what, how and when to update data into which format and we have concluded that the attached file would best represent the final output. 1. No we're not anymore. 2. Please find it attached. 3. Yes, only custom properties for now.

API_NODES.csvColumns: Entity (I know, it already exists in Orion, that's an added challenge), Purchase and Service are what should be imported to Orion.

Do you have a ready to use ps script that could achieve this?

I don't have a PoSH script handy to do that right at the moment, but I'm working towards it.

Right now, what I have is a script that dumps all Custom Properties (of every kind) to CSV files.

SwqlQueries/Export-SwisCp.ps1 at main · kmsigma/SwqlQueries (github.com)

Now that I have the exports in a standardized format, I can work on the next phase (import/update).