PowerShell Export-Csv with no header


I’m working on a project that provides billing information on 1 specific service – Lyris ListManager messages sent by company (Or in ListManager parlance, site)

I had all the data, and exported it to a csv as that was the format that we agreed to provide to the billing people.

Life was good for about ten minutes until the billing people said “We don’t want that header on your file, we just want the data”

Export-Csv has no ability to export data only, so I had to rethink my output strategy.

Get-Help *csv*shows that there is a shiny new cmdlet for v2 “Convertto-Csv”

Get-Help Convertto-Csv says “The ConvertTo-CSV cmdlet returns a series of comma-separated, variable-length (CSV) strings that represents the objects that you submit. “

So, I can take my output object, and convert it to csv format, without saving to file.

Now I can manipulate the data, and save just what I need.

Try this example that shows the result:

001

Get-WmiObject Win32_OperatingSystem `
|Select-Object CSName,Caption,Version,OSArchitecture `
|Export-Csv OSInfo.csv -NoTypeInformation

This will create the following output when run against my computer:

001
002

"CSName","Caption","Version","OSArchitecture"
"Karl-PC","Microsoft Windows Server 2008 R2 Standard ","6.1.7600","64-bit"

Now try this:

001
002

003

Remove-Item -Path "OSInfo.csv"
Get-WmiObject Win32_OperatingSystem `
|Select-Object CSName,Caption,Version,OSArchitecture `
|ConvertTo-Csv -OutVariable OutData -NoTypeInformation
$OutData[1..($OutData.count  1)] `
| ForEach-Object {Add-Content -Value $_ -Path "OSInfo.csv"}

This will create the following output when run against my computer:

001

"Karl-PC","Microsoft Windows Server 2008 R2 Standard ","6.1.7600","64-bit"

Advertisements
  1. #1 by Someone on December 30, 2010 - 07:09

    Thanks – very useful!!

  2. #3 by Saiyan on May 24, 2011 - 09:24

    Nice solution, this would work to:

    … | ConvertTo-Csv -NoTypeInformation | Select-Object -Skip 1 | Out-File -Path ‘File.csv’

    • #4 by Karl Mitschke on June 9, 2011 - 10:07

      Cool – thanks!

      Of course, the parameter is FilePath
      | Out-File -FilePath ‘File.csv’

  3. #5 by mohdfarid914 on February 13, 2012 - 18:09

    Cool… alternatively, since it is only two-row output, we can also do it as follows:

    … | ConvertTo-Csv -NoTypeInformation | Select -Last 1 | Out-File -Path ‘File.csv’

    Cheers! ^_^

    • #6 by Karl Mitschke on February 14, 2012 - 08:14

      The example is only two rows, but the real data is hundreds of rows. The previous commet with the “Select -Skip 1” works fairly well in that situation.
      However, in my testing neithe method provides a valid .csv file that can be opened in Excel.

      Thanks for the comment!

      • #7 by psUser on February 27, 2012 - 07:43

        I just used the out-file method for a solution I am working on, and ran into this problem. I found out the issue is the encoding of the file that is output, and you can easily make it “valid” by adding

        -Encoding ASCII

        to your out-file cmdlet.

        Cheers

      • #8 by Karl Mitschke on March 5, 2012 - 14:50

        That’s good info to know – shows there’s more than one way to scin a PowerShell script 😉

  4. #9 by Finn Boje Johannessen on February 20, 2013 - 02:53

    Alternative use:
    ConvertTo-Csv -NoTypeInformation | Select -Skip 1 (this will skip the first line)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: