Use PowerShell to Work with CSV Formatted Text

Use PowerShell to Work with CSV Formatted Text

  • Comments 9
  • Likes

Summary: See how to use Windows PowerShell to create CSV files from formatted and unformatted text.

 

Hey, Scripting Guy! QuestionHey, Scripting Guy! I have begun to play around with Windows PowerShell, and it is absolutely the most confusing thing Microsoft has ever created. Simple things are easy. I can use Get-Process and Get-Service with no problem, but the moment I begin to think I can use this tool, I get kicked in the teeth. A case in point is the Export-CSV cmdlet. When I first saw this, I thought, “Well, now, this is cool!” But I have yet to see how cool it really is. The thing is nearly impossible to use. I just don’t get it. All I want to do is save data in a CSV file, so I can open it up in Microsoft Excel. Is the cmdlet broken?

—BB

 

Hey, Scripting Guy! AnswerHello BB,

Microsoft Scripting Guy Ed Wilson here. I can certainly sympathize with you. I get this question quite a bit, unfortunately. Part of the problem is that the cmdlet does not really do what you think it will. For example, if I have a string with a CSV listing, and I write it to a CSV file by using the Export-CSV cmdlet, I might use code that looks like the following:

$Outputstring = "dog","Cat","Mouse"

$OutputString | Export-Csv C:\fso\csvTest.csv

However, when I look at the csvtest.csv file, the results are disappointing. The file created by the preceding code is shown in the following figure.

Image of file created by preceding code

The first time I saw this, I could not believe my eyes. I actually deleted the file and ran the command a second time to make sure of the results. To my chagrin, the second file appeared as the first. Neither was a CSV file.

There are two Windows PowerShell cmdlets that work with comma-separated values: ConvertTo-CSV and Export-CSV. The two cmdlets are basically the same; the difference is that Export-CSV will save to a text file, and ConvertTo-CSV does not. The cmdlets are useful for working with deserialized objects. For example, if I want to be able to analyze process information at a later date, I can use the Get-Process cmdlet to store the objects in a text file. I can then use Import-CSV to reconstitute the process objects. This is shown here:

PS C:\> Get-Process winword | Export-Csv c:\fso\procWord.csv

PS C:\> $a = Import-Csv C:\fso\procWord.csv

PS C:\> $a.Name

WINWORD

The complete text of the procWord.csv file is shown in the following figure.

Image of complete text of procWord.csv file

As shown in the preceding figure, the CSV file created by Export-CSV consists of three parts. The first is the type of object stored in the file. The second is the column headings, and the third contains the property values. If more than one object were stored in the file, the remaining lines would contain additional property values. If a property did not exist on the object, the file would be padded by commas. When the object is reconstituted via the Import-CSV cmdlet, all the properties stored in the file—but none of the methods—return to the object. A reconstituted object is devoid of any methods.

If I want to save process information as a CSV file because I am planning to open the file in Excel, I use the NoTypeInformation switched parameter of the Export-CSV cmdlet. This technique is shown here (GPS is an alias for the Get-Process cmdlet):

GPS winword,Excel,Outlook | Export-Csv c:\fso\procoff.csv –NoTypeInformation

When I open the CSV file in Microsoft Excel, each object appears on its own line. The properties are in the first line as column headers. This is shown in the following figure.

Image of Excel file with each object on its own line

BB, as shown so far, the Export-CSV cmdlet is great at taking objects and storing them in an offline format so that they can be reconstituted for later analysis and comparison. In addition, I can use the Export-CSV cmdlet to save objects and then view the properties in Microsoft Excel. If I do not want all of the properties, I can create a custom object by piping to the Select-Object cmdlet first. In the following command, I use gps (the alias for Get-Process) to return information about each process on the machine. I then choose only three properties from the objects: id, processName, and CPU. This information is exported into a CSV file. This technique is shown here:

gps | Select-Object id, processName, CPU | Export-Csv c:\fso\co.csv –NoTypeInformation

The saved data is shown in the following figure when viewed in Microsoft Excel.

Image of saved data viewed in Excel

If you want to pipe your array of strings to the Export-CSV cmdlet, you will need to first convert them into objects. This is because you need a custom object with multiple properties, instead of a series of single property strings. This is the problem you were wrestling with earlier—you were not providing the Export-CSV cmdlet with a nice object upon which to work.

Export-CSV treats each object as a new row of data. The columns used with the CSV file are determined by the properties of the object. To work with Export-CSV, it is necessary to create an object for each row of data to be stored. This technique is shown here:

$Outputstring = "dog","Cat","Mouse"

$psObject = $null

$psObject = New-Object psobject

foreach($o in $outputString)

{

 Add-Member -InputObject $psobject -MemberType noteproperty `

    -Name $o -Value $o

}

$psObject | Export-Csv c:\fso\psobject.csv -NoTypeInformation

The resulting CSV file is shown in the following figure.

Image of resulting CSV file

Most of the time, if I need to create a CSV file from unformatted text, I tend to use manual string techniques, as shown here:

$Outputstring = "dog","Cat","Mouse"

$Outputstring -join "," >> c:\fso\joinCSV.csv

The output from this approach is shown in the following figure.

Image of output from this approach

 

BB, those are several ways of working with CSV data and the Export-CSV cmdlet.

I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at scripter@microsoft.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.

Ed Wilson, Microsoft Scripting Guy

 

 

Your comment has been posted.   Close
Thank you, your comment requires moderation so it may take a while to appear.   Close
Leave a Comment
  • Another useful parameter is -Encoding.

    Example:

    Get-Mailbox mbox_* | Select-Object DisplayName, Name, Database, ProhibitSendReceiveQuota | Export-CSV \mbox.csv -NoTypeInformation -Delimiter ";" -Encoding utf8; start \mbox.csv

  • Thanks for the article.

  • Ed - Thanks for this article. A lot of folks encounter the issues that you propose a solution for in this post. As indicated by 'camlost', a useful (in my opinion given the appropriate scenario is an important) parameter of Export-CSV cmdlet is the -Encoding parameter. I have encountered many scenarios where this parameter makes all the difference between frustration and success with regards to the expected csv output. As a suggestion - could you write a 'gotcha' post for the community on the usage of the -Encoding parameter and plausible scenarios where this parameter makes a difference? Cheers. Craig.

  • @camlost, @Craig you are absolutely correct that the -encoding parameter is excellent to use. Keep in mind that Windows PowerShell defaults to unicode, but there are certainly times you need ASCII. For those cases, use -encoding ASCII

    @Q thanks. I am glad you liked the article.

  • What's the most elegant way to use EXPORT-CSV command to handle mulit-value elements?  GET-SERVICE | FORMAT-LIST vs. GET-SERVICE | EXPORT-CSV Service.csv.  The latter generates a column called DependentServices with its type (System.ServiceProcess.ServiceController[]) instead of the array values.  Yet FORMAT-LIST and practically all other format/export commands expands these.  Without writing code specific for this one column from this one command, how can I get EXPORT-CSV to expand array values in general?  

  • @Terry E. Dow The problem occurs because the "property" that you are seeing, contains another object that is rather rich. This is not just with Get-Service, it exists on MANY cmdlets, such as Get-Process. The way to view the rich data is to pipeline to Select-Object and use the ExpandProperty parameter to expand the property. You could then pipeline that to ExportCSV if you wish. There are problems with CSV formats because it is essentially two dimensional, and there is not way to handle another object in a property. XML can do this. I am going to write a Hey Scripting Guy blog article about this. See November 15, 2011 article.

  • Ok, I've run into something like this and I'm stumped. Here is what I have, code + output.

    $Events = Get-WinEvent -ErrorAction Stop -FilterHashtable @{LogName='ForwardedEvents';ID=4733}

    if ($Events.Count)

    {

       $Events = $Events[0]

       }

    $XMLEvent = [XML]$Events.ToXML()

    $tempReport = $XMLEvent.Event.EventData.Data

    $NewReport = @()

    foreach ($line in $tempReport)

    {

       [string]$EventField = [string]$Line.Name.ToString()

       [string]$EventData = [string]$Line."#text".ToString()

       $LineItem = New-Object -TypeName PSObject -Property @{

           EventField = $EventField

           EventData = $EventData

           }

       $NewReport += $LineItem

       }

    $NewReport

    ConvertTo-Csv -InputObject $NewReport

    EventField                                    EventData                                  

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

    MemberName                                    -                                          

    MemberSid                                     S-1-5-21-1248838550-3875098049-756341321-...

    TargetUserName                                Administrators                              

    TargetDomainName                              Builtin                                    

    TargetSid                                     S-1-5-32-544                                

    SubjectUserSid                                S-1-5-21-1248838550-3875098049-756341321-500

    SubjectUserName                               Administrator                              

    SubjectDomainName                             COMPANY                                    

    SubjectLogonId                                0x23a98                                    

    PrivilegeList                                 -                                          

    #TYPE System.Object[]

    "Count","Length","LongLength","Rank","SyncRoot","IsReadOnly","IsFixedSize","IsSynchronized"

    "10","10","10","1","System.Object[]","False","True","False"

    Originally I was doing this.

    $tempReport += $XMLEvent.Event.EventData.Data `

               |Select-Object -Property @{Label='EventField';Expression="Name"}, @{Label='EventData';Expression="#text"}

    I thought perhaps because I was tossing XML data into the array, and then later adding more EventData and EventField items it was cause this problem. This seemed obvious because when I would ConvertTo-CSV the additional field items, they would convert over just fine.

    If anyone could shed light I would really appreciate it.

    Thanks!

  • Ok, so maybe someone can answer this question for me. What is the difference between these two commands? Because the output is COMPLETELY different.

    Export-Csv -InputObject $NewReport -Path .\sample.csv -Force

    $NewReport |Export-Csv -Path .\sample.csv -Force

    The first yields a csv of apparently the object, and the second yields a csv with the actual data in it.

  • Hi,

    I want to know how to apply background color and other formatting aspects to the header column to a csv file using powershell. Can you please help me with some code sample.

    Thanks & Regards,

    Santosh Kumar Patro