如何让Excel自动识别Unicode编码CSV文件的数据列？

DevOps 小满

2026-6-15

问题

我有一段PowerShell脚本（见下方「原始脚本」），可提取目录中的文件路径列表及各类文件元数据并输出为CSV文件。但打开这个Unicode编码的CSV时，必须先把"",""和,""替换成罕见字符（比如∞），再执行文本分列才能让数据归到对应列。我想省去查找替换和文本分列的步骤，试过以下方法但都不行：

移除-Encoding Unicode：Excel能自动分列，但文件路径里的特殊字符会变成问号，没法用于后续移动命令，不可行；
修改脚本最后一行为$a = Get-FolderItem "Z:\Folder" | Export-Csv -Path C:\Folder\2024-12-23Testing.csv -Encoding Unicode -Delimiter ','：无效；
在脚本末尾加查找替换步骤：

(Get-Content C:\Folder\2024-12-23Testing.csv).Replace('"",""', '¬') | Set-Content C:\Folder\2024-12-23Testing.csv 
(Get-Content C:\Folder\2024-12-23Testing.csv).Replace(',""', '¬') | Set-Content C:\Folder\2024-12-23Testing.csv

这个方法能完成替换，但还是得手动执行耗时的文本分列操作。试了多种Unicode分隔符，也没法实现自动分列。

「原始脚本」

Function Get-FolderItem {
    
        [cmdletbinding(DefaultParameterSetName='Filter')]
        Param (
            [parameter(Position=0,ValueFromPipeline=$True,ValueFromPipelineByPropertyName=$True)]
            [Alias('FullName')]
            [string[]]$Path = $PWD,
            [parameter(ParameterSetName='Filter')]
            [string[]]$Filter = '*.*',    
            [parameter(ParameterSetName='Exclude')]
            [string[]]$ExcludeFile,              
            [parameter()]
            [int]$MaxAge,
            [parameter()]
            [int]$MinAge
        )
        Begin {
            $params = New-Object System.Collections.Arraylist
            $params.AddRange(@("/L","/E","/NJH","/BYTES","/FP","/NC","/XJ","/R:0","/W:0","T:W","/UNILOG:c:\temp\test.txt"))
            If ($PSBoundParameters['MaxAge']) {
                $params.Add("/MaxAge:$MaxAge") | Out-Null
            }
            If ($PSBoundParameters['MinAge']) {
                $params.Add("/MinAge:$MinAge") | Out-Null
            }
        }
        Process {
            ForEach ($item in $Path) {
                Try {
                    $item = (Resolve-Path -LiteralPath $item -ErrorAction Stop).ProviderPath
                    If (-Not (Test-Path -LiteralPath $item -Type Container -ErrorAction Stop)) {
                        Write-Warning ("{0} is not a directory and will be skipped" -f $item)
                        Return
                    }
                    If ($PSBoundParameters['ExcludeFile']) {
                        $Script = "robocopy `"$item`" NULL $Filter $params /XF $($ExcludeFile  -join ',')"
                    } Else {
                        $Script = "robocopy `"$item`" NULL $Filter $params"
                    }
                    Write-Verbose ("Scanning {0}" -f $item)
                    Invoke-Expression $Script | Out-Null
                    get-content "c:\temp\test.txt" | ForEach {
                        Try {
                            If ($_.Trim() -match "^(?<Children>\d+)\s(?<FullName>.*)") {
                               $object = New-Object PSObject -Property @{
                                    FullName = $matches.FullName
                                    #Extension = $matches.fullname -replace '.*\.(.*)','$1'
                                    #FullPathLength = [int] $matches.FullName.Length
                                    #FileHash = Get-FileHash -LiteralPath "\\?\$($matches.FullName)" |Select -Expand Hash
                                    #Created = ([System.IO.FileInfo] $matches.FullName).creationtime
                                    #Created = ([System.IO.FileInfo] "\\?\$($matches.FullName)").creationtime
                                    LastWriteTime = ([System.IO.FileInfo] "\\?\$($matches.FullName)").LastWriteTime
                                    #Characters = (Get-Content -LiteralPath "\\?\$($matches.FullName)" | Measure-Object -ignorewhitespace -Character).Characters
                                    #Size = ([System.IO.FileInfo] "\\?\$($matches.FullName)").length
                                    #Access = ((Get-Acl -LiteralPath "\\?\$($matches.FullName)") |Select -Expand AccessToString)-replace '[\r\n]',' '
                                    #$permission = (Get-Acl $Folder).Access | ?{$_.IdentityReference -match $User} | Select IdentityReference,FileSystemRights
                                    #Owner = (Get-ACL $matches.Fullname).Owner
                                } 
                                $object.pstypenames.insert(0,'System.IO.RobocopyDirectoryInfo')
                                Write-Output $object
                            } Else {
                                Write-Verbose ("Not matched: {0}" -f $_)
                            }
                        } Catch {
                            Write-Warning ("{0}" -f $_.Exception.Message)
                            Return
                        }
                    }
                } Catch {
                    Write-Warning ("{0}" -f $_.Exception.Message)
                    Return
                }
            }
        }
    }
    
 $a = Get-FolderItem "Z:\Folder" | Export-Csv -Path C:\Folder\2024-12-23Testing.csv -Encoding Unicode

