如何在PowerShell导出CSV文件时添加文本限定符？

Got it, let's fix this for you! You want each field wrapped in double quotes with a pipe (|) as the delimiter (like "Column 1"|"Column 2"), but your current script is messing up the quote structure—removing existing qualifiers and leaving the first column without proper wrapping. Here are two reliable ways to get the exact format you need:

Solution 1: Manually Construct Formatted Rows (Full Control) #

This approach gives you complete control over how each field is quoted, including handling values that contain double quotes (which need to be escaped as two double quotes per CSV standards):

Get-MsolUser -All | 
    Where-Object { $_.IsLicensed -and $_.Title -notlike "Volunteer" } | 
    Select-Object FirstName, LastName, Department, Title | 
    ForEach-Object {
        # Wrap each field in double quotes, escape internal quotes if present
        $formattedFields = $_.PSObject.Properties.Value | ForEach-Object {
            if ($null -eq $_) {
                '""'  # Handle empty/null values
            } else {
                # Escape existing double quotes by replacing " with ""
                $escapedValue = $_ -replace '"', '""'
                "`"$escapedValue`""
            }
        }
        # Join fields with pipe delimiter
        $formattedFields -join "|"
    } | Out-File "LicensedUsers.csv" -Encoding UTF8

Why this works: #

• It explicitly wraps every field in double quotes, no exceptions.
• Automatically escapes any double quotes within field values (e.g., a name like O"Neil becomes "O""Neil").
• Uses Out-File with UTF8 encoding to avoid character encoding issues.

Solution 2: Use ConvertTo-Csv with Regex Replacement (Simpler) #

If you prefer leveraging PowerShell's built-in CSV handling, you can first convert to standard CSV (with quotes) then swap the comma delimiter for a pipe—without breaking the quote structure:

Get-MsolUser -All | 
    Where-Object { $_.IsLicensed -and $_.Title -notlike "Volunteer" } | 
    Select-Object FirstName, LastName, Department, Title | 
    ConvertTo-Csv -NoTypeInformation | 
    ForEach-Object {
        # Replace only commas that are between double quotes with pipes
        $_ -replace '(?<="),(?=")', "|"
    } | Out-File "LicensedUsers.csv" -Encoding UTF8

Why this works: #

• ConvertTo-Csv automatically handles quoting and escaping for you (so you don't have to write logic for nulls or embedded quotes).
• The regex (?<="),(?=") uses lookbehind/lookahead to only replace commas that are directly between two double quotes—preserving all field-wrapping quotes.

What Was Wrong with Your Original Script? #

Your line $_ -replace '","', "|" replaces every occurrence of "," with |, which turns a line like "John","Doe","IT" into "John|Doe|IT". This leaves only the first field's opening quote and last field's closing quote, stripping quotes from all middle fields—and making the first column look like it's missing a closing qualifier. The regex in Solution 2 fixes this by targeting only the delimiter commas, not the quote-delimiter combinations.

内容的提问来源于stack exchange，提问作者Lee