Help Exporting Array of comma separated values in a String to CSV file

Question

I have written a script that gets details of AIP labels being downgraded from the unified audit log.
A loop examines each relevant row from the audit log converted from JSON and held in a custom Object $audit
The required details are extracted from $audit and held as comma separated values in a text string in $message that is added to an array $ROut

$message = @()  
$message = "'" + $audit.UserId + '","' + $audit.ObjectID +'",' + $audit.CreationTime +',"' + $oldLabel + '","' + $newLabel + '","' + $audit.SensitivityLabelJustificationText + '",' + $audit.Sensitivitylabeleventdata.OldSensitivityLabelOwnerEmail + ","  + $audit.Sensitivitylabeleventdata.SensitivityLabelOwnerEmail  
$ROut += $message  

I end up with an array $ROut that contains values separated by a comma (with some in side double quotes - where there is a chance the text could itself contain a comma as user input)
Looks like:
'name@keyman .com","OneDrive","https://domain-my.sharepoint.com/personal/user_domain_com/Documents/Document5.docx",2022-04-28T14:55:46,"Public","Internal","Previous%20label%20was%20incorrect",oldname@keyman .com,newname@keyman .com

If I save the file as .txt using Out-File it can be opened manually in Excel specifying comma as separator
if I save with a .csv extension, the file opens in Excel but ignores the commas and puts all wavlues int he first cell in each row
Export-CSV doesn't help as only export the string length.

How is best to capture the details required from $audit so that they can be exported as a csv file?

thanks
Graham

Answer 1

Hi GrahamPullenUK-5045,

A csv file will always display the data in the way you’re describing when opened in excel, unless you specify to excel that the file is comma delimited. If you have commas in the text that do not want to be treated as comma separators, you may need to enclose them in quotes specifically, or choose a different delimiter, like. ; or :

I hope this answers your question.

Thanks.

---

--If the reply is helpful, please Upvote and Accept as answer--

Answer 2

I can't verify that this will work without at least on set of JSON data to work with, but give it a try. If it doesn't work, post the JSON you used to create the $audit object:

$details = $audit | 
                Select-Object   UserId,
                                ObjectID,
                                CreationTime,
                                @{n='oldlabel';e={$oldlabel}},
                                @{n='newlabel';e={$newlabel}},
                                SensitivityLabelJustificationText,
                                @{n='OldSensitivityLabelOwnerEmail';e={$_.Sensitivitylabeleventdata.OldSensitivityLabelOwnerEmail}},
                                @{n='SensitivityLabelOwnerEmail';e={$_.Sensitivitylabeleventdata.SensitivityLabelOwnerEmail}}