1 
"ID","Full Name","Age"

"1","Jone Micale","25"

Here a sample from a CSV file that I created, and now I want to remove double quotes from only the ID and Age column value.

I tried different ways but I don't want to create a new file out of it. I just want to update the file with changes using PowerShell v1.

Improve this question
2
  • 2
    Could you please explain the reasoning to do this? I'm sure there is a perfectly good explanation, but the above is a perfectly "valid" csv (though csv isn't terribly standardized, of course, generally wrapping columns in quotation characters is accepted). Is it to get a smaller file or is it for importing the data, and if so why are you having trouble importing the data just because of the quotation characters? Commented Feb 20, 2014 at 10:17
  • The above example was sample of how my csv file looks like. There are number of fields available out of them i just wanted to remove double quotes on specific fields like ID and Age. I don't want to remove double quotes on Full Name field. This csv I am going to use in another DQM activity. Commented Feb 20, 2014 at 11:04

1 Answer 1

Reset to default
2

Export-Csv will always put all fields in double quotes, so you have to remove the undesired quotes the hard way. Something like this might work:

$csv = 'C:\path\to\your.csv'
(Get-Content $csv) -replace '^"(.*?)",(.*?),"(.*?)"$', '$1,$2,$3' |
    Set-Content $csv

Regular expression breakdown:

  • ^ and $ match the beginning and end of a string respectively (Get-Content returns an array with the lines from the file).
  • "(.*?)" matches text between two double quotes and captures the match (without the double quotes) in a group.
  • ,(.*?), matches text between two commas and captures the match (including double quotes) in a group.
  • $1,$2,$3 replaces a matching string with the comma-separated first, second and third group from the match.
Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

Hey thanx Ansgar....great solution. As I am new for PowerShell, can you elaborate this logic for me please. Thanx in Advance
That means how may fields i have in my CSV, I have to insert that much "(.*?)" and $4.... some number in script ?
And in above logic where actually you are saying power-shell to remove double quotes ? Can you please tell me.
Hi Ansgar, this logic not working on big size or content file data. :( I tried it on big size file but it only removes header double quotes. Please tell me solution on it.
@ppatil The above is not a generic solution. In its current form it removes double quotes from the first and last field of a CSV with 3 fields. If your input data looks differently, you'd need to adjust the pattern accordingly.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.