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

okay, have array, import-csv of csv file. each object in array entire csv row. input file renders when open excel (the columns correct)

this csv file comma delimited, in following example format:

"first","second","this all

the third

column","fourth"

i have gotten in script work properly, except part need write array .csv file in format render. have been having issues using out-file, export-csv, , tried this doesn't give me need. each object needs new line in csv file, appear once, , must adhere excel's rendering of csv files (i.e. each column needs double quoted , comma delimited).

i got desperate i'm trying manually format thing correctly 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, i'm doing here couple of things:

• remove @{ beginning , } end of each object
• change delimeter ; ,
• put each column in double quotations
• remove columnheader= beginning of each value

here 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 # method keeps arrays separate types foreach ( $object in $outterarrayscanme )         {   $innerarrayscanme.add("$object") | out-null } # note outterarrayexception import-csv formatted file outterarrayscanme foreach ( $exception in $outterarrayexception )         { $innerarrayscanme.remove("$exception") } # method keeps arrays separate types $outterarrayscanme = @($arrayheader) + @($innerarrayscanme) $outterarrayscanme | export-csv "$dirresults\$filescanme-$starttime.csv" -delimiter ";" -notypeinformation 

update:
when use export-csv, new lines object place information new row in csv. thus, should gets messy.

update2:
there have been questions format looks , issues format. clear up, have included single object in array demonstrates issue. remember, happens in script there import-csv of formatted csv (has multi line, etc. , renders perfectly), removal of unnecessary objects (using $array.remove), , dump file (out-file or export-csv). simple, yet 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 remote web server
vulnerability.; description=the web server running on remote host appears using microsoft
asp.net, , may affected vulnerability.

additionally, there speculation vulnerability result in unicorns , rainbows if attacker physical access machine plugs in.; solution=use magic filter block requests naughty sites.; plugin output= nessus received naughty page or related error message requesting
following url :

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

example array object (export-csv):
#type system.string
"length"
"616"

the problem seems how manipulating array. check out:

test.csv

"age";"name" 12;"test name" 13;"my other test" 

script

$array = import-csv .\test.csv -delimiter ";"  #$array.remove() not valid method. array of fixed size, , exception #instead, create new array of items filtered through rules. #my test rule kids 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 should be.

do not use -replace on objects. convert object string, , outputs 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 because didn't specify -notypeinformation export-csv

out2.txt

@{age=12; name=test name} @{age=13; name=my othertest} 

btw, out-file not way export objects, still, after -replace array of objects became array of strings, output in out2.txt

edit $outterarrayscanme = @($arrayheader) + @($innerarrayscanme) problem. merge 2 arrays together. first object in new array string-object. export-csv uses first object's properties headers every object. since first object string(only length property), objects exported length property only.

as said, export-csv uses objects properties headers(unless specify header-definition | select age, name | export-csv). if $innerarrayscanme array of objects same properties, use:

$innerarrayscanme | export-csv "$dirresults\$filescanme-$starttime.csv" -delimiter ";" -notypeinformation 

and export-csv figure out headings itself. summary: don't add header-string of yours array. if need specific order orneed exclude properties, can use pass string-array select-object define header, 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