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

Graham Pullen (UK) 41 Reputation points
2022-05-24T10:19:55.907+00:00

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

Windows Server PowerShell
Windows Server PowerShell
Windows Server: A family of Microsoft server operating systems that support enterprise-level management, data storage, applications, and communications.PowerShell: A family of Microsoft task automation and configuration management frameworks consisting of a command-line shell and associated scripting language.
5,362 questions
0 comments

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Rich Matheisen 44,776 Reputation points
    2022-05-24T14:43:38.213+00:00

    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}}
    0 comments
  2. Limitless Technology 43,931 Reputation points
    2022-05-26T07:44:19.8+00:00

    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--

    0 comments