Как правильно передать параметр для экспорта в csv

Quick Links

Key Takeaways

• To save a PowerShell command’s output to a TXT file, type the command, press Spacebar, type the > (greater than) symbol, press Spacebar, and type the full path to the TXT file.
• To generate a CSV file from a PowerShell command, type your command, press Spacebar, type the | (pipe) sign, press Spacebar, type “Export-CSV”, press Spacebar, enter the full path to the CSV file, press Spacebar, type “-NoTypeInformation”, and press Enter.

Do you want to save your PowerShell command’s result in a TXT (text) or CSV (comma-separated values) file on your computer? If so, it’s easy to do, and we’ll show you how on your Windows 11 or Windows 10 PC.

Скрипт берет список групп из C:\dst\findenem.csv, находит каждого члена группы и переписывает в C:\dst\cll.csv. Но переписывает только следущее:

“SamAccountName”,”Name”
“Одна из групп”,”Одна из групп”

clear
Import-Module ActiveDirectory
$list = Import-CSV -path "C:\dst\findenem.csv"
foreach ($item in $list)
{
$item.Groups
echo "------------------------"
Get-ADGroupMember -Identity $item.Groups | Select-Object SamAccountName, Name | Export-CSV "C:\dst\cll.CSV" -NoTypeInformation -Encoding UTF8
Get-ADGroupMember -Identity $item.Groups | Select-Object SamAccountName, Name
Wait-Event -Timeout 1
echo "##################################################################################################################"
}

Если прописать название группы вручную, то запись в csv будет корреткной.

Get-ADGroupMember -Identity GroupeName | Select-Object SamAccountName, Name | Export-CSV "C:\dst\cll.CSV" -NoTypeInformation -Encoding UTF8

Подскажите пожалуйста, как правильно передавать записи из массива, чтобы они могли правильно экспортироваться в новую таблицу?

Но, если я попытаюсь вывести этот параметр, то в консоли будет пусто.

Get-ADUser -identity Username | Select-Object Description

Как я могу вывести этот параметр?

When working with PowerShell, you may often find yourself in situations where you need to export data to a CSV file for reporting, analysis, or data interchange purposes. CSV, which stands for Comma Separated Values, is a simple file format widely supported and easily read by humans and machines. In this PowerShell tutorial, we’ll explore how to export an array to a CSV file using PowerShell with a few real examples.

Understanding Arrays and CSV in PowerShell

Before diving into the export process, let’s establish a basic understanding of arrays and CSV files in the context of PowerShell.

An array is a data structure that holds a collection of items. These items can be of any data type, and you can access them by their index in the array. In PowerShell, you create an array by assigning multiple values to a variable, separated by commas.

A CSV file is a plain text file where each line represents a data record. Each record consists of one or more fields, separated by commas. The first line often contains headers, which are the names of the fields.

Exporting a PowerShell Array to CSV Using Export-Csv

The primary cmdlet used for exporting data to a CSV file in PowerShell is Export-Csv. This cmdlet takes an array of objects and creates a CSV file where each object becomes a row, and the object properties become the columns.

Basic Example

Let’s start with a simple example of exporting an array of PSObjects to a CSV file:

# Create an array of PSObjects
$personArray = @( [PSCustomObject]@{Name='John Doe'; Age=30; City='New York'}, [PSCustomObject]@{Name='Jane Smith'; Age=25; City='Los Angeles'}, [PSCustomObject]@{Name='Michael Johnson'; Age=35; City='Chicago'}
)
# Export the array to a CSV file
$personArray | Export-Csv -Path 'C:\temp\people.csv' -NoTypeInformation

In this example, we create an array $personArray that contains three custom objects, each representing a person with Name, Age, and City properties. We then pipe this array to the Export-Csv cmdlet, specifying the path to the CSV file. The -NoTypeInformation parameter is used to exclude the type information from the CSV file.

I have executed the script using Visual Studio code in the screenshot below for the output.

Export PowerShell Simple Arrays to CSV Format

If you have a simple array, not an array of objects in PowerShell, you must convert it to a format that Export-Csv can use. Here’s how you can do that:

# Create a simple array
$simpleArray = @('Apple', 'Banana', 'Cherry')
# Convert the simple array to an array of objects
$objectArray = $simpleArray | ForEach-Object { [PSCustomObject]@{Fruit=$_}
}
# Export the array of objects to a CSV file
$objectArray | Export-Csv -Path 'C:\temp\fruits.csv' -NoTypeInformation

