Export-Csv

注意:本文来源于微软官网,并对原内容增加了一些实际应用的示例。原网址:https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-7.1

Export-Csv:将对象转换为一系列逗号分隔值 (CSV) 字符串并将这些字符串保存到文件中。

函数模型:

Export-Csv
      -InputObject <PSObject>
      [[-Path] <String>]
      [-LiteralPath <String>]
      [-Force]
      [-NoClobber]
      [-Encoding <Encoding>]
      [-Append]
      [[-Delimiter] <Char>]
      [-IncludeTypeInformation]
      [-NoTypeInformation]
      [-QuoteFields <String[]>]
      [-UseQuotes <QuoteKind>]
      [-WhatIf]
      [-Confirm]
      [<CommonParameters>]

描述

使用Export-CSVcmdlet 会创建指定的CSV 文件。在该文件中,包含以逗号分隔的对象属性值列表,每个查询对象都是一行。您可以使用Export-CSV cmdlet 创建表格并与接受 CSV 文件的程序共享数据。

在将对象发送到Export-CSVcmdlet之前不要对其进行格式化。如果Export-CSV接收格式化的对象,CSV 文件包含格式属性而不是对象属性。若要仅导出对象的选定属性(只导出部分选择的字段,而非全部字段),请使用Select-Object cmdlet。

例子

示例 1:将进程属性导出到 CSV 文件

本示例选择具有特定属性的Process对象,将对象导出到 CSV 文件。

Get-Process -Name WmiPrvSE | Select-Object -Property BasePriority,Id,SessionId,WorkingSet |
  Export-Csv -Path .\WmiData.csv -NoTypeInformation
Import-Csv -Path .\WmiData.csv

BasePriority Id    SessionId WorkingSet
------------ --    --------- ----------
8            976   0         20267008
8            2292  0         36786176
8            3816  0         30351360
8            8604  0         15011840
8            10008 0         8830976
8            11764 0         14237696
8            54632 0         9502720

Get-Process cmdlet 获取Process对象。该名称参数过滤输出只包括WMIPRVSE进程的对象。进程对象通过管道发送到 Select-Object cmdlet。Select-Object使用Property参数选择进程对象属性的子集。进程对象通过管道发送到Export-Csv cmdlet。Export-Csv将进程对象转换为一系列 CSV 字符串。

  • 路径 参数指定WmiData.csv文件保存在当前目录中。
  • NoTypeInformation 参数从 CSV 输出中删除 #TYPE 信息标头,在 PowerShell 6 中不需要。
  • Import-Csvcmdlet 使用Path参数显示位于当前目录中的文件(这句话的意思是使用Import-Csv -Path .\WmiData.csv这条命令将已经导出到WmiData.csv中的文件内容显示到当前运行窗口)。

下面这个测试中,没有NoTypeInformation,则在csv文件输出中有默认添加的文件头信息TYPE Selected.System.Diagnostics.Process

Get-Process -Name WmiPrvSE | Select-Object -Property BasePriority,Id,SessionId,WorkingSet |
>>   Export-Csv -Path .\WmiData1.csv

示例 2:将进程导出到逗号分隔的文件

此示例获取Process对象并将对象导出到 CSV 文件。

Get-Process | Export-Csv -Path .\Processes.csv -NoTypeInformation
Get-Content -Path .\Processes.csv

"Name","SI","Handles","VM","WS","PM","NPM","Path","Parent","Company","CPU","FileVersion", ...
"ApplicationFrameHost","4","511","2203597099008","35364864","21979136","30048", ...

Get-Process cmdlet 获取Process对象。Process对象通过管道发送到Export-Csv cmdlet。Export-CsvProcess对象转换为一系列 CSV 字符串。该路径参数指定Processes.csv文件保存在当前目录中。NoTypeInformation 参数从 CSV 输出中删除 #TYPE 信息标头,在 PowerShell 6 中不需要。Get-Content cmdlet使用路径参数来显示位于当前目录中的文件。