解决方案

核心问题分析

你的CSV格式异常本质是Robocopy输出解析时可能引入了多余引号或格式问题，或者Export-Csv生成的Unicode CSV未被Excel正确识别为标准格式。以下是可行修复方案：

方案1：修正Robocopy输出的解析逻辑

原脚本通过[System.IO.FileInfo]获取LastWriteTime会额外触发文件访问，且正则匹配可能导致属性值带多余引号。改用Robocopy输出的时间直接提取，同时清理路径引号：

# 替换原ForEach中的正则匹配块
get-content "c:\temp\test.txt" | ForEach {
    Try {
        # 匹配Robocopy输出的完整格式：数字 + 路径 + 时间
        If ($_.Trim() -match "^(?<Children>\d+)\s(?<FullName>.+?)\s+(?<LastWriteTime>\d{4}/\d{2}/\d{2}\s+\d{2}:\d{2})") {
           $object = New-Object PSObject -Property @{
                FullName = $matches.FullName.Trim('"')  # 强制移除路径两端的多余引号
                LastWriteTime = [datetime]$matches.LastWriteTime
            } 
            $object.pstypenames.insert(0,'System.IO.RobocopyDirectoryInfo')
            Write-Output $object
        } Else {
            Write-Verbose ("Not matched: {0}" -f $_)
        }
    } Catch {
        Write-Warning ("{0}" -f $_.Exception.Message)
        Return
    }
}

方案2：生成带正确BOM的Unicode CSV

Excel对UTF-16编码的CSV需要BOM才能正确识别分隔符，改用ConvertTo-Csv + Out-File组合确保BOM正确：

# 替换原脚本最后一行
$outputPath = "C:\Folder\2024-12-23Testing.csv"
Get-FolderItem "Z:\Folder" | ConvertTo-Csv -NoTypeInformation | Out-File -FilePath $outputPath -Encoding Unicode

方案3：改用Tab作为分隔符（最稳定的Excel兼容方案）

逗号分隔易受路径中特殊字符干扰，改用Tab分隔符后，Excel能自动识别并分列，同时保留所有特殊字符：

# 修改Export-Csv参数
$outputPath = "C:\Folder\2024-12-23Testing.csv"
Get-FolderItem "Z:\Folder" | Export-Csv -Path $outputPath -Encoding Unicode -Delimiter "`t" -NoTypeInformation

使用说明：直接双击文件，Excel会自动按Tab分列，无需手动操作。

方案4：清理临时日志文件避免残留数据

Robocopy生成的c:\temp\test.txt如果未清理，多次运行会混入旧数据。在Begin块开头添加清理逻辑：

Begin {
    $logPath = "c:\temp\test.txt"
    # 清理旧日志文件
    If (Test-Path $logPath) { Remove-Item $logPath -Force }
    $params = New-Object System.Collections.Arraylist
    $params.AddRange(@("/L","/E","/NJH","/BYTES","/FP","/NC","/XJ","/R:0","/W:0","T:W","/UNILOG:$logPath"))
    # 其余代码不变
}

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