PowerShell How-To

Creating CSV Files with PowerShell

The process is similar to creating any plain text file in PowerShell -- but with a few twists.

• By Adam Bertram
• 06/08/2017

Storing data in a structured way is important when that data will be read by a computer. One of the easiest ways to put data into an easy-to-read format is with a comma-separated value (CSV) file. A CSV file can just be thought of like a spreadsheet without all the bells and whistles. A CSV file consists of a line of headers to indicate column name and subsequent values for each column all separated by a comma.

When put in a position where you need to structure data in a CSV file, PowerShell has a few ways to make that happen. Since a CSV file is just a text file, it can loosely be created with PowerShell's Add-Content cmdlet. The Add-Content cmdlet can create text files and populate them with strings. Even though Add-Content does not natively understand a CSV file, it would still be able to read one. For example, perhaps I want to create a list of employee names in a CSV file. We can do this by manually creating the headers and then each employee like so:

Add-Content -Path C:\Employees.csv  -Value '"FirstName","LastName","UserName"'

  

  $employees = @(

  '"Adam","Bertram","abertram"'

  '"Joe","Jones","jjones"'

  '"Mary","Baker","mbaker"'

  )

  $employees | foreach { Add-Content -Path  C:\Employees.csv -Value $_ }

We can then read the CSV created and prove that it is a well-structured CSV file since now Import-Csv can read the file and understand that the headers should be the properties and each employee represents the data.

PS> Import-Csv C:\Employees.csv

  

  FirstName LastName UserName

  --------- -------- --------

  Adam       Bertram  abertram

  Joe        Jones    jjones

  Mary       Baker    mbaker

Add-Content works but since it doesn't natively understand the CSV's structure, it's easy to make typos. Instead, the preferred approach would be to use the Export-Csv cmdlet. Although this cmdlet cannot take any old value like Add-Content can, it's "safer" in that it understands the structure of a CSV file and thus can return errors if the user attempts to add something to the CSV file that may break the overall schema.

Export-Csv creates a CSV file by accepting one or more objects and converting those objects into comma-separated values and storing them in a file. Using our employee example above, we'd need to change the array of three strings into an array of objects that Export-Csv would understand. Notice below that we no longer have to add the Headers line like we did with Add-Content. Export-Csv understands the each object's property name is a header and the values are the data.

$employees = @(

  [pscustomobject]@{

  FirstName = 'Adam'

  LastName  = 'Bertram'

  Username  = 'abertram'

  }

  [pscustomobject]@{

  FirstName = 'Joe'

  LastName  = 'Jones'

  Username  = 'jjones'

  }

  [pscustomobject]@{

  FirstName = 'Mary'

  LastName  = 'Baker'

  Username  = 'mbaker'

  }

  )

  $employees | Export-Csv -Path C:\Employees2.csv

The code above would create the same CSV file as we did with Add-Content. Notice that I chose a different file path for this example. A new file path is used because, by default, Export-Csv overwrites any existing CSV that's specified. When performing routine scripting, it's a frequent need to append rows to the CSV rather than replacing the entire file. To add rows, rather than replacing the file, we can use the Append parameter. This will ensure that the CSV file is not overwritten.

One final parameter to be aware of with Export-Csv is the NoTypeInformation parameter. By default, Export-Csv writes object type information at the top of the CSV. I have never had a need for this information and need to exclude it. By using the NoTypeInformation does not record that information. An example of using these two parameters looks like this:

[pscustomobject]@{ FirstName =  'Mary'; LastName = 'Baker'; Username = 'mbaker' } | Export-Csv -Path  C:\Employee.csv -Append -NoTypeInformation

PowerShell has a few ways to create and write to CSV files. I recommend converting data to structured objects and using Export-Csv if you can otherwise Add-Content will get you by. Just watch out for those typos!