Trying to export an array to csv using powershell (array objects are ; delimited and contain newlines)

Question

Okay, so I have this array, which is essentially an Import-Csv of a CSV file. Each object in the array is an entire CSV row. The input file renders properly when I open it with Excel (The columns are correct)

This CSV file is comma delimited, in the following example format:

"first","second","this is all

the third

column","fourth"

I have gotten everything in my script to work properly, except for the part where I need to write this array to a .csv file in a format which will properly render. I have been having issues using Out-File, Export-Csv, and I even tried this but it doesn't give me exactly what I need. Each object needs to be a new line in the csv file, appear only once, and must adhere to Excel's rendering of CSV files (i.e. each column needs to be double quoted and comma delimited).

I even got so desperate that I'm trying to manually format this thing correctly by doing this:

$Array | % { $_ -replace "`"@`{","`n`"" } | % { $_ -replace "}`"","`"`n" } | % { $_ -replace "`;","`"`;`"" } | % { $_ -replace "Plugin ID=","" } | % { $_ -replace "CVE=","" } | % { $_ -replace "CVSS=","" } | % { $_ -replace "Risk=","" } | % { $_ -replace "Host=","" } | % { $_ -replace "Protocol=","" } | % { $_ -replace "Port=","" } | % { $_ -replace "Name=","" } | % { $_ -replace "Synopsis=","" } | % { $_ -replace "Solution=","" } | % { $_ -replace "Description=","" } | % { $_ -replace "Plugin Output=","" } | % { $_ -replace "`";`"","`",`"" } | Out-File "$dirResults\$Results-$Time.csv"    

Essentially, what I'm doing here is a couple of things:

• Remove @{ from the beginning and } from the end of each object
• Change the delimeter from ; to ,
• Put each column in double quotations
• Remove the ColumnHeader= from the beginning of each value

Here is a snippet of code:

$arrayHeader = @("Plugin ID;CVE;CVSS;Risk;Host;Protocol;Port;Name;Synopsis;Description;Solution;Plugin Output")
$OutterArrayException = Import-Csv "$dirExceptions\$FileException" -Delimiter ","
$InnerArrayScanMe = New-Object System.Collections.ArrayList
# This method keeps the arrays separate types
FOREACH ( $Object in $OutterArrayScanMe )
        {   $InnerArrayScanMe.Add("$Object") | Out-Null }
# Note that the OutterArrayException is an Import-Csv from a similarly formatted file as OutterArrayScanMe
FOREACH ( $Exception in $OutterArrayException )
        { $InnerArrayScanMe.Remove("$Exception") }
# This method keeps the arrays separate types
$OutterArrayScanMe = @($arrayHeader) + @($InnerArrayScanMe)
$OutterArrayScanMe | Export-Csv "$dirResults\$FileScanMe-$startTime.csv" -Delimiter ";" -NoTypeInformation

UPDATE:
When I use export-csv, the new lines for an object place the information as a new row in the csv. Thus, something that should look like this gets really messy.

UPDATE2:
There have been some questions about what the format looks like and what the issues are with the format. To clear those up, I have included a single object in the array which demonstrates the issue. Remember, what happens in my script is there is an import-csv of a properly formatted CSV (Has multi line, etc. and renders perfectly), removal of unnecessary objects (using $array.Remove), and then a dump to file (Out-File or Export-Csv). So simple, yet so broken...

Example Array Object (Out-File):
@{Plugin ID=#####; CVE=CVE-####-####; CVSS=#.#; Risk=Medium; Host=###.###.###.###; Protocol=xxx; Port=##; Name=Microsoft ASP.NET xxx; Synopsis=A framework used by the remote web server
vulnerability.; Description=The web server running on the remote host appears to be using Microsoft
ASP.NET, and may be affected by a vulnerability.

Additionally, there is speculation that this vulnerability could result in unicorns and rainbows if an attacker with physical access to the machine plugs in.; Solution=Use an magic filter to block requests to naughty sites.; Plugin Output= Nessus received an naughty page or related error message by requesting
the following URL :

hxxp://###.###.###.###/xxx/xxxxxxxxx
}

Example Array Object (Export-Csv):
#TYPE System.String
"Length"
"616"

Answer 1

The issue is in the script, at this step:

$InnerArrayScanMe = New-Object System.Collections.ArrayList  
# This method keeps the arrays separate types  
FOREACH ( $Object in $OutterArrayScanMe )  
        {   $InnerArrayScanMe.Add("$Object") | Out-Null }

The $InnerArrayScanMe successfully adds the objects from the normal array $OutterArrayScanMe, without changing the array types, however because it is being put into a .NET array, the data is formatted incorrectly from here on out.

The point of doing it the initial way was so I could use the .NET array's .Add and .Remove methods. The workaround so that we don't need to reference objects to be removed by index looks something like this:

FOREACH ( $Exception in $ArrayException )
{
    $ArrayScanMe = $ArrayScanMe | Where-Object {"$_" -ne "$Exception"};
}

That then allows us to do a basic Export-Csv

$ArrayScanMe | Export-Csv "$dirResults\$FileScanMe-$startTime.csv" -Delimiter "," -NoTypeInformation

Answer 2

The problem seems to be with how your manipulating the array. Check this out:

Test.csv

"Age";"Name"
12;"Test name"
13;"My Other
test"

Script

$Array = Import-Csv .\test.csv -Delimiter ";"

#$Array.Remove() is NOT a valid method. An array is of fixed size, and you will get an exception
#Instead, create a new array of items that are filtered through your rules.
#My test rule is only kids who are not 12 years old
$Newarray = $Array | Where-Object { $_.Age -ne "12" }

$Newarray | Export-Csv .\out.csv -Delimiter ";" -NoTypeInformation

Out.csv

"Age";"Name"
"13";"My Other
test"

Everything is like it should be.

DO NOT use -replace on objects. It will convert the object to a string, and you will get the outputs you provided in update2. Ex:

$Array = Import-Csv .\test.csv -Delimiter ";"

$Array -replace "`r`n" | Export-Csv .\out2.csv -Delimiter ";"
$Array -replace "`r`n" | Out-File out2.txt

Out2.csv

#TYPE System.String
"Length"
"25"
"28"

The "TYPE" part is just because you didn't specify -NoTypeInformation with Export-CSV

Out2.txt

@{Age=12; Name=Test name}
@{Age=13; Name=My Othertest}

Btw, Out-File is not a way to export objects, but still, after -replace your array of objects became an array of strings, like the output in Out2.txt

EDIT $OutterArrayScanMe = @($arrayHeader) + @($InnerArrayScanMe) This is your problem. You merge two arrays together. The first object in your new array will be a string-object. Export-CSV uses the first object's properties as headers for every object. Since the first object is a string(only Length property), all objects will be exported with Length property only.

As said, Export-CSV uses the objects properties as headers(unless you specify another header-definition like | Select Age, Name | Export-Csv). So if $InnerArrayScanMe is an array of objects with the same properties, simply use:

$InnerArrayScanMe | Export-Csv "$dirResults\$FileScanMe-$startTime.csv" -Delimiter ";" -NoTypeInformation

and Export-CSV will figure out the headings by itself. Summary: DON'T add that header-string of yours to the array. If you need a specific order orneed to exclude some properties, you can use pass an string-array to select-object define the header, like this:

$arrayHeader = @("Plugin ID","CVE","CVSS","Risk","Host","Protocol","Port","Name","Synopsis","Description","Solution","Plugin Output")

$arrlist | Select-Object -Property $arrayHeader | 
Export-Csv "$dirResults\$FileScanMe-$startTime.csv" -Delimiter ";" -NoTypeInformation

Answer 3

Not sure to understand your problem, you can do the following :

Import-Csv C:\temp\nessus.csv | where {$_.risk -eq "high"}
Import-Csv C:\temp\nessus.csv | where {$_.risk -eq "high"} | select host -Unique

You can export it to a CSV editable with Excel

Import-Csv C:\temp\nessus.csv | where {"high","medium","low" -contains $_.risk} | select "Plugin ID", CVE, CVSS, Risk, Host, Protocol, Port, Name | Export-Csv 'c:\temp\nessusExcel.csv' -Delimiter ';' 

You can show in a gridview :

Import-Csv C:\temp\nessus.csv | where {"high","medium","low" -contains $_.risk} | select "Plugin ID", CVE, CVSS, Risk, Host, Protocol, Port, Name | Out-GridView

Export to an HTM file :

Import-Csv C:\temp\nessus.csv | where {"high","medium","low" -contains $_.risk} | select "Plugin ID", CVE, CVSS, Risk, Host, Protocol, Port, Name | ConvertTo-Html -As List > report.html