Converting output from a NoSql database to joined and structured CSV in Powershell

I was tasked to export data from Microsoft Graph about users to CSV in order to build reports in Qlickview.

The problem I had was that the user object in Microsoft Graph are polymorphic, meaning that the output we get are not required to have the same properties for each of the user object we get returned. When we then try to convert the return data to CSV using the builtin ”Convertto-Csv” or ”Export-CSV” cmdlets, they only build headers from the first object, which means much of the data of the other objects might get truncated. The solution to the problem seems quite simple in my head, but when I did a quick google search for the problem I didn’t find any relevant results.

So, here is my solution.

First I get all the properties in a big array for all the user objects. I do this by running Get-Member on each of the objects, and then join in the result with a Select -Unique. When the headers are all found and sorter I write them to my csv file.

After that I compile an expression to run on each of the objects, to expand the properties that exist, and fill the blanks with null, The expression outputs a commaseparated string by default that matches the headers we generated earlier.

When the expression is prepared, we need to execute it and append the output to the csv file.

So even though this was only 4 rows, it helped me to convert the nosql structured output to a structured CSV without losing any data on the way.

Hope this helps someone!

Full code below.


Disclaimer: All information on this blog is offered "as is" with no warranty. It is strongly recommended that you verify all information and validate all scripts in isolated test environments before using them in production environments.