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:tempVerynewAdinventory.csv -nti
}
}

Answer 1

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

[cultureinfo]::CurrentCulture.TextInfo.ListSeparator

要使用Export Csv，只需双击输出Csv文件即可在Excel中打开，您需要将开关-UseCulture附加到该文件上，或者通过在参数-Delimiter后面加上从上面代码行获得的字符来告诉它分隔符应该是什么(如果不是逗号的话(。

也就是说，您的代码不会生成完整的表，因为导出到csv文件的位置错误。正如PalleDue所评论的，如果您正确地缩进代码，您就可以看到这一点。

此外，我建议使用更多自我描述的变量名，例如不要使用$r或$x，而是使用$memory和$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:tempVerynewAdinventory.csv' -UseCulture -NoTypeInformation