Remove newline in CSV field

Question

Remove newline in CSV field

ram302

SSCommitted

Points: 1618
More actions
June 11, 2016 at 5:04 am

#319380

Hi all,
I'm trying to replace text in a CSV.
The CSV line looks something like this:
"Field One","Field Two","Field
Three","Field Four"
So I tried something like:
(Get-Content $csvfile) -replace "Field`n", "Field" | Set-Content $csvfile
But this didn't work.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16167 More actions · Answer 1

ram302 (6/11/2016)
Hi all,
I'm trying to replace text in a CSV.
The CSV line looks something like this:
"Field One","Field Two","Field
Three","Field Four"
So I tried something like:
(Get-Content $csvfile) -replace "Field`n", "Field" | Set-Content $csvfile
But this didn't work.

It wouldn't. get-content will not process csv files as such.

look into using import-csv and then parsing each element and do the replace you need.

depending on what you are trying to accomplish the following from Chrissy LeMaire may also be of interest to you - and will at least give you some options to process CSV files - https://blog.netnerds.net/2015/01/powershell-high-performance-techniques-for-importing-csv-to-sql-server/

Note that the above will only work if your file is a real CSV according to standard - if it is a badly formed CSV (just having a , as a delimiter does not make it a CSV file) you will have other issues.

And I have to query why you wish to remove the newline - in many cases that I have seen people wanted to remove them only because the file was viewed by people before loading to another system that was expecting those to be supplied.

Shawn Melton SSC-Insane Points: 24685 More actions · Answer 2

frederico_fonseca (6/11/2016)
And I have to query why you wish to remove the newline - in many cases that I have seen people wanted to remove them only because the file was viewed by people before loading to another system that was expecting those to be supplied.

This actually depends on what is going to absorb the data from the CSV file. In some situations having a value with a new line will cause errors. In this case if you viewed the data in PowerShell using Import-Csv it actually is pretty easy to find the field with the problem.

This works because PowerShell treats what shows in double quotes as a full value, other products may not be so nice.

Shawn Melton
Twitter: @wsmelton
Blog: wsmelton.github.com
Github: wsmelton

ram302 SSCommitted Points: 1618 More actions · Answer 3

Good morning Guys,

Thanks for your input. Federico's suggestion for using import-csv is what I ended up using. However, that adds a first line to the file that reads:

#TYPE System.Management.Automation.PSCustomObject

So in addition to doing the import-csv and export-csv, I had to add two additional lines:

(Get-Content "test.csv") -replace "#TYPE System.Management.Automation.PSCustomObject", "" | Set-Content "test.csv"

and

(Get-Content "test.csv") | ? { $_.trim() -ne "" } | Set-Content "test.csv"

Maybe you can suggest something here, I tried to replace the text adding `n and `r`n in the string, but this was to no avail; doesn't seem to find the string with the newline characters.

frederico_fonseca SSCoach Points: 16167 More actions · Answer 4

sample code to replace carriage returns and linefeeds from a column

generic and will go through all columns - test conditions for property.name can allow you to restrict the replacement just ot the columns you wish

$content = Import-Csv "c:\temp\x.txt"

$content| Foreach-Object {

foreach ($property in $_.PSObject.Properties)

{

$property.Value = ($property.Value).replace("`r","").replace("`n","")

}

$content|Export-Csv -Path "c:\temp\x1.txt" -NoTypeInformation

-NoTypeInformation above removes the "#TYPE System.Management.Automation.PSCustomObject" line from being added to the output.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 5

By any chance, is the ultimate goal to import the data into a table in SQL Server?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

ram302 SSCommitted Points: 1618 More actions · Answer 6

Jeff Moden (6/13/2016)
By any chance, is the ultimate goal to import the data into a table in SQL Server?

No. I'll be converting the CSV to XLSX for delivering reports.