excel - Powershell 导出 CSV 看起来很奇怪

Question

我在使用 powershell 将 CSV 导出到 Excel 时遇到问题。当我导入时，它看起来很糟糕，我找不到任何可以帮助我解决它的信息。
在这里我附上导入的图像和代码。我看到其他 CSV 导入，它的类别在 Excel 中按行分隔，看起来很正常，但我不知道该怎么做。

我的工作簿的图像

 $Computers = Get-ADComputer -Filter {OperatingSystem -like "*Server*"} -Properties OperatingSystem | Select-Object -ExpandProperty Name
   Foreach($computer in $computers){
if(!(Test-Connection -Cn $computer -BufferSize 16 -Count 1 -ea 0 -quiet))
  {write-host "cannot reach $computer offline" -f red}
   else {
$outtbl = @()
Try{
$sr=Get-WmiObject win32_bios -ComputerName $Computer  -ErrorAction Stop 
$Xr=Get-WmiObject –class Win32_processor -ComputerName $computer -ErrorAction Stop   
$ld=get-adcomputer $computer -properties Name,Lastlogondate,operatingsystem,ipv4Address,enabled,description,DistinguishedName -ErrorAction Stop
$r="{0} GB" -f ((Get-WmiObject Win32_PhysicalMemory -ComputerName $computer |Measure-Object Capacity  -Sum).Sum / 1GB)
$x = gwmi win32_computersystem -ComputerName $computer |select @{Name = "Type";Expression = {if (($_.pcsystemtype -eq '2')  ) 

{'Laptop'} Else {'Desktop Or Other something else'}}},Manufacturer,@{Name = "Model";Expression = {if (($_.model -eq "$null")  ) {'Virtual'} Else {$_.model}}},username -ErrorAction Stop
$t= New-Object PSObject -Property @{
 serialnumber = $sr.serialnumber
 computername = $ld.name
 Ipaddress=$ld.ipv4Address
 Enabled=$ld.Enabled
 Description=$ld.description
 Ou=$ld.DistinguishedName.split(',')[1].split('=')[1] 
 Type = $x.type
 Manufacturer=$x.Manufacturer
 Model=$x.Model
 Ram=$R
 ProcessorName=($xr.name | Out-String).Trim()
 NumberOfCores=($xr.NumberOfCores | Out-String).Trim()
 NumberOfLogicalProcessors=($xr.NumberOfLogicalProcessors | Out-String).Trim()
 Addresswidth=($xr.Addresswidth | Out-String).Trim()
 Operatingsystem=$ld.operatingsystem
 Lastlogondate=$ld.lastlogondate
 LoggedinUser=$x.username
 }
 $outtbl += $t
 }
 catch [Exception]
 {
     "Error communicating with $computer, skipping to next"   
 }
$outtbl | select Computername,enabled,description,ipAddress,Ou,Type,Serialnumber,Manufacturer,Model,Ram,ProcessorName,NumberOfCores,NumberOfLogicalProcessors,Addresswidth,Operatingsystem,loggedinuser,Lastlogondate |export-csv -Append C:\temp\VerynewAdinventory.csv -nti
}
}

标签： excelpowershell

Answer 1

正如所评论的，您的区域设置计算机使用默认使用的不同分隔符Export-Csv（即逗号）。
您可以检查您的计算机（以及您的 Excel）使用的字符，如下所示：

[cultureinfo]::CurrentCulture.TextInfo.ListSeparator

要以一种可以简单地双击输出 csv 文件以在 Excel 中打开的方式使用 Export-Csv，您需要将开关附加-UseCulture到它，或者通过附加参数告诉它分隔符应该是什么，如果不是逗号，-Delimiter后跟你从上面的代码行得到的字符。

也就是说，您的代码不会生成完整的表格，因为导出到 csv 文件的位置错误。正如Palle Due评论的那样，如果您正确缩进代码，您可能已经看到了。

另外，我建议使用更多的自描述变量名称，例如 not $ror $x， but $memoryand $machine。

如今，您应该避免使用Get-CimInstance而不是Get-WmiObject
添加到数组中，+=因为它既耗时又耗内存。（每次添加到固定大小的数组时，都必须在内存中重建整个数组）。

您的代码已修改：

# set the $ErrorActionPreference to Stop, so you don't have to add -ErrorAction Stop everywhere in the script
# remember the currens value, so you can restore that afterwards.
$oldErrorPref = $ErrorActionPreference
$ErrorActionPreference = 'Stop'

# get an array of computers, gathering all properties you need
$computers = Get-ADComputer -Filter "OperatingSystem -like '*Server*'" -Properties OperatingSystem, LastLogonDate, IPv4Address, Description
$result = foreach ($computer in $computers) {
    $serverName = $computer.Name
    if(!(Test-Connection -ComputerName $serverName -BufferSize 16 -Count 1 -ErrorAction SilentlyContinue -Quiet)) {
        Write-Host "cannot reach $serverName offline" -ForegroundColor Red
        continue  # skip this computer and proceed with the next one
    }

    try {
        # instead of Get-WmiObject, nowadays you should use Get-CimInstance
        $bios      = Get-WmiObject -Class Win32_bios -ComputerName $serverName
        $processor = Get-WmiObject -Class Win32_Processor -ComputerName $serverName 
        $memory    = Get-WmiObject -Class Win32_PhysicalMemory -ComputerName $serverName
        $disks     = Get-WmiObject -Class Win32_LogicalDisk -ComputerName $serverName
        $machine   = Get-WmiObject -Class Win32_ComputerSystem -ComputerName $serverName | 
                     Select-Object @{Name = "Type"; Expression = {
                                        if ($_.pcsystemtype -eq '2') {'Laptop'} else {'Desktop Or Other something else'}}
                                   },
                                   Manufacturer,
                                   @{Name = "Model"; Expression = {
                                        if (!$_.model) {'Virtual'} else {$_.model}}
                                   },
                                   UserName
        # output an object to be collected in variable $result
        # put the properties in the order you would like in the output
        [PsCustomObject] @{
            ComputerName              = $serverName
            Enabled                   = $computer.Enabled
            Description               = $computer.description
            IpAddress                 = $computer.IPv4Address
            Ou                        = $computer.DistinguishedName.split(',')[1].split('=')[1]
            Type                      = $machine.type
            SerialNumber              = $bios.serialnumber
            Manufacturer              = $machine.Manufacturer
            Model                     = $machine.Model
            Ram                       = '{0} GB' -f (($memory | Measure-Object Capacity -Sum).Sum / 1GB)
            ProcessorName             = $processor.Name
            NumberOfCores             = $processor.NumberOfCores
            NumberOfLogicalProcessors = $processor.NumberOfLogicalProcessors
            Addresswidth              = $processor.Addresswidth
            OperatingSystem           = $computer.OperatingSystem
            # {0:N2} returns the number formatted with two decimals 
            TotalFreeDiskSpace        = '{0:N2} GB' -f (($disks | Measure-Object FreeSpace -Sum).Sum / 1GB)
            LoggedInUser              = $machine.UserName
            Lastlogondate             = $computer.LastLogonDate
        }
    }
    catch {
        Write-Warning "Error communicating with computer $serverName, skipping to next"   
    }
}
# restore the ErrorActionPreference to its former value
$ErrorActionPreference = $oldErrorPref

# output the completed array in a CSV file 
# (using the delimiter characer your local machine has set as ListSeparator)
$result | Export-Csv -Path 'C:\temp\VerynewAdinventory.csv' -UseCulture -NoTypeInformation