Powershell выгрузка в excel
Microsoft Excel is one of those ubiquitous tools most of us can’t escape even if we tried. Many IT professionals use Excel as a little database storing tons of data in various automation routines. What’s the best scenario for automation and Excel? PowerShell and Excel!
Excel spreadsheets have always been notoriously hard to script and automate. Unlike its less-featured (and simpler) CSV file counterpart, Excel workbooks aren’t just simple text files. Excel workbooks required PowerShell to manipulate complicated Component Object Model (COM) objects thus you had to have Excel installed. Not anymore.
Thankfully, an astute PowerShell community member, Doug Finke, created a PowerShell module called ImportExcel for us mere mortals. The ImportExcel module abstracts away all of that complexity. It makes it possible to easily manage Excel workbooks and get down to PowerShell scripting!
In this article, let’s explore what you can do with PowerShell and Excel using the ImportExcel module and a few popular use cases.
Table of Contents
Prerequisites
When running the ImportExcel module on a Windows system, no separate dependencies are necessary. However, if you’re working on macOS, you will need to install the mono-libgdiplus package using brew install mono-libgdiplus . All examples in this article will be built using macOS but all examples should work cross-platform.
If you’re using macOS, be sure to restart your PowerShell session before continuing.
Installing the ImportExcel Module
Start by downloading and installing the module via the PowerShell Gallery by running Install-Module ImportExcel -Scope CurrentUser . After a few moments, you’ll be good to go.
Using PowerShell and Excel to Export to a Worksheet
You may be familiar with the standard PowerShell cmdlets Export-Csv and Import-Csv . These cmdlets allow you to read and export PowerShell objects to CSV files. Unfortunately, there’s no Export-Excel and Import-Excel cmdlets. But using the ImportExcel module, you can build your own functionality.
One of the most common requests a sysadmin has is exporting PowerShell objects to an Excel worksheet. Using the Export-Excel cmdlet in the ImportExcel module, you can easily make it happen.
For example, perhaps you need to find some processes running on your local computer and get them into an Excel workbook.
The Export-Excel cmdlet accepts any object exactly the way Export-Csv does. You can pipe any kind of object to this cmdlet.
To find processes running on a system with PowerShell, use the Get-Process cmdlet which returns each running process and various information about each process. To export that information to Excel, use the Export-Excel cmdlet providing the file path to the Excel workbook that will be created. You can see an example of the command and screenshot of the Excel file generated below.
Worksheet created with PowerShell and Excel
Congrats! You’ve now exported all the information just like Export-Csv but, unlike Export-Csv , we can make this data a lot fancier. Let’s make sure the worksheet name is called Processes, the data is in a table and rows are auto-sized.
By using the AutoSize switch parameter to autosize all rows, TableName to specify the name of the table that will include all the data and the WorksheetName parameter name of Processes, you can see in the screenshot below what can be built.
Autosize Switch Parameter Result
The Export-Excel cmdlet has a ton of parameters you can use to create Excel workbooks of all kinds. For a full rundown on everything Export-Excel can do, run Get-Help Export-Excel .
Using PowerShell to Import to Excel
So you’ve exported some information to a file called processes.xlsx in the previous section. Perhaps now you need to move this file to another computer and import/read this information with PowerShell and Excel. No problem. You have Import-Excel at your disposal.
At its most basic usage, you only need to provide the path to the Excel document/workbook using the Path parameter as shown below. You’ll see that it reads the first worksheet, in this case, the Processes worksheet, and returns PowerShell objects.
Path Parameter
Maybe you have multiple worksheets in an Excel workbook? You can read a particular worksheet using the WorksheetName parameter.
Do you need to only read certain columns from the Excel worksheet? Use the HeaderName parameter to specify only those parameters you’d like to read.
The Import-Excel cmdlet has other parameters you can use to read Excel workbooks of all kinds. For a full rundown on everything Import-Excel can do, run Get-Help Import-Excel .
Using PowerShell to Get (and Set) Excel Cell Values
You now know how to read an entire worksheet with PowerShell and Excel but what if you only need a single cell value? You technically could use Import-Excel and filter out the value you need with Where-Object but that wouldn’t be too efficient.
Instead, using the Open-ExcelPackage cmdlet, you can “convert” an Excel workbook into a PowerShell object which can then be read and manipulated. To find a cell value, first, open up the Excel workbook to bring it into memory.
The Open-ExcelPackage is similar to using New-Object -comobject excel.application if working directly with COM objects.
Next, pick the worksheet inside of the workbook.
This process is similar to the COM object way of opening workbooks with excel.workbooks.open .
Once you have the worksheet assigned to a variable, you can now drill down to individual rows, columns, and cells. Perhaps you need to find all cell values in the A1 row. You simply need to reference the Cells property providing an index of A1 as shown below.
You can also change the value of cells in a worksheet by assigning a different value eg. $worksheet.Cells['A1'] = 'differentvalue'
Once in memory, it’s important to release the Excel package using the Close-ExcelPackage cmdlet.
Converting Worksheets to CSV Files with PowerShell and Excel
Once you have the contents of an Excel worksheet represented via PowerShell objects, “converting” Excel worksheets to CSV simply requires sending those objects to the Export-Csv cmdlet.
Using the processes.xlsx workbook created earlier, read the first worksheet which gets all of the data into PowerShell objects, and then export those objects to CSV using the command below.
If you now open up the resulting CSV file, you’ll see the same data inside of the Processes worksheet (in this example).
Converting Multiple Worksheets
If you have an Excel workbook with multiple worksheets, you can also create a CSV file for each worksheet. To do so, you can find all the sheets in a workbook using the Get-ExcelSheetInfo cmdlet. Once you have the worksheet names, you can then pass those names to the WorksheetName parameter and also use the sheet name as the name of the CSV file.
Below you can the example code needed using PowerShell and Excel.
Conclusion
Using PowerShell and Excel, you can import, export, and manage data in Excel workbooks exactly like you would CSVs without having to install Excel!
In this article, you learned the basics of reading and writing data in an Excel workbook but this just scratches the surface. Using PowerShell and the ImportExcel module, you can create charts, pivot tables, and leverage other powerful features of Excel!
Hate ads? Want to support the writer? Get many of our tutorials packaged as an ATA Guidebook.
More from ATA Learning & Partners
Recommended Resources!
Recommended Resources for Training, Information Security, Automation, and more!
Get Paid to Write!
ATA Learning is always seeking instructors of all experience levels. Regardless if you’re a junior admin or system architect, you have something to share. Why not write on a platform with an existing audience and share your knowledge with the world?
ATA Learning Guidebooks
ATA Learning is known for its high-quality written tutorials in the form of blog posts. Support ATA Learning with ATA Guidebook PDF eBooks available offline and with no ads!
31.08.2020
itpro
Active Directory, Office, PowerShell
комментариев 10
В это статье мы покажем, как получить доступ к данным в файлах Excel напрямую из PowerShell. Возможности прямого обращения к данным Excel из PowerShell открывает широкие возможности по инвентаризации и построению различных отчетов по компьютерам, серверам, инфраструктуре, Active Directory и т.д.
Обращение к Excel из PowerShell выполняется через отдельный Component Object Model (COM) объект. Это требует наличие установленного Excel на компьютере.
Прежде, чем показать, как обратиться к данным в ячейке файла Excel, необходимо рассмотреть архитектуру уровней представления в документе Excel. На следующем рисунке показаны 4 вложенных уровня в объектной модели Excel:
- Уровень приложения (Application Layer) – запущенное приложение Excel;
- Уровень книги (WorkBook Layer) – одновременно могут быть открыты несколько книг (документов Excel);
- Уровень листа (WorkSheet Layer) – в каждом xlsx файле может быть несколько листов;
- Ячейки (Range Layer) – здесь можно получить доступ к данным в конкретной ячейке или диапазонe ячеек.
Доступ к данным в Excel из консоли PowerShell
Рассмотрим на простом примере как получить доступ из PowerShell к данным в Excel файле со списком сотрудников.
Сначала нужно запустить на компьютере приложение Excel (application layer) через COM объект:
После выполнения этой команды на компьютере запускается в фоновом режиме приложение Excel. Чтобы сделать окно Excel видимым, нужно изменить свойство Visible COM объекта:
Теперь можно открыть файл (книгу, workbook) Excel:
В каждом файле Excel может быть несколько листов (worksheets). Выведем список листов в текущей книге Excel:
$ExcelWorkBook.Sheets| fl Name, index
Теперь можно открыть конкретный лист (по имени или по индексу):
Текущий (активный) лист Excel можно узнать командой:
$ExcelWorkBook.ActiveSheet | fl Name, Index
Теперь вы можете получить значения из ячеек документа Excel. Можно использовать различные способы адресации ячеек в книге Excel: через диапазон (Range), ячейку (Cell), столбец (Columns) или строку(Rows). Ниже я привел разные примеры получения данных из одной и той же ячейки:
$ExcelWorkSheet.Range("B2").Text
$ExcelWorkSheet.Range("B2:B2").Text
$ExcelWorkSheet.Range("B2","B2").Text
$ExcelWorkSheet.cells.Item(2, 2).text
$ExcelWorkSheet.cells.Item(2, 2).value2
$ExcelWorkSheet.Columns.Item(2).Rows.Item(2).Text
$ExcelWorkSheet.Rows.Item(2).Columns.Item(2).Text
Как получить данные из Active Directory и сохранить их в книге Excel?
Рассмотрим практический пример использования доступа к данным Excel из PowerShell. Например, нам нужно для каждого пользователя в Excel файле получить информацию из Active Directory. Например, его телефон (атрибут telephoneNumber), отдел (department) и email адрес (mail).
Для получения информации об атрибутах пользователя в AD мы будем использовать командлет Get-ADUser из модуля AD PowerShell.
В результате в Excel файле для каждого пользователя были добавлены столбцы с информацией из AD.
На сайте есть статья, в которой показывается как получить данные из AD из vba макроса Excel. На мой взгляд метод с обращением к AD из Excel намного проще и гибче.
Рассмотрим еще один пример построения отчета с помощью PowerShell и Excel. Допустим, вам нужно построить Excel отчет о состоянии службы Print Spooler на всех серверах домена.
Для получения списка серверов в AD используется командлет Get-ADComputer, а для удаленной проверки статуса службы на серверах командлет WinRM Invoke-Command.
Область применения возможностей доступа из PowerShell в Excel очень широка. Начиная от простого построения отчетов, например, из Active Directory, и заканчивая возможностью создания PowerShell скриптов для актуализации данных в AD из Excel.
Например, вы можете поручить сотруднику отдела кадров вести реестр пользователей в Excel. Затем с помощью PowerShell скрипта через Set-ADUser сотрудник может автоматически обновлять данные пользователей в AD (достаточно делегировать пользователю права на изменение этих атрибутов пользователей AD и показать как запускать PS скрипт). Таким образом можно вести актуальную адресную книгу с актуальными номерами телефонами и должностями.
Converts objects into a series of comma-separated value (CSV) strings and saves the strings to a file.
Syntax
Description
The Export-CSV cmdlet creates a CSV file of the objects that you submit. Each object is a row that includes a comma-separated list of the object's property values. You can use the Export-CSV cmdlet to create spreadsheets and share data with programs that accept CSV files as input.
Do not format objects before sending them to the Export-CSV cmdlet. If Export-CSV receives formatted objects the CSV file contains the format properties rather than the object properties. To export only selected properties of an object, use the Select-Object cmdlet.
Examples
Example 1: Export process properties to a CSV file
This example selects Process objects with specific properties, exports the objects to a CSV file.
Example 2: Export processes to a comma-delimited file
This example gets Process objects and exports the objects to a CSV file.
Example 3: Export processes to a semicolon delimited file
This example gets Process objects and exports the objects to a file with a semicolon delimiter.
Example 4: Export processes using the current culture's list separator
This example gets Process objects and exports the objects to a file. The delimiter is the current culture's list separator.
Example 5: Export processes with type information
Example 6: Export and append objects to a CSV file
This example describes how to export objects to a CSV file and use the Append parameter to add objects to an existing file.
The Get-Service cmdlet gets service objects. The DisplayName parameter returns services that contain the word Application. The service objects are sent down the pipeline to the Select-Object cmdlet. Select-Object uses the Property parameter to specify the DisplayName and Status properties. The $AppService variable stores the objects.
The Get-Service and Select-Object cmdlets are repeated for services that contain the word Windows. The $WinService variable stores the service objects. The Export-Csv cmdlet uses the Append parameter to specify that the $WinService objects are added to the existing Services.csv file. The Get-Content cmdlet is repeated to display the updated file that includes the appended data.
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.
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.
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.
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.
Example 11: Export to CSV with quotes only when needed
This example converts a DateTime object to a CSV string.
Example 12: Convert hashtables to CSV
In PowerShell 7.2 and above, when you export hashtables to CSV, the keys of the first hashtable are serialized and used as headers in the csv file output.
Example 13: Converting hashtables to CSV with additional properties
In PowerShell 7.2 and above, when you export a hashtable that has additional properties added with Add-Member or Select-Object the additional properties are also added as a header in the CSV file.
Each hashtable has a property named ExtraProp added by Add-Member and then exported to CSV. You can see ExtraProp is now a header in the CSV file output.
If an added property has the same name as a key from the hashtable, the key takes precedence and only the key is exported to CSV.
Parameters
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 |
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 |
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 |
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.
UTF-7* is no longer recommended to use. As of 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 |
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 |
This parameter was introduced in PowerShell 6.0.
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 |
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 |
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 |
Type: | SwitchParameter |
Aliases: | NTI |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
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 |
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 |
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 |
- Never - don't quote anything
- Always - quote everything (default behavior)
- AsNeeded - only quote fields that contain a delimiter character, double-quote, or newline character
This parameter was added in PowerShell 7.0.
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
You can pipe any object with an Extended Type System (ETS) adapter to Export-CSV .
Outputs
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:
When you submit multiple objects to Export-CSV , Export-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.
Converts objects into a series of comma-separated value (CSV) strings and saves the strings to a file.
Syntax
Description
The Export-CSV cmdlet creates a CSV file of the objects that you submit. Each object is a row that includes a comma-separated list of the object's property values. You can use the Export-CSV cmdlet to create spreadsheets and share data with programs that accept CSV files as input.
Do not format objects before sending them to the Export-CSV cmdlet. If Export-CSV receives formatted objects the CSV file contains the format properties rather than the object properties. To export only selected properties of an object, use the Select-Object cmdlet.
Examples
Example 1: Export process properties to a CSV file
This example selects Process objects with specific properties, exports the objects to a CSV file.
Example 2: Export processes to a comma-delimited file
This example gets Process objects and exports the objects to a CSV file.
Example 3: Export processes to a semicolon delimited file
This example gets Process objects and exports the objects to a file with a semicolon delimiter.
Example 4: Export processes using the current culture's list separator
This example gets Process objects and exports the objects to a file. The delimiter is the current culture's list separator.
Example 5: Export processes with type information
Example 6: Export and append objects to a CSV file
This example describes how to export objects to a CSV file and use the Append parameter to add objects to an existing file.
The Get-Service cmdlet gets service objects. The DisplayName parameter returns services that contain the word Application. The service objects are sent down the pipeline to the Select-Object cmdlet. Select-Object uses the Property parameter to specify the DisplayName and Status properties. The $AppService variable stores the objects.
The Get-Service and Select-Object cmdlets are repeated for services that contain the word Windows. The $WinService variable stores the service objects. The Export-Csv cmdlet uses the Append parameter to specify that the $WinService objects are added to the existing Services.csv file. The Get-Content cmdlet is repeated to display the updated file that includes the appended data.
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.
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.
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.
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.
Example 11: Export to CSV with quotes only when needed
This example converts a DateTime object to a CSV string.
Parameters
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 |
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 |
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 |
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.
UTF-7* is no longer recommended to use. As of 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 |
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 |
This parameter was introduced in PowerShell 6.0.
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 |
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 |
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 |
Type: | SwitchParameter |
Aliases: | NTI |
Position: | Named |
Default value: | None |
Accept pipeline input: | False |
Accept wildcard characters: | False |
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 |
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 |
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 |
- 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.
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
You can pipe any object with an Extended Type System (ETS) adapter to Export-CSV .
Outputs
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:
When you submit multiple objects to Export-CSV , Export-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.
Summary: Microsoft PowerShell MVP, Tobias Weltner, talks about Windows PowerShell and Excel interaction. Microsoft Scripting Guy, Ed Wilson, is here. Let’s welcome back guest blogger, Tobias Weltner… Microsoft Excel is a great tool to share Windows PowerShell data. However, the scripting tactics have changed from the good old VBScript times. Let’s take a look at how Windows PowerShell can output data to Excel fast, safe, and reliably.
Out-GridView: Built-in “mini-Excel”
Before I touch Excel, let’s first look at its little cousin, which ships with Windows PowerShell: Out-GridView. It’s trivial to use: simply pipe anything to Out-GridView, and it will happily display the content in a mini-spreadsheet:
Get-Process | Where-Object < $_.MainWindowTitle >| Out-GridView You can even use Out-GridView as a generic selection dialog box. Add the parameter –passThru (added in PowerShell 3.0). This parameter adds two new buttons to the lower right area of the grid view, and when you select something, it will be returned to you. Curious? This piece of script pipes all top-level applications to the grid view, and when you select one and click OK, it will be immediately deleted:
Out-GridView -Title ‘Select Program To Kill’ -PassThru |
Stop-Process That’s admittedly a bit rude (and dangerous, too, because you will lose all unsaved data). So here is a gentleman’s version that will actually ask if there is unsaved data. It also allows the program to clean up its resources before it closes.
Out-GridView -Title ‘Select Program To Kill’ -PassThru |
Excel: Out-GridView on steroids
So what about Excel? Why use it at all? Well, there are things that Out-GridView cannot do. You cannot save the data, and you cannot hand it over to someone else. And of course, you cannot use the many sophisticated formatting options that are found in Microsoft Excel spreadsheets. Out-GridView can show a maximum of 30 columns. So if an object has more than 30 properties, Out-GridView silently suppresses the rest. I’d call this a bug, but maybe it’s a feature, too. Anyhow, here is a script to prove the point:
ForEach-Object < $hash = [Ordered]@<>> < New-Object PSObject -Property $hash>| Out-GridView This creates an object with 100 properties. Out-GridView only displays the first 30. Time for Excel…the real thing. Wouldn’t it be nice to be able to do this:
Get-Process | Where-Object < $_.MainWindowTitle >| Out-Excel Or, for that matter, this (and finally get all object properties and not just the first 30):
Out-Excel Well, you can. Out-Excel actually is a ridiculously simple Windows PowerShell function:
param($Path = “$env:temp$(Get-Date -Format yyyyMMddHHmmss).csv”)
$input | Export-CSV -Path $Path -UseCulture -Encoding UTF8 -NoTypeInformation
Invoke-Item -Path $Path
Get-Process | Where-Object < $_.MainWindowTitle >| Out-Excel You get a report that includes all the technical data from all of your running main applications. With all of the object properties—nothing is left out. Basically, you can pipe anything into Out-Excel. It works pretty much like Out-GridView (except that you cannot turn Excel into a selection dialog, of course). $input is an automatic Windows PowerShell variable. It delivers all data that is piped from the upstream cmdlet, and this data is then written to a CSV file and opened with Excel. Because Excel always shows all properties, you may want to use Select-Object and select only the properties you really care about:
Select-Object -Property Name, Company, Description, CPU |
Writing directly to Excel
$excel = New-Object -ComObject Excel.Application
> Here is the fast and clean way that is used by Windows PowerShell to create the same report:
Select-Object -Property Name, DisplayName, Status |
Export-Csv -Path $env:tempreport.csv -Encoding UTF8 -UseCulture -NoTypeInformation
Invoke-Item -Path $env:tempreport.csv Actually, Windows PowerShell is not even touching the internals of Excel; but instead, it is producing CSV files that can then be opened by Excel.
Formatting data with colors
Unfortunately, importing CSV data is a black and white world. You cannot use color in cells or format the spreadsheet otherwise. Well actually, you can. This will create the very same report of services. This time, running services will show in green and stopped services in red.
if ($_.Status -eq ‘Running’)
ForEach-Object -Begin $begin -Process $process -End $end |
Set-Content -Path $Path -Encoding UTF8
Start-Process -FilePath ‘C:Program Files*Microsoft OfficeOffice*EXCEL.EXE’ -ArgumentList $Path The trick is to create an HTML report that gives you all the formatting opportunities you need. To create a Excel spreadsheet with color, the HTML file needs to be fed directly into excel.exe. Note how Windows PowerShell automatically identifies the Excel version you use. Start-Process accepts a file path that can contain wildcard characters. By replacing version numbers in the path, Windows PowerShell can launch any Excel version that happens to be installed. If you wanted to know that path, too, then simply ask Resolve-Path:
Resolve-Path -Path ‘C:Program Files*Microsoft OfficeOffice*EXCEL.EXE’ |
Select-Object -ExpandProperty Path -First 1
Turning arrays into string
Take a look at the Excel spreadsheet that uses color again. Look at the columns RequiredServices and DependentServices. They are just fine—which might surprise you. Because typically, when Windows PowerShell exports arrays of information, they do not display well in Excel. Check this out, for example… The next piece of script outputs the 10 newest error events from your System event log to Excel. When you look at the data, the columns Data and ReplacementStrings contain unreadable stuff. To make array content display correctly in Excel, arrays need to be converted to strings. Here’s sample script that illustrates how easily that can be done:
$Path = “$env:temp$(Get-Date -Format yyyyMMddHHmmss)report.csv”
Get-EventLog -LogName System -EntryType Error -Newest 10 |
$_.ReplacementStrings = $_.ReplacementStrings -join ‘,’
$_.Data = $_.Data -join ‘,’
Export-CSV -Path $Path -UseCulture -Encoding UTF8 -NoTypeInformation
Читайте также: