CODEWITHSUNDEEP
powershellcsv
Glowie
Glowie

Reputation: 2309

CSV separates Date in two cells

PowerShell program reads data from fixed Database in format Month Day, Year.

CSV outputs Month and Day in one cell, and Year in next cell.

How to fix this? I tried putting single and double-quotes around $i[6] and $av_date, and it still doesn't work

Code snippit

foreach ($i in $sql_output){
    $computer_name = $i[0]
    $ip = $i[1]
    $computer_domain = $i[2]
    $computer_os = $i[3]
    $current_group = $i[4]
    $sep_version = $i[5]
    $av_date = $i[6]
    Add-Content D:\Script\Reports\IP_Status_$date.csv "$computer_name,$ip,$computer_domain,$computer_os,$current_group,$sep_version,$av_date"
}

Output where entire date is not under Antivirus Date column

enter image description here

Upvotes: 0

Views: 64

Answers (3)

user2226112
user2226112

Reputation:

You shouldn't hack together functionality which PowerShell has built in. Just build an array of objects and use Export-Csv or Export-Csv -Delimiter ';' if that is required for your comma issue.

A quick example showing how to iterate over some input objects and building the output:

1..10 | foreach {
    New-Object PSObject -Property @{
        Index = $_
        String = "Just some text, with a comma $(3 * $_)"
        Date = (Get-Date).AddDays($_)
    }
} | Export-Csv test.csv -NoTypeInformation

Upvotes: 1

Michael Oryl
Michael Oryl

Reputation: 21662

It seems that whatever spreadsheet you are using (you don't mention it), that it ignores quotes and only looks for commas when parsing columns.

Generally putting something inside double quotes deals with this issue. Have you tried putting the quotes as shown below (\" quotes around av_date)?

Add-Content D:\Script\Reports\IP_Status_$date.csv "$computer_name,$ip,$computer_domain,$computer_os,$current_group,$sep_version,\"$av_date\""

Perhaps you could also try escaping the comma in the dates so that you'd have Mar 05\, 2015 instead of the normal comma in there.

Either that, or change the format of your date to Mar-05-2015 or something similar that doesn't use commas.

Upvotes: 1

Ifedi Okonkwo
Ifedi Okonkwo

Reputation: 3656

Try inserting this on the line just before your Add-Content (note the triple quotes):

$av_date = """$av_date"""

Then go ahead:

Add-Content D:\Script\Reports\IP_Status_$date.csv "$computer_name,$ip,$computer_domain,$computer_os,$current_group,$sep_version,$av_date"

The comma in the date requires that the field is enclosed in quotes, and this is one way of achieving that.

Upvotes: 2

Related Questions