none
Powershell out-file csv to columns

    Question

  • Is there a way to use the out-file command and load data into a csv file so when opened into excel it shows data in multiple colums.

     

    Example. Loop with three variables($a, $b, and $c). each time through the loop, the variables are loaded with different data. at the end of each loop, there is an out-file (with append) command that writes to a CSV and puts $a in column A, $b in column b, and $c in column c.

     

    Is this possible?

    Wednesday, November 2, 2011 1:54 PM

Answers

All replies

  • Have you tried this?

    Get-Help Export-Csv
    



    Rich Prescott | Infrastructure Architect, Windows Engineer and PowerShell blogger | MCITP, MCTS, MCP

    Engineering Efficiency
    @Rich_Prescott
    Client System Administration tool
    AD User Creation tool
    • Proposed as answer by Bigteddy Wednesday, November 2, 2011 2:14 PM
    • Marked as answer by IamMred Sunday, November 6, 2011 11:55 PM
    Wednesday, November 2, 2011 2:04 PM
    Moderator
  • You can use Export-CSV as Rich points out, but to also answer your question about Out-File, below is an example of how you can use it to accomplish your goal.

    "{0}`t{1}`t{2}" -f "ColumnA","ColumnB","ColumnC" | Out-File -FilePath "file.csv"
    1..10 | ForEach {
        "{0}`t{1}`t{2}" -f $_,($_+1),($_+2) | Out-File -FilePath "file.csv" -Append
    }

    Wednesday, November 2, 2011 2:15 PM
    Moderator
  • you can't append with export-csv, at least, not as far as I can see. so i wouldn't be able to use it in the loop.

    Wednesday, November 2, 2011 2:33 PM
  • You can append to a .csv with Dmitry's function:

    http://dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append/

    You can also try my method on http://unlockpowershell.wordpress.com/2009/12/15/powershell-export-csv-with-no-header/

    Karl


    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})
    Wednesday, November 2, 2011 2:42 PM
  • You can append to a .csv with Dmitry's function:

    http://dmitrysotnikov.wordpress.com/2010/01/19/export-csv-append/

    You can also try my method on http://unlockpowershell.wordpress.com/2009/12/15/powershell-export-csv-with-no-header/

    Karl


    My Blog: http://unlockpowershell.wordpress.com
    My Book: Windows PowerShell 2.0 Bible
    My E-mail: -join ("6B61726C6D69747363686B65406D742E6E6574"-split"(?<=\G.{2})",19|%{[char][int]"0x$_"})

    As a side note, PowerShell V3 does include -Append on Export-CSV. But note that it is currently in CTP and should not be used in a production environment.
    Wednesday, November 2, 2011 3:09 PM
    Moderator
  • you can't append with export-csv, at least, not as far as I can see. so i wouldn't be able to use it in the loop.

    The usual course that most people take is to store the objects in a variable and when the loop is done, pipe the variable out to Export-Csv.

    Rich Prescott | Infrastructure Architect, Windows Engineer and PowerShell blogger | MCITP, MCTS, MCP

    Engineering Efficiency
    @Rich_Prescott
    Client System Administration tool
    AD User Creation tool
    Wednesday, November 2, 2011 3:33 PM
    Moderator
  • Wednesday, November 2, 2011 9:19 PM
  • I know this is really old but I found this page when I was searching for a similar solution and here is what I found:

    To make sure the data gets in separate columns when you try to open it in excel, make sure you use the parameter "-encoding ascii" for the out-file part of the script

    so it would look something like

    "format your 3 variables as required here" | convertto-csv -notypeinformation | select skip 1 | out-file -encoding ascii -append $filename

    Friday, November 1, 2013 1:30 AM
  • I know this is really old but I found this page when I was searching for a similar solution and here is what I found:

    To make sure the data gets in separate columns when you try to open it in excel, make sure you use the parameter "-encoding ascii" for the out-file part of the script

    so it would look something like

    "format your 3 variables as required here" | convertto-csv -notypeinformation | select skip 1 | out-file -encoding ascii -append $filename

    Just use Export-Csv. It has an -Encoding parameter and the default is already ascii.

    http://technet.microsoft.com/en-us/library/hh849932%28v=wps.620%29.aspx


    Don't retire TechNet! - (Maybe there's still a chance for hope, over 12,300+ strong and growing)

    Friday, November 1, 2013 2:53 AM
  • I know this is really old but I found this page when I was searching for a similar solution and here is what I found:

    To make sure the data gets in separate columns when you try to open it in excel, make sure you use the parameter "-encoding ascii" for the out-file part of the script

    so it would look something like

    "format your 3 variables as required here" | convertto-csv -notypeinformation | select skip 1 | out-file -encoding ascii -append $filename

    This is what helped me the most. All these looping answers aren't the best solution in my case. The CSV file I was writing is so huge that writing it all at the end is doomed to fail.
    Tuesday, June 30, 2015 4:28 PM
  • I don't comment out here much, but I have to agree that the out-file -encoding ascii solution is much for efficient and has many more uses beyond PowerShell one-liners.  As BrendanM721
     points out, sometimes we have to print a lot of data and storing that all at once through piping uses a lot of memory, and is not reliable.  The root problem is that powershell is designed to toss objects around and export-csv is very good at printing object properties, but printing strings to a file and appending within a control structure is best done with out-file.
    Wednesday, January 18, 2017 4:35 PM
  • Data is not stored in a pipeline. A pipeline nearly always uses less memory and is faster.  That is why we use it.

    $somedate |
         ForEach-Object{
              ... prepare the data
             [pscustomobject]@{
                  A=$a
                  B=$b
                  C=$c
            }
        } |
        Export-Csv somefile.csv

    This s a constant programming pattern and is implemented in a very nice way in powershell.


    \_(ツ)_/

    Wednesday, January 18, 2017 4:48 PM
  • This is what I was looking for :) I achieved what I wanted. 

    Thanks,

    Ashish

    Tuesday, September 12, 2017 3:56 PM
  • you can't append with export-csv, at least, not as far as I can see. so i wouldn't be able to use it in the loop.

    EXACTLY what I found as well, looking at out-file to format in columns too

    Patrick Burwell, Sr. Systems Engineer

    Thursday, September 13, 2018 9:55 PM
  • ONlY prints the LAST line of the input file bud

    Out-file works better


    Patrick Burwell, Sr. Systems Engineer

    Thursday, September 13, 2018 9:57 PM
  • you can't append with export-csv, at least, not as far as I can see. so i wouldn't be able to use it in the loop.

    EXACTLY what I found as well, looking at out-file to format in columns too

    Patrick Burwell, Sr. Systems Engineer

    Please don't add bad assumptions to a a topic that has been answered and closed.  If you do not understand how to use the code then open your own topic and ask a complete question.

    Export-Csv supports append.  Have you even read the help?

    https://docs.microsoft.com/en-us/powershell/module/microsoft.powershell.utility/export-csv?view=powershell-6

    It is supported in all current versions of PowerShell.


    \_(ツ)_/

    Thursday, September 13, 2018 10:30 PM
  • ONlY prints the LAST line of the input file bud

    Out-file works better


    Patrick Burwell, Sr. Systems Engineer

    Please don't add bad assumptions to a a topic that has been answered and closed.  If you do not understand how to use the code then open your own topic and ask a complete question.

    Out-File cannot create a CSv.  Please read the help.


    \_(ツ)_/

    Thursday, September 13, 2018 10:30 PM
  • Export-csv has a character limit per value when it exports... this solution seems to maintain all data. Thanks.
    Wednesday, November 7, 2018 10:42 PM
  • Export-Csv has no character limit.


    \_(ツ)_/

    Thursday, November 8, 2018 12:07 AM