The above script, $simpleArray contains a list of strings. We use ForEach-Object to convert each string into a custom object with a single property. Then, we export the resulting array of objects to a CSV file.

Advanced Usage of Export-Csv in PowerShell

Let us check out a few advanced scenarios to export an array to CSV in PowerShell using Export-csv.

1. Appending to an Existing CSV File

If you want to add more data to an existing CSV file, you can use the -Append parameter:

# Additional data to append
$newData = @( [PSCustomObject]@{Name='Anna Brown'; Age=28; City='Miami'}, [PSCustomObject]@{Name='Greg White'; Age=40; City='Seattle'}
)
# Append the new data to the existing CSV file
$newData | Export-Csv -Path 'C:\temp\people.csv' -NoTypeInformation -Append

This script will add $newData to the people.csv file without overwriting the existing content.

:/>  Как проверить порты 80 и 443 являются открытыми windows 10

2. Specifying a Delimiter

Sometimes, you may need to use a different delimiter instead of a comma. For instance, to use a semicolon, you can use the -Delimiter parameter:

# Export using a semicolon delimiter
$personArray | Export-Csv -Path 'C:\temp\people_semicolon.csv' -Delimiter ';' -NoTypeInformation

Conclusion

Once you understand how the cmdlet works, exporting an array to a CSV file in PowerShell is a straightforward process. Whether you’re dealing with simple arrays or arrays of custom objects, PowerShell provides the flexibility to shape and export your data as needed. Remember to use the -NoTypeInformation parameter to keep your CSV files clean and the -Append parameter to add data to existing files without overwriting them.

In this PowerShell tutorial, I have explained how to export an array to CSV in PowerShell.

You may also like:

Exporting Objects to CSV file in PowerShell

Another approach to exporting data to a CSV file is by creating custom objects and populating them with the desired data. This method gives you more flexibility in structuring the exported data and allows you to include additional information or perform calculations before exporting.

$users = Get-AzureADUser | Select DisplayName, userprincipalname, Mail
$exportData = foreach ($user in $users) { [PSCustomObject]@{ Name = $user.DisplayName Username = $user.userprincipalname Mail = $user.Mail }
}
$exportData
# convert objects to csv
$exportData | Export-CSV "C:\Scripts\Users.csv" -NoTypeInformation

Here is an example of how to export data to a CSV file in PowerShell using the Export-Csv cmdlet:

$data = @( [PSCustomObject]@{ Name = 'John' Age = 30 City = 'New York' }, [PSCustomObject]@{ Name = 'Jane' Age = 25 City = 'Chicago' }
)
$data | Export-Csv -Path 'C:\Data\export.csv' -NoTypeInformation

Name,Age,City
John,30,New York
Jane,25,Chicago

Note that the -NoTypeInformation parameter is used to prevent PowerShell from adding the type information header from the output to the first line of the CSV file. If you are looking to export simple arrays to CSV, refer: How to Export an Array to CSV in PowerShell?

Exporting Data Arrays to CSV File using PowerShell

In addition to exporting individual objects, the Export-CSV cmdlet also enables you to export data arrays directly to CSV files. This can be useful when you have a collection of objects or data that you want to save in a tabular format.

To export a data array to a CSV file, you can simply pass the array to the Export-CSV cmdlet, specify the path to the CSV output, and it will convert the array into a CSV string and save it to the specified file. Here’s an example:

$users = Get-ADUser -Filter * -Properties Name, Email, Title
$users | Export-CSV -Path "C:\PS\users.csv" -NoTypeInformation

View Your Text or CSV File’s Contents in PowerShell

You can view your newly created text or CSV file’s contents right inside PowerShell. You don’t have to leave the tool and use another app to open your files.

Type PATH

Type C:\Users\mahes\Desktop\SystemInfo.txt

Instantly, PowerShell will load your file’s contents in your open window, allowing you to read the file content.

And that’s all there is to know about saving your PowerShell command results in text or CSV files. Enjoy!

Exporting data using Out-File

$CSVPath = "$env:temp\report-$(Get-Date -Format yyyyMMddHHmmss).csv"
Get-Process | ConvertTo-Csv -NoTypeInformation | Out-File $CSVPath

This command exports the data from the Get-Process cmdlet to the CSV output file “ProcessData.csv” in the current directory. The ConvertTo-Csv cmdlet is used to convert the data to a CSV format before it is exported. The -NoTypeInformation parameter is used to remove the type information from the exported data, which can make the resulting CSV file easier to read.

Exporting data with different delimiters

By default, the Export-CSV cmdlet uses commas as the delimiter (Depends on the current culture! You can get it with: (Get-Culture).TextInfo.ListSeparator) ). However, you can use a different delimiter if required. To use a different delimiter, you can use the -Delimiter parameter. For example, let’s say you have a list of employees in your system, and you want to export their names and email addresses to a CSV file with semicolons as the delimiter.

Get-ADUser -Filter * -Properties Name, EmailAddress | Select-Object Name, EmailAddress | Export-CSV -Path "C:\Employees.csv" -NoTypeInformation -Delimiter ";"

Get-Process | Export-CSV -Path "C:\Scripts\processes.csv" -NoTypeInformation -Delimiter "`t"

In this example, the -Delimiter ‘`t‘ parameter instructs PowerShell to use “tab” as the delimiter instead of the default comma.

Exporting data with different encodings

Get-Process | Export-CSV -Path "C:\PS\processes.csv" -NoTypeInformation -Encoding UTF16

In this example, the -Encoding UTF16 parameter instructs PowerShell to use UTF-16 encoding for the exported file.

Understanding the Export-CSV cmdlet

Some key things to know about Export-Csv:

• It takes pipeline input, so you can pipe any PowerShell objects into it
• By default, object properties become CSV columns and rows stored in a plain text file
• You need to specify an output file path to export the CSV data
• Using the -NoTypeInformation parameter removes unnecessary type info from the CSV
• CSV files can be opened in Excel and other spreadsheet programs
• Use the Select-Object cmdlet to choose specific properties or columns before exporting to a CSV file.
• Use the Append parameter with Export-CSV to add data to an existing CSV file.

In the rest of this guide, we will cover common examples of exporting data to CSV with Windows PowerShell.

Syntax of Export-CSV cmdlet

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

The Export-CSV cmdlet offers several parameters that allow you to customize the output and enhance your exporting capabilities. Let’s take a closer look at some of the most commonly used parameters:

These are just a few of the parameters available with the Export-CSV cmdlet. Exploring and understanding these parameters can help you tailor the output to your specific requirements and improve your efficiency when working with CSV files.

Exporting data using PowerShell Export-CSV

Get-Process | Export-Csv -Path C:\Scripts\ProcessData.csv -NoTypeInformation

In the above PowerShell Export CSV script, we export the data from the Get-Process cmdlet to the “ProcessData.csv” file in the specified directory. The -Path parameter specifies the location of the CSV file, and the -NoTypeInformation parameter is used to remove the type information from the exported data, which can make the resulting CSV file easier to read.

Type Information Header

By default, the Export-CSV cmdlet includes a “#TYPE” line in the CSV output, which specifies the type of objects being exported. This line can be useful when importing the CSV file back into PowerShell, as it helps PowerShell recognize the type of each object. However, if you do not need this type information in your CSV file, you can use the -NoTypeInformation parameter to exclude it.

Get-EventLog -LogName Application -Newest 10 | Export-CSV "C:\PS\eventlog.csv" -NoTypeInformation

In this example, we export the ten most recent entries from the Application event log to a CSV file and exclude the type information from the output.

Wrapping up

What does Export-Csv do in PowerShell?

The Export-Csv cmdlet in PowerShell converts objects into a series of comma-separated value (CSV) strings and saves them to a CSV file. This cmdlet is useful for exporting data to a CSV file for later use or for importing into another program.

How do I create and append a CSV file in PowerShell?

To create and append a CSV file in PowerShell, you can use the Export-Csv cmdlet. First, you need to create an object with the desired data and then use the Export-Csv cmdlet to export it to a CSV file. If you want to append data to an existing CSV file, you can use the Append parameter to append it to the existing file.

How do I Export output to CSV in PowerShell script?

How to convert CSV to XLSX in PowerShell?

How do I get the contents of a CSV file in PowerShell?

To get the contents of a CSV file in PowerShell, you can use the Import-Csv cmdlet. This cmdlet reads the contents of a CSV file and creates custom objects from the data. Here’s an example of how to use it:
$data = Import-Csv -Path "C:\temp\data.csv"

How to Export JSON to CSV in PowerShell?

How do I export data to a CSV file without quotes using PowerShell?

How do I handle special characters when exporting to CSV?

How to use PowerShell to Export CSV with specific columns?

Sometimes, you may want to export only specific columns to a CSV file. You can use the Select-Object cmdlet to select only the columns you want to export. You can achieve this by using the Get-Service cmdlet to retrieve the list of services, selecting the desired properties using the Select-Object cmdlet, and then piping the output to the Export-CSV cmdlet. Here’s an example:

Get-Service | Select-Object Name, DisplayName, Status | Export-CSV -Path "C:\PS\services.csv" -NoTypeInformation

In this example, the Get-Service cmdlet retrieves a list of all the Windows services on the system. We then use the Select-Object cmdlet to choose the properties we want to include in the CSV file (in this case, Name, DisplayName, and Status). Finally, we pipe the output to the Export-CSV cmdlet and specify the path where we want to save the file using the -Path parameter.

Filter & Sort Data and Export to CSV

You can also filter and sort the data based on specific criteria. PowerShell provides the Where-Object and Sort-Object cmdlets for these purposes.

#Filter
$FilteredData = Get-Service| Where-Object { $_.Status -eq "Running" }
#Sort
$SortedData = $FilteredData | Sort-Object Name
#Export Data
$SortedData | Export-CSV -Path "C:\PS\services.csv" -NoTypeInformation

In this example, we are filtering the imported CSV data to only include rows where the Status property is equal to “Running”.

By default, the Export-CSV cmdlet includes a header row in the CSV file with the property names as column headers. However, if you are working with data that does not have a header, or you want to customize the header, you can use the -Header parameter to specify a custom header row.

Get-Process | Select-Object ID, Name, CPU, Memory, WorkingSet, Handles | Export-CSV "C:\PS\processes.csv" -NoTypeInformation -Header "Process Name", "CPU Usage", "Memory Usage"

When converting Excel files to CSV format, the resulting CSV file includes the headers from the Excel file. However, there may be scenarios where we want to exclude the headers from the CSV file. To achieve this, we can utilize PowerShell’s pipeline functionality and the Select-Object cmdlet.

Here’s an example script that removes the headers from a CSV file:

# Import the CSV file
$data = Get-Content -Path "C:\Scripts\UsersData.csv"
# Remove the first row (headers)
$data = $data | Select-Object -Skip 1
# Export the modified data to a new CSV file
Set-Content "C:\Scripts\UsersData.csv"

In this script, we start by importing the CSV file using the Get-Content cmdlet. Next, we use the Select-Object cmdlet with the -Skip parameter to exclude the first row (headers) from the data. Finally, we export the modified data to a new CSV file using the Set-Content cmdlet.

Using Quotes in CSV Values (PowerShell 7 only)

When exporting data to a CSV file, you may encounter values that contain special characters, such as commas or double quotes. To handle these cases, the Export-CSV cmdlet provides the -UseQuotes parameter, which allows you to specify how quotes should be used in CSV values.

Get-Service | Export-CSV "C:\Scripts\Service-output.csv" -NoTypeInformation -UseQuotes AsNeeded

In this example, we are exporting service data using the Get-Service cmdlet. We specify the -UseQuotes parameter with the value AsNeeded (Other possible values are: “Never”, “Always” – default value), which instructs PowerShell to use quotes around CSV values only if they contain special characters. This ensures that the exported CSV file maintains proper formatting and can be correctly parsed by other applications.

Error Handling and Logging in Data Export Scripts

try { # Data export logic here # ... $data | Export-CSV "C:\Path\to\output.csv" -NoTypeInformation
}
catch { # Error handling and logging logic here # ... Write-Error $_.Exception.Message $_.Exception | Out-File "C:\Path\to\error.log" -Append
}
finally { # Cleanup and finalization logic here # ...
}

Further Reading and Real-world examples

Here is the list of my real-world articles that use the Export-CSV cmdlet:

Other Recommended Reading: Microsoft Docs – Export-Csv documentation: https://learn.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-7.3

Exporting data to CSV with multiple commands

You can use multiple commands to export data to CSV. This is useful when you want to perform certain actions before exporting the data. For example, let’s say you have a list of orders in your system, and you want to export them to a CSV file with the total amount.

$orders = Get-Order -Filter * $orders | Select-Object OrderNumber, Customer, @{Name="Total";Expression={$_.Price * $_.Quantity}} | Export-CSV -Path "C:\Orders.csv" -NoTypeInformation

In the above example, we use the Get-Order cmdlet to get a list of orders from our system. We then use the Select-Object cmdlet to select the OrderNumber, Customer, and Total properties. The Total property is calculated based on the Price and Quantity properties. Finally, we use the Export-CSV cmdlet to export the data to a CSV file.

Best practices for exporting to CSV with PowerShell

• Always include headers in the CSV file.
• Use a delimiter that is not present in the data.
• Use UTF8 encoding for the CSV file.
• Use filters to export only the data that meets specific criteria.
• Use multiple commands to perform certain actions before exporting the data.

Exporting Multiple Data Sources to a Single CSV

In some cases, you might need to combine data from multiple sources into a single CSV file. You can do this using the ForEach-Object cmdlet and the -Append parameter to append the data into a CSV with Export-Csv. Let’s say you have a list of server names and want to export information about running processes on each server to a single CSV file:

$serverList = "Server1", "Server2", "Server3"
$serverList | ForEach-Object { Get-Process -ComputerName $_ | Export-Csv -Path .\AllServersProcesses.csv -Append
}

This script will create a CSV file named “AllServersProcesses.csv” containing information about running processes on all servers in the $serverList array.

Exporting Multiple Objects to a Single CSV File

You can export multiple objects to a single CSV file using the Export-CSV cmdlet. For example, let’s say you have a list of employees and orders in your system, and you want to export them to a single CSV file.

$employees = Get-ADUser -Filter * -Properties Name, EmailAddress
$orders = Get-Order -Filter *
$employees, $orders | Export-CSV -Path "C:\Data.csv" -NoTypeInformation

Exporting data to CSV with filters

You can use filters to export only data that meets specific criteria. For example, let’s say you have a list of employees in your system and want to export only the employees who work in the IT department.

Get-ADUser -Filter {Department -eq "IT"} -Properties Name, EmailAddress | Select-Object Name, EmailAddress | Export-CSV -Path "C:\IT-Employees.csv" -NoTypeInformation

For cmdlets that don’t support filter parameters, you can use the “Where” clause.

Get-Process | Where-Object { $_.CPU -gt 100 } | Export-CSV -Path "C:\path\to\your\file.csv" -NoTypeInformation

Common Errors to Troubleshoot

Here are some common errors that you may encounter when exporting data to CSV with PowerShell and how to troubleshoot them:

• “Cannot convert the “System.Object[]” value of type “System.Object[]” to type “System.String”.” This error occurs when you are trying to export an array of objects that contain complex properties. To fix this error, use the Select-Object cmdlet to select only the properties you want to export.
• “The process cannot access the file because it is being used by another process.” This error occurs when the CSV file is already open in another application. Close the file in the other application and try again.
• “Cannot find path ‘C:\Users.csv’ because it does not exist.” This error occurs when the path of the CSV file is incorrect. Double-check the path and try again.

Appending to an Existing CSV File using Append Parameter

By default, the Export-CSV cmdlet creates a new CSV file (if the file exists already, it will be overwritten). In some scenarios, you may need to add new data to an existing CSV file without overwriting its contents. The Export-CSV cmdlet provides the -Append parameter to achieve this. This is particularly useful when you want to update a CSV file periodically or combine multiple sets of data into a single file.

$additionalData = Get-Service | Select-Object Name, DisplayName, StartType
$additionalData | Export-CSV "C:\PS\services.csv" -NoTypeInformation -Append

In this example, we retrieve additional data about the services and append it to the existing “C:\PS\Services.csv” file. The -Append parameter ensures that the new data is added to the end of the file without replacing the existing content.

Send a PowerShell Command’s Output to a Text File

To write your PowerShell command’s output to a text (TXT) file, first launch a PowerShell window. Here, type whatever command you need, the output of which you want in a text file. After you’ve typed the command, press Spacebar, type the > (greater than) symbol, press Spacebar, enter the full path to the text file where you want to save the output, and press Enter.

systeminfo > C:\Users\mahes\Desktop\SystemInfo.txt

As soon as you press Enter, PowerShell creates your specified file and adds your command’s result to it. When that’s done, access your specified path, and you’ll find your newly created file there.

Send a PowerShell Command’s Output to a CSV File

If you want to create a CSV file containing the output of your specified PowerShell command, use the tool’s “Export-CSV” cmdlet.

Get-ChildItem | Export-CSV C:\Users\mahes\Desktop\List.csv -NoTypeInformation

When you’ve finished running the command, you’ll have a file called “List.csv” on your desktop containing the list of the items in your current directory.

Похожее:

Полезные заготовки вызова утилит командной строки Power shell и его возможности в уходящем году Список power shell array: руководство для начинающих Освоение командной строки