示例 3:将进程导出到分号分隔的文件

此示例获取Process对象并将对象导出到带有分号分隔符的文件中。

Get-Process | Export-Csv -Path .\Processes.csv -Delimiter ';' -NoTypeInformation
Get-Content -Path .\Processes.csv

"Name";"SI";"Handles";"VM";"WS";"PM";"NPM";"Path";"Parent";"Company";"CPU";"FileVersion"; ...
"ApplicationFrameHost";"4";"509";"2203595321344";"34807808";"21770240";"29504"; ...

Get-Process cmdlet 获取Process对象。Process对象通过管道发送到Export-Csv cmdlet。Export-CsvProcess对象转换为一系列 CSV 字符串。该路径参数指定Processes.csv文件保存在当前目录中。NoTypeInformation 参数从 CSV 输出中删除 #TYPE 信息标头,在 PowerShell 6 中不需要。Get-Content cmdlet 使用 Path 参数显示位于当前目录中的文件。

示例 4:使用当前区域性的列表分隔符导出进程

此示例获取Process对象并将对象导出到文件。分隔符是当前区域性的列表分隔符。

(Get-Culture).TextInfo.ListSeparator
Get-Process | Export-Csv -Path .\Processes.csv -UseCulture -NoTypeInformation
Get-Content -Path .\Processes.csv

"Name","SI","Handles","VM","WS","PM","NPM","Path","Parent","Company","CPU","FileVersion", ...
"ApplicationFrameHost","4","511","2203597099008","35364864","21979136","30048", ...

Get-Culture cmdlet 使用嵌套属性 TextInfo ListSeparator 并显示当前区域性的默认列表分隔符。 Get-Process cmdlet 获取 Process 对象。 进程对象通过管道发送到 Export-Csv cmdlet。 Export-CSV 将进程对象转换为一系列 CSV 字符串。 Path 参数指定Processes.csv 文件保存在当前目录中。 UseCulture 参数使用当前区域性的默认列表分隔符作为分隔符。 NoTypeInformation 参数从 CSV 输出中删除 #TYPE 信息标头,在 PowerShell 6 中不需要。Get-Content cmdlet 使用 Path 参数显示位于当前目录中的文件。

示例 5:带有类型信息的导出流程

此示例说明如何在 CSV 文件中包含 #TYPE 标头信息。 #TYPE 标头是 PowerShell 6.0 之前版本中的默认标头。

Get-Process | Export-Csv -Path .\Processes.csv -IncludeTypeInformation
Get-Content -Path .\Processes.csv

#TYPE System.Diagnostics.Process
"Name","SI","Handles","VM","WS","PM","NPM","Path","Company","CPU","FileVersion", ...
"ApplicationFrameHost","4","507","2203595001856","35139584","20934656","29504", ...

Get-Process cmdlet 获取 Process 对象。 进程对象通过管道发送到 Export-Csv cmdlet。 Export-CSV 将进程对象转换为一系列 CSV 字符串。 Path 参数指定Processes.csv 文件保存在当前目录中。 IncludeTypeInformation 在 CSV 输出中包含 #TYPE 信息标题。 Get-Content cmdlet 使用 Path 参数显示位于当前目录中的文件。

示例 6:将对象导出并附加到 CSV 文件

此示例描述如何将对象导出到 CSV 文件并使用 Append 参数将对象添加到现有文件。

$AppService = (Get-Service -DisplayName *Application* | Select-Object -Property DisplayName, Status)
$AppService | Export-Csv -Path .\Services.Csv -NoTypeInformation
Get-Content -Path .\Services.Csv
$WinService = (Get-Service -DisplayName *Windows* | Select-Object -Property DisplayName, Status)
$WinService | Export-Csv -Path ./Services.csv -NoTypeInformation -Append
Get-Content -Path .\Services.Csv

"DisplayName","Status"
"Application Layer Gateway Service","Stopped"
"Application Identity","Running"
"Windows Audio Endpoint Builder","Running"
"Windows Audio","Running"
"Windows Event Log","Running"

