CODEWITHSUNDEEP
powershellcsv
Holly George-Samuels
Holly George-Samuels

Reputation: 11

How to write to a csv file with no newline using powershell?

I want each row to be populated with the data retrieved from each file. Currently, the 2nd and 3rd column entries are being written to a newline.CSV file output I have tried using "export-csv" and the "-nonewline" command. Perhaps there is a regex command that would solve this?

#Column headings
$headings = "Source file, Review file existence, Review Result, Compilation Check Result, Static Analysis Result, Review up-to-date, Reviewed code version, Latest code version"
# Create array with $headings as first input
$output = @($headings)


$SourceParentDir = "C:\Document"
$Files = get-childitem -Path $SourceParentDir -Recurse -Filter '*.?pp' | % { $_.FullName }
foreach ($File in $Files)
{
    $BaseName = [System.IO.Path]::GetFileName($File)
# Populate each row for each file
    $output += $BaseName
    $output += ", Review Exists" # writes to a newline
    $output += ", " + $Result + "," + $Compilation + "," + $StaticAnalysis + "," + $UpToDateFlag + "," + $ReviewFileVersionNumber + "," + $SourceFileVersionNumber + ","
}

# write output to a csv file
$output | Out-File -FilePath Documents\Example-csv.csv -encoding utf8

Upvotes: 1

Views: 604

Answers (3)

Joel Coehoorn
Joel Coehoorn

Reputation: 415810

$output is an array, so each of those += inside the loop is a new entry in the array, and therefore a new line in the file.

You can fix this by using a temporary string variable in the middle of the loop, and appending it to $output once at the end of each iteration:

foreach ($File in $Files)
{
    $row = [System.IO.Path]::GetFileName($File)
    $row += ", Review Exists" 
    $row += ", " + $Result + "," + $Compilation + "," + $StaticAnalysis + "," + $UpToDateFlag + "," + $ReviewFileVersionNumber + "," + $SourceFileVersionNumber + ","
  
    $output += $row
}

or by putting everything in one big string interpolation:

foreach ($File in $Files)
{
    $BaseName = [System.IO.Path]::GetFileName($File)
    $output += "$BaseName, Review Exists, $Result, $Compilation, $StaticAnalysis, $UpToDateFlag, $ReviewFileVersionNumber, $SourceFileVersionNumber"
}

But I agree with the other answer that building up an array of custom objects for the Export-Csv commandlet is more idiomatic for PowerShell.

Upvotes: 1

Bacon Bits
Bacon Bits

Reputation: 32170

You can do things that way, but there's definitely a more-Powershelley way:

Get-ChildItem -Path $SourceParentDir -Recurse -Filter '*.?pp' |
ForEach-Object {
    $File = $_

    # Put your other code here

    # This will output an object to the stream
    [PSCustomObject]@{
        'Source file' = $File.Name
        'Review file existence' = 'Review Exists'
        'Review Result' = $Result
        'Compilation Check Result' = $Compilation
        'Static Analysis Result' =  $StaticAnalysis
        'Review up-to-date' = $UpToDateFlag
        'Reviewed code version' = $ReviewFileVersionNumber
        'Latest code version' = $SourceFileVersionNumber
    }   
} | Export-Csv Example-csv.csv -NoTypeInformation

The big drawback here is that you don't get a lot of formatting choices about the CSV. Every field is quoted, for example.

Alternately, if you really want really detailed control of the $output string, you should use a StringBuilder instead of a String. StringBuilder is one of the most potent and widely used classes in C#. This is because strings in C# and Powershell are immutable, so when you += a String you create a new string, copy everything over with the new bit, then throw the old string away. It can be very memory intensive with large operations. StringBuilder lets you get around all that. It's a class that's designed to let you append stuff to strings and format them however you want.

You instance it like so:

$output = [System.Text.StringBuilder]::new()

And then you typically call one of two methods to add text. Append($string) appends the string, AppendLine($string) appends the line and then adds a newline. You can also call AppendLine() with no argument to just add a newline. To get your final string, you call the ToString() method. The append methods do return a status when you call them which you can prevent from outputting pretty easily with a [void], or by saving it to another variable if you need it.

$output = [System.Text.StringBuilder]::new()
[void]$output.AppendLine($headings)


$SourceParentDir = "C:\StarTeam\00011114-JSENS_TRS\ATR\04_SW_Implementation\Operational"
$Files = get-childitem -Path $SourceParentDir -Recurse -Filter '*.?pp' | % { $_.FullName }
foreach ($File in $Files)
{
    $BaseName = [System.IO.Path]::GetFileName($File)
# Populate each row for each file
    [void]$output.Append($BaseName)
    [void]$output.Append(", Review Exists")
    [void]$output.Append(", $Result,$Compilation,$StaticAnalysis,$UpToDateFlag,$ReviewFileVersionNumber,$SourceFileVersionNumber,")
    [void]$output.AppendLine()
}

$output.ToString() | Out-File -FilePath Documents\Example-csv.csv -encoding utf8

Upvotes: 2

zackchadwick
zackchadwick

Reputation: 111

The issue is how you're populating $Output. Since it is defined as an array, each time you're adding new information it's creating a new entry rather than just adding the additional information to a string.

If you make your $output into one line with all required fields it should correct it without you changing the array.

$output += $BaseName + ", Review Exists" + ", " + $Result + "," + $Compilation + "," + $StaticAnalysis + "," + $UpToDateFlag + "," + $ReviewFileVersionNumber + "," + $SourceFileVersionNumber + ","

Upvotes: 0

Related Questions