current community

your communities

more stack exchange communities

Stack Exchange
tour help
stack overflow careers
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite
1

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"

share|improve this question
1  
What was wrong with the way it renedered when you used export-csv? –  mjolinor Apr 5 '13 at 20:38
    
When you save as CSV in Excel 2013, it's delimited with ";" not "," and text is not always in quotes (actually never when I try). What is the sample above ? Is it "input.csv" ? then you modify it, export it and want to keep the same format(multiline per object), or do you want to remove the newlines? Please provide sample for output csv. –  Frode F. Apr 5 '13 at 21:03
    
"first","second","this is all the third column","fourth" Are all new lines? –  Austin French Apr 6 '13 at 3:20
    
Please give an example of what you want to export to a CSV file. –  JPBlanc Apr 6 '13 at 4:47
    
@mjolinor See update. –  JZeolla Apr 6 '13 at 16:03

3 Answers 3

up vote 2 down vote

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
share|improve this answer
    
When using Excel it seems to have no problem showing multi-line fields as long as they are properly formatted. I do not want to remove the newlines, I just want to make sure that when a newline is in the CSV it is formatted in a way that Excel will recognize it should be a single entry. –  JZeolla Apr 7 '13 at 11:56
    
Either I'm having a "stupid-day" or you're not explaining it well enough. If you open the first sample in my "answer" (with ; instead of ,) which is the "proper way" to provide multiline-values in csv, it WILL be read correct in excel. However, you have to resize the rows height to see all the text. Is that what you want? For Excel to understand that it needs to stretch? –  Frode F. Apr 7 '13 at 12:04
    
Please see update2, this should provide some more clarity. –  JZeolla Apr 8 '13 at 15:04
    
See updated answer. –  Frode F. Apr 8 '13 at 15:29
    
Via update3, my output is: "Length" "91" "91" "616" "1234" "1234" "616" "616" "616" "1233" "1234" "607" "616" "616" "852" "852" –  JZeolla Apr 8 '13 at 16:03
up vote 1 down vote

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
share|improve this answer
    
I'm sorry if I wasn't clear; the script imports a csv, then does work on it, comparing it to other files, parsing entries, etc. At that point I have a "final array" which I would like output to a file in a good format. My issue is that during that output stage, the format is messy because it seems to want to add @{} around each object in the array, and it does not quote properly for multi-line entries to be parsed properly. The input is in this proper format, and the format is not manually changed while in an array. It seems that powershell is changing the format slightly (See bullets). –  JZeolla Apr 7 '13 at 12:00
    
Can you give an example of $Array for example in an xml file with Export-Clixml ? –  JPBlanc Apr 7 '13 at 19:24
    
Please see my update2 and let me know if this gives you what you were looking for. I did an Export-Clixml and provided an obfuscated single line (There are multiple entries that are formatted exactly the same). I obviously removed the <S></S>. –  JZeolla Apr 8 '13 at 15:00
up vote 0 down vote accepted

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
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.