Get-Service cmdlet 获取服务对象。 DisplayName 参数返回包含应用程序一词的服务。服务对象通过管道发送到 Select-Object cmdlet。 Select-Object 使用 Property 参数来指定 DisplayName Status 属性。 $AppService 变量存储对象。

$AppService 对象通过管道发送到 Export-Csv cmdlet。 Export-CSV 将服务对象转换为一系列 CSV 字符串。 Path 参数指定Services.csv 文件保存在当前目录中。 NoTypeInformation 参数从 CSV 输出中删除 #TYPE 信息标头,在 PowerShell 6 中不需要。Get-Content cmdlet 使用 Path 参数显示位于当前目录中的文件。

Get-ServiceSelect-Object cmdlet 对包含单词 Windows 的服务重复。 $WinService 变量存储服务对象。 Export-Csv cmdlet 使用 Append 参数指定将 $WinService 对象添加到现有 Services.csv 文件中。重复 Get-Content cmdlet 以显示包含附加数据的更新文件。

Example 7: Format cmdlet within a pipeline creates unexpected results

This example shows why it is important not to use a format cmdlet within a pipeline. When unexpected output is received, troubleshoot the pipeline syntax.PowerShell复制

Get-Date | Select-Object -Property DateTime, Day, DayOfWeek, DayOfYear |
 Export-Csv -Path .\DateTime.csv -NoTypeInformation
Get-Content -Path .\DateTime.csv

"DateTime","Day","DayOfWeek","DayOfYear"
"Wednesday, January 2, 2019 14:59:34","2","Wednesday","2"

Get-Date | Format-Table -Property DateTime, Day, DayOfWeek, DayOfYear |
 Export-Csv -Path .\FTDateTime.csv -NoTypeInformation
Get-Content -Path .\FTDateTime.csv

"ClassId2e4f51ef21dd47e99d3c952918aff9cd","pageHeaderEntry","pageFooterEntry","autosizeInfo", ...
"033ecb2bc07a4d43b5ef94ed5a35d280",,,,"Microsoft.PowerShell.Commands.Internal.Format. ...
"9e210fe47d09416682b841769c78b8a3",,,,,
"27c87ef9bbda4f709f6b4002fa4af63c",,,,,
"4ec4f0187cb04f4cb6973460dfe252df",,,,,
"cf522b78d86c486691226b40aa69e95c",,,,,

The Get-Date cmdlet gets the DateTime object. The object is sent down the pipeline to the Select-Object cmdlet. Select-Object uses the Property parameter to select a subset of object properties. The object is sent down the pipeline to the Export-Csv cmdlet. Export-Csv converts the object to a CSV format. The Path parameter specifies that the DateTime.csv file is saved in the current directory. The NoTypeInformation parameter removes the #TYPE information header from the CSV output and is not required in PowerShell 6. The Get-Content cmdlet uses the Path parameter to display the CSV file located in the current directory.

When the Format-Table cmdlet is used within the pipeline to select properties unexpected results are received. Format-Table sends table format objects down the pipeline to the Export-Csv cmdlet rather than the DateTime object. Export-Csv converts the table format objects to a series of CSV strings. The Get-Content cmdlet displays the CSV file which contains the table format objects.

Example 8: Using the Force parameter to overwrite read-only files

This example creates an empty, read-only file and uses the Force parameter to update the file.PowerShell复制

New-Item -Path .\ReadOnly.csv -ItemType File
Set-ItemProperty -Path .\ReadOnly.csv -Name IsReadOnly -Value $true
Get-Process | Export-Csv -Path .\ReadOnly.csv -NoTypeInformation

Export-Csv : Access to the path 'C:\ReadOnly.csv' is denied.
At line:1 char:15
+ Get-Process | Export-Csv -Path .\ReadOnly.csv -NoTypeInformation
+               ~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : OpenError: (:) [Export-Csv], UnauthorizedAccessException
+ FullyQualifiedErrorId : FileOpenFailure,Microsoft.PowerShell.Commands.ExportCsvCommand

Get-Process | Export-Csv -Path .\ReadOnly.csv -NoTypeInformation -Force
Get-Content -Path .\ReadOnly.csv

"Name";"SI";"Handles";"VM";"WS";"PM";"NPM";"Path";"Parent";"Company";"CPU";"FileVersion"; ...
"ApplicationFrameHost";"4";"509";"2203595321344";"34807808";"21770240";"29504"; ...

The New-Item cmdlet uses the Path and ItemType parameters to create the ReadOnly.csv file in the current directory. The Set-ItemProperty cmdlet uses the Name and Value parameters to change the file’s IsReadOnly property to true. The Get-Process cmdlet gets Process objects. The process objects are sent down the pipeline to the Export-Csv cmdlet. Export-Csv converts the process objects to a series of CSV strings. The Path parameter specifies that the ReadOnly.csv file is saved in the current directory. The NoTypeInformation parameter removes the #TYPE information header from the CSV output and is not required in PowerShell 6. The output shows that the file is not written because access is denied.

The Force parameter is added to the Export-Csv cmdlet to force the export to write to the file. The Get-Content cmdlet uses the Path parameter to display the file located in the current directory.

Example 9: Using the Force parameter with Append

This example shows how to use the Force and Append parameters. When these parameters are combined, mismatched object properties can be written to a CSV file.PowerShell复制

$Content = [PSCustomObject]@{Name = 'PowerShell Core'; Version = '6.0'}
$Content | Export-Csv -Path .\ParmFile.csv -NoTypeInformation
$AdditionalContent = [PSCustomObject]@{Name = 'Windows PowerShell'; Edition = 'Desktop'}
$AdditionalContent | Export-Csv -Path .\ParmFile.csv -NoTypeInformation -Append

Export-Csv : Cannot append CSV content to the following file: ParmFile.csv.
The appended object does not have a property that corresponds to the following column:
Version. To continue with mismatched properties, add the -Force parameter, and then retry
 the command.
At line:1 char:22
+ $AdditionalContent | Export-Csv -Path .\ParmFile.csv -NoTypeInformation -Append
+                      ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
+ CategoryInfo          : InvalidData: (Version:String) [Export-Csv], InvalidOperationException
+ FullyQualifiedErrorId : CannotAppendCsvWithMismatchedPropertyNames,Microsoft.PowerShell. ...

$AdditionalContent | Export-Csv -Path .\ParmFile.csv -NoTypeInformation -Append -Force
Import-Csv -Path .\ParmFile.csv

Name               Version
----               -------
PowerShell Core    6.0
Windows PowerShell

An expression creates the PSCustomObject with Name and Version properties. The values are stored in the $Content variable. The $Content variable is sent down the pipeline to the Export-Csv cmdlet. Export-Csv uses the Path parameter and saves the ParmFile.csv file in the current directory. The NoTypeInformation parameter removes the #TYPE information header from the CSV output and is not required in PowerShell 6.

Another expression creates a PSCustomObject with the Name and Edition properties. The values are stored in the $AdditionalContent variable. The $AdditionalContent variable is sent down the pipeline to the Export-Csv cmdlet. The Append parameter is used to add the data to the file. The append fails because there is a property name mismatch between Version and Edition.

The Export-Csv cmdlet Force parameter is used to force the export to write to the file. The Edition property is discarded. The Import-Csv cmdlet uses the Path parameter to display the file located in the current directory.

Example 10: Export to CSV with quotes around two columns

This example converts a DateTime object to a CSV string.PowerShell复制

Get-Date | Export-Csv  -QuoteFields "DateTime","Date" -Path .\FTDateTime.csv
Get-Content -Path .\FTDateTime.csv

DisplayHint,"DateTime","Date",Day,DayOfWeek,DayOfYear,Hour,Kind,Millisecond,Minute,Month,Second,Ticks,TimeOfDay,Year
DateTime,"Thursday, August 22, 2019 11:27:34 AM","8/22/2019 12:00:00 AM",22,Thursday,234,11,Local,569,27,8,34,637020700545699784,11:27:34.5699784,2019

Example 11: Export to CSV with quotes only when needed

This example converts a DateTime object to a CSV string.PowerShell复制

Get-Date | Export-Csv  -UseQuotes AsNeeded -Path .\FTDateTime.csv
Get-Content -Path .\FTDateTime.csv

DisplayHint,DateTime,Date,Day,DayOfWeek,DayOfYear,Hour,Kind,Millisecond,Minute,Month,Second,Ticks,TimeOfDay,Year
DateTime,"Thursday, August 22, 2019 11:31:00 AM",8/22/2019 12:00:00 AM,22,Thursday,234,11,Local,713,31,8,0,637020702607132640,11:31:00.7132640,2019

Parameters

-Append

Use this parameter so that Export-CSV adds CSV output to the end of the specified file. Without this parameter, Export-CSV replaces the file contents without warning.

This parameter was introduced in Windows PowerShell 3.0.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

-Confirm

Prompts you for confirmation before running the cmdlet.

Type:SwitchParameter
Aliases:cf
Position:Named
Default value:False
Accept pipeline input:False
Accept wildcard characters:False

-Delimiter

Specifies a delimiter to separate the property values. The default is a comma (,). Enter a character, such as a colon (:). To specify a semicolon (;), enclose it in quotation marks.

Type:Char
Position:1
Default value:comma (,)
Accept pipeline input:False
Accept wildcard characters:False

-Encoding

Specifies the encoding for the exported CSV file. The default value is utf8NoBOM.

The acceptable values for this parameter are as follows:

  • ascii: Uses the encoding for the ASCII (7-bit) character set.
  • bigendianunicode: Encodes in UTF-16 format using the big-endian byte order.
  • bigendianutf32: Encodes in UTF-32 format using the big-endian byte order.
  • oem: Uses the default encoding for MS-DOS and console programs.
  • unicode: Encodes in UTF-16 format using the little-endian byte order.
  • utf7: Encodes in UTF-7 format.
  • utf8: Encodes in UTF-8 format.
  • utf8BOM: Encodes in UTF-8 format with Byte Order Mark (BOM)
  • utf8NoBOM: Encodes in UTF-8 format without Byte Order Mark (BOM)
  • utf32: Encodes in UTF-32 format.

Beginning with PowerShell 6.2, the Encoding parameter also allows numeric IDs of registered code pages (like -Encoding 1251) or string names of registered code pages (like -Encoding "windows-1251"). For more information, see the .NET documentation for Encoding.CodePage.

 备注

UTF-7* is no longer recommended to use. In PowerShell 7.1, a warning is written if you specify utf7 for the Encoding parameter.

Type:Encoding
Accepted values:ASCII, BigEndianUnicode, BigEndianUTF32, OEM, Unicode, UTF7, UTF8, UTF8BOM, UTF8NoBOM, UTF32
Position:Named
Default value:UTF8NoBOM
Accept pipeline input:False
Accept wildcard characters:False

-Force

This parameter allows Export-Csv to overwrite files with the Read Only attribute.

When Force and Append parameters are combined, objects that contain mismatched properties can be written to a CSV file. Only the properties that match are written to the file. The mismatched properties are discarded.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

-IncludeTypeInformation

When this parameter is used the first line of the CSV output contains #TYPE followed by the fully qualified name of the object type. For example, #TYPE System.Diagnostics.Process.

This parameter was introduced in PowerShell 6.0.

Type:SwitchParameter
Aliases:ITI
Position:Named
Default value:#TYPE <Object>
Accept pipeline input:False
Accept wildcard characters:False

-InputObject

Specifies the objects to export as CSV strings. Enter a variable that contains the objects or type a command or expression that gets the objects. You can also pipe objects to Export-CSV.

Type:PSObject
Position:Named
Default value:None
Accept pipeline input:True
Accept wildcard characters:False

-LiteralPath

Specifies the path to the CSV output file. Unlike Path, the value of the LiteralPath parameter is used exactly as it is typed. No characters are interpreted as wildcards. If the path includes escape characters, use single quotation marks. Single quotation marks tell PowerShell not to interpret any characters as escape sequences.

Type:String
Aliases:PSPath, LP
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

-NoClobber

Use this parameter so that Export-CSV does not overwrite an existing file. By default, if the file exists in the specified path, Export-CSV overwrites the file without warning.

Type:SwitchParameter
Aliases:NoOverwrite
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

-NoTypeInformation

Removes the #TYPE information header from the output. This parameter became the default in PowerShell 6.0 and is included for backwards compatibility.

Type:SwitchParameter
Aliases:NTI
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

-Path

A required parameter that specifies the location to save the CSV output file.

Type:String
Position:0
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

-QuoteFields

Specifies the names of the columns that should be quoted. When this parameter is used, only the specified columns are quoted. This parameter was added in PowerShell 7.0.

Type:String[]
Aliases:QF
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

-UseCulture

Uses the list separator for the current culture as the item delimiter. To find the list separator for a culture, use the following command: (Get-Culture).TextInfo.ListSeparator.

Type:SwitchParameter
Position:Named
Default value:None
Accept pipeline input:False
Accept wildcard characters:False

-UseQuotes

Specifies when quotes are used in the CSV files. Possible values are:

  • Never – don’t quote anything
  • Always – quote everything (default behavior)
  • AsNeeded – only quote fields that contain a delimiter character

This parameter was added in PowerShell 7.0.

Type:Microsoft.PowerShell.Commands.BaseCsvWritingCommand+QuoteKind
Aliases:UQ
Position:Named
Default value:Always
Accept pipeline input:False
Accept wildcard characters:False

-WhatIf

Prevents the cmdlet from being processed or making changes. The output shows what would happen if the cmdlet were run.

Type:SwitchParameter
Aliases:wi
Position:Named
Default value:False
Accept pipeline input:False
Accept wildcard characters:False

Inputs

PSObject

You can pipe any object with an Extended Type System (ETS) adapter to Export-CSV.

Outputs

String

The CSV list is sent to the file designated in the Path parameter.

Notes

The Export-CSV cmdlet converts the objects that you submit into a series of CSV strings and saves them in the specified text file. You can use Export-CSV -IncludeTypeInformation to save objects in a CSV file and then use the Import-Csv cmdlet to create objects from the text in the CSV file.

In the CSV file, each object is represented by a comma-separated list of the property values of the object. The property values are converted to strings using the ToString() method. The strings are represented by the property value name. Export-CSV -IncludeTypeInformation does not export the methods of the object.

The CSV strings are output as follows:

  • If IncludeTypeInformation is used, the first string contains the #TYPE information header followed by the object type’s fully qualified name. For example, #TYPE System.Diagnostics.Process.
  • If IncludeTypeInformation is not used the first string includes the column headers. The headers contain the first object’s property names as a comma-separated list.
  • The remaining strings contain comma-separated lists of each object’s property values.

Beginning with PowerShell 6.0 the default behavior of Export-CSV is to not include the #TYPE information in the CSV and NoTypeInformation is implied. IncludeTypeInformation can be used to include the #TYPE Information and emulate the default behavior of Export-CSV prior to PowerShell 6.0.

When you submit multiple objects to Export-CSVExport-CSV organizes the file based on the properties of the first object that you submit. If the remaining objects do not have one of the specified properties, the property value of that object is null, as represented by two consecutive commas. If the remaining objects have additional properties, those property values are not included in the file.

You can use the Import-Csv cmdlet to recreate objects from the CSV strings in the files. The resulting objects are CSV versions of the original objects that consist of string representations of the property values and no methods.

The ConvertTo-Csv and ConvertFrom-Csv cmdlets convert objects to CSV strings and from CSV strings. Export-CSV is the same as ConvertTo-CSV, except that it saves the CSV strings in a file.

Scroll to Top