CODEWITHSUNDEEP
powershellcsvexport
DarkLite1
DarkLite1

Reputation: 14695

Export-CSV formatting carriage returns

I'm retrieving information from the active directory but when I try to export it with Export-CSV there are some issues with the carriage returns. It's starting a new line in the CSV-file for each return it finds, while it should actually stay on the current line.

$User = Get-ADUser Bob -Properties *
Display name    : Bob Smith
Notes           : Line 1
                  Line 2
                  Line 3
E-mail          : [email protected]

$User | Export-CSV 'C:\file.csv'
Display name, Notes, E-mail
Bob Smith, Line 1
Line 2
Line 3
[email protected]

How can I have the following output?

$User | Export-CSV 'C:\file.csv'
Display name, Notes, E-mail
Bob Smith, Line 1 Line 2 Line 3, [email protected]

I've tried with encoding, other delimiters, .. Can't really figure this one out.

Solution

$User | Select 'Display Name', @{l='Notes';e={$_.Notes -replace "`n"," "}}, E-mail | Export-Csv 'C:\file.csv' -NoTypeInformation -Delimiter ';'

Upvotes: 4

Views: 15359

Answers (3)

Lavento
Lavento

Reputation: 1

Really old thread but after extensive Googling couldn't still find no answer, but stumbled on the right answer by myself and decided to share. Problem was that output CSV was having a line break.

$resultti = $result.computername -join " " | Out-String

$resultti | Export-Csv -Path $outfile -Delimiter ";" -NoTypeInformation -Encoding UTF8

And out-put CSV looked like "-character always on the next line:

"Name";"Email";"Samaccountname";"Workstations"

"Pete";"[email protected]";"pete";"W7DT1335 W7LT1812

"

"Anne";"[email protected]";"anne";"W7DT13565 W7LT1612

"

"Anton";"[email protected]";"anton";"W7DT1345 W7LT1752

"

This didn't end up well with replace either.

$resultti = $resultti.Replace("`n","x")

--> CSV looked like:

"Name";"Email";"Samaccountname";"Workstations" "Pete";"[email protected]";"pete";"W7DT1335 W7LT1812

x"

"Anne";"[email protected]";"anne";"W7DT13565 W7LT1612

x"

"Anton";"[email protected]";"anton";"W7DT1345 W7LT1752

x"

Solution for me was to stream out-string like this: $resultti = $result.computername -join " " | Out-String -Stream

And CSV looked after that nice.

"Name";"Email";"Samaccountname";"Workstations"

"Pete";"[email protected]";"pete";"W7DT1335 W7LT1812"

"Anne";"[email protected]";"anne";"W7DT13565 W7LT1612"

"Anton";"[email protected]";"anton";"W7DT1345 W7LT1752"

Upvotes: 0

TheMadTechnician
TheMadTechnician

Reputation: 36277

If you are having trouble with inserted CR/LF with a specific field you can always perform a -join on Notes in an expression at a Select operator. Something like:

$User | Select DisplayName, @{l='Notes';e={$_.Notes -join ", "}}, Email | Export-Csv 'C:\files.csv'

That will output the desired output.

If you are having the issue with multiple fields then it gets a bit more complicated as far as how I'd solve it (by reproducing the object as a custom object recreating all the fields, and processing their values with -join or replacing "`n" with ', ')

Upvotes: 3

Patrick
Patrick

Reputation: 2208

The result you get schould be correct because it's seperatet by ",". If you import the csv by "Import-Csv" you get the correct data. The problem is the property "Notes". There are line breaks. Exclude the notes ore replace ``r`n (line break) with "$null".

Upvotes: 0

Related Questions