CODEWITHSUNDEEP
powershellcsvpowershell-2.0powershell-3.0export-to-csv
KTM
KTM

Reputation: 95

Can I add title rows to a CSV export in Powershell?

I'm exporting a report to a CSV in Powershell and I've been asked to provide three rows above the header row with title information, date of report, etc.

If I export to CSV, i.e. $reportdata | export-csv -notypeinformation fooreport.csv, and then manually add the Title rows in Notepad, i.e.,

"Title"
"Date of Report - XX-XX-XXXX"
 "***Important disclaimer about report***"
"Column1","Column2","Column3", etc
"Data1","Data2","Data3", etc

the CSV Report opens fine in Excel and the header and data rows are handled appropriately.

However, when I attempt to do the following in PS,

#Create Report Header

$date = get-date -format "dd MMM yyyy"

$title = @"
"Mailbox Send As, Full Permission and Send on Behalf Report"
"$date"
"***** Important Disclaimer - Only Explicit or Non-Inherited Permissions Are Displayed in this Report *****"
"@

$reportpath = "D:\dev\report.csv"

$rep | export-csv -notypeinformation $reportpath

$temp = gc $reportpath

echo $title > $reportpath

$temp >> $reportpath

the CSV doesn't open properly in Excel and is improperly delimited (i.e., quotes and commas are visible in the cells), even though both files look identical in Notepad.

Is there a way to do what I'm trying to do from Powershell without messing up the integrity of the CSV file? I'm wondering if I'm somehow removing some critical piece of information that signifies a header row in CSV files - since both files are identical, I'm wondering if I"m maybe missing a hidden or special character?

Upvotes: 2

Views: 3433

Answers (1)

Matt
Matt

Reputation: 46710

You should not need to output to file with export-csv just to read the file back in to make a change. Lets output the data all at once without reading the back in again. 

$rep = $rep | ConvertTo-Csv -NoTypeInformation

$title | Set-Content -Path $reportpath
$rep | Add-Content -Path $reportpath

Not what I intended since I think you could get this in one line but you dont need to read the file back this way. Mostly because it was bugging me I got a one-liner that I was looking for. 

$reportpath = "D:\dev\report.csv"
Set-Content -Path c:\temp\export.csv -Value "$title`r`n$(($rep | ConvertTo-Csv -NoTypeInformation) -Join "`r`n")"

ConvertTo-Csv creates a string array. We join all the strings with a newline "`r`n". Then we attach the title to that data and split it up with another newline.

Upvotes: 3

Related Questions