{"id":12211,"date":"2011-11-02T00:01:00","date_gmt":"2011-11-02T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/11\/02\/remove-unwanted-quotation-marks-from-csv-files-by-using-powershell\/"},"modified":"2011-11-02T00:01:00","modified_gmt":"2011-11-02T00:01:00","slug":"remove-unwanted-quotation-marks-from-csv-files-by-using-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/remove-unwanted-quotation-marks-from-csv-files-by-using-powershell\/","title":{"rendered":"Remove Unwanted Quotation Marks from CSV Files by Using PowerShell"},"content":{"rendered":"

Summary: <\/strong>Learn how to remove unwanted quotation marks from a CSV file by using Windows PowerShell.<\/span><\/span><\/span><\/p>\n

 <\/span><\/span><\/span><\/p>\n

Microsoft Scripting Guy Ed Wilson here. The fall is rapidly falling down here in Charlotte, North Carolina, in the United States. It is cooling off here, and is around 60 degrees Fahrenheit (15.5 degrees Celsius, according to my <\/span>conversion module<\/span><\/a>). The neighbors have all been wracked with anguish over whether to tough it out for a few more days, or to go ahead and turn on the heater. It is, evidently, such a convoluted decision that they have made numerous Facebook postings about it. But I guess I would rather read about anguished decisions about to turn on the heater or not, than to read about the latest nonferrous material that their pet consumed.  <\/span><\/span><\/span><\/span><\/p>\n

The Internet is cool. For example, I really enjoy comments that are posted on the Hey, Scripting Guy! Blog. In fact, I have subscribed to a RSS feed that alerts me any time that a new comment is made, regardless of how old the article may be. Just this morning, I replied to two comments from readers on postings that were more than five years old; that’s <\/span>35 in doggy years<\/span><\/a>, and more like 50 in Internet years. <\/span><\/span><\/span><\/span><\/p>\n

Anyway, the other day, I wrote a pretty cool article named, <\/span>Use PowerShell to Append CSV files Easily<\/span><\/a>.In the comments about that article, Jeffrey S. Patton from Kansas (yes <\/span>Toto, he is from Kansas<\/span><\/a>) posted a asked why I used Notepad <\/i>to remove commas instead of programmatically replacing the commas in the output with Windows PowerShell. I answered that I did it because opening the file in Notepad, clicking Edit<\/b>, and then clicking Replace<\/b> was faster and easier than writing the extra code to replace the quotation marks. In a one-off scenario, that is probably true. But what if this is something that needs to be scheduled on a regular basis? If it is an automation scenario, opening the file in Notepad <\/i>is off the table; as cool as Notepad <\/i>is, it does not have an automation interface, so it is not a scriptable solution. <\/span><\/span><\/span><\/span><\/p>\n

What exactly is the problem? When using the Export-CSV<\/b> cmdlet, it automatically adds quotation marks around everything. In some cases, when reading this file, the quotation marks could be interpreted as part of the data, which of course would be a problem. It is rare, but it most certainly could happen. In fact, this very problem is one reason I quit using Microsoft Excel to manipulate CSV files: it has the same “feature.” This happens because if you have a space in a column, some programs are not smart enough to respect the space between commas. Therefore, to help prevent an error with a column that has a space in it, quotation marks are added everywhere. The output file produced <\/span>from <\/span>our command from yesterday<\/span><\/a> (shown here) is shown in the figure following this code:<\/span><\/span><\/span><\/p>\n

dir c:\\fso -Filter *.csv | ? {$_.basename -like ‘users?’} | Import-Csv | sort lname,fname | Export-Csv -Path c:\\fso\\UsersConsolidated.csv –NoTypeInformation<\/span><\/span><\/span><\/p>\n

\"Image<\/a><\/span><\/span><\/span><\/p>\n

Jeffrey was absolutely correct when he said I could use replace<\/b> <\/i>to remove the quotation marks from the output file. It is actually pretty simple. <\/span><\/span><\/span><\/p>\n

Here are the steps I need to perform:<\/span><\/span><\/span><\/p>\n

    \n
  1. Use the Get-Content<\/b> cmdlet to read the text of the usersconsolidated.csv <\/i>file. <\/span><\/span><\/span><\/li>\n
  2. Use the Foreach-Object<\/b> cmdlet (%<\/b> is an alias) to read each line as it comes from the file.<\/span><\/span><\/span><\/li>\n
  3. Inside the script block for the Foreach-Object<\/b> command, use the $_ <\/b>automatic variable to reference the current line and the replace<\/b> <\/i>operator to replace a quotation mark with nothing.<\/span><\/span><\/span><\/li>\n
  4. Use the Out-File<\/b> cmdlet to overwrite the existing usersconsolidated.csv <\/i>file with the newly changed content that no longer contains the quotation marks.<\/span><\/span><\/span><\/li>\n
  5. Use the force <\/i>switched parameter to tell the Out-File<\/b> cmdlet to overwrite existing content.<\/span><\/span><\/span><\/li>\n
  6. Use the encoding <\/i>parameter to specify ASCII <\/i>encoding to maintain compatibility with legacy applications.<\/span><\/span><\/span><\/li>\n<\/ol>\n

    The command line I use is much simpler than the six steps above would make it seem. Here is the command:<\/span><\/span><\/span><\/p>\n

    (Get-Content C:\\fso\\UsersConsolidated.csv) | % {$_ -replace ‘”‘, “”} | out-file -FilePath C:\\fso\\UsersConsolidated.csv -Force -Encoding ascii<\/span><\/span><\/span><\/p>\n

    The preceding command is a single-line command that has wrapped. No line continuation marks are used in the command. The parentheses around the Get-Content<\/b> cmdlet are required (otherwise, it creates a blank file). I can actually shorten this command by searching for additional aliases. I use the Get-Alias<\/b> cmdlet to search for aliases. The cool thing is that it will accept an array for input, and therefore I can look for aliases for both Get-Content<\/b> and Out-File<\/b> at the same time. Here is the command:<\/span><\/span><\/span><\/p>\n

    Get-Alias -Definition get-content, out-file<\/span><\/span><\/span><\/p>\n

    The bad thing about the Get-Alias<\/b> cmdlet is that it returns an error when no match appears. This behavior is shown in the following figure.<\/span><\/span><\/span><\/p>\n

    \"Image<\/a><\/span><\/span><\/span><\/p>\n

    The shortened version of the command uses the gc<\/b> <\/i>alias for the Get-Content<\/b> cmdlet. It also uses positional parameters and partial parameter names. <\/span><\/span><\/span><\/p>\n

    (gc C:\\fso\\UsersConsolidated.csv) | % {$_ -replace ‘”‘, “”} | out-file C:\\fso\\UsersConsolidated.csv -Fo -En ascii<\/span><\/span><\/span><\/p>\n

    As shown in the following figure, there are no more quotation marks in the file. <\/span><\/span><\/span><\/p>\n

    <\/span><\/span><\/span>\"Image<\/a><\/p>\n

    If you want to do this all in a single command, it is easier to switch to using the ConvertTo-CSV<\/b> cmdlet instead of the Export-CSV<\/b> cmdlet. The reason is that ConvertTo-CSV<\/b> converts to CSV format, but does not export to a file. This allows time to replace the quotation marks prior to writing to file. The revised command is shown here:<\/span><\/span><\/span><\/p>\n

    dir c:\\fso -Filter *.csv | ? {$_.basename -like ‘users?’} | Import-Csv |  sort lname,fname | convertto-csv -NoTypeInformation | % { $_ -replace ‘”‘, “”} | out-file c:\\fso\\usersconsolidated.csv -fo -en ascii<\/span><\/span><\/span><\/p>\n

    There is no alias for the ConvertTo-CSV<\/b> cmdlet, but there is an alias for Import-CSV<\/b>, which is ipcsv<\/b>. I can also use ls<\/b> as an alias for Get-ChildItem<\/b>, instead of the lengthier dir<\/b> alias. I can also shorten filter<\/b> <\/i>to fi<\/i><\/b> <\/i>(I cannot use the single letter f <\/b>because there is also a parameter force <\/i>defined for the Get-ChildItem<\/b> cmdlet). Here is the shortened command:<\/span><\/span><\/span><\/p>\n

    ls c:\\fso -Fi *.csv | ? {$_.basename -like ‘users?’} | ipcsv | sort lname,fname | convertto-csv -NoTypeInformation | % { $_ -replace ‘”‘, “”} | out-file c:\\fso\\usersconsolidated.csv -fo -en ascii<\/span><\/span><\/span><\/p>\n

    Well, that is about all there is to removing quotation marks from a CSV file. Join me tomorrow for more Windows PowerShell fun. <\/span><\/span><\/span><\/p>\n

     <\/span><\/p>\n

    I invite you to follow me on <\/span>Twitter<\/span><\/a> and <\/span>Facebook<\/span><\/a>. If you have any questions, send email to me at <\/span>scripter@microsoft.com<\/span><\/a>, or post your questions on the <\/span>Official Scripting Guys Forum<\/span><\/a>. See you tomorrow. Until then, peace.<\/span><\/span><\/span><\/span><\/p>\n<\/p>\n

    Ed Wilson, Microsoft Scripting Guy<\/span><\/b><\/span><\/p>\n

    <\/span><\/span><\/span> <\/p>\n

     <\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

    Summary: Learn how to remove unwanted quotation marks from a CSV file by using Windows PowerShell.   Microsoft Scripting Guy Ed Wilson here. The fall is rapidly falling down here in Charlotte, North Carolina, in the United States. It is cooling off here, and is around 60 degrees Fahrenheit (15.5 degrees Celsius, according to my […]<\/p>\n","protected":false},"author":596,"featured_media":87096,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[1],"tags":[169,3,4,45],"class_list":["post-12211","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-scripting","tag-csv-and-other-delimited-files","tag-scripting-guy","tag-scripting-techniques","tag-windows-powershell"],"acf":[],"blog_post_summary":"

    Summary: Learn how to remove unwanted quotation marks from a CSV file by using Windows PowerShell.   Microsoft Scripting Guy Ed Wilson here. The fall is rapidly falling down here in Charlotte, North Carolina, in the United States. It is cooling off here, and is around 60 degrees Fahrenheit (15.5 degrees Celsius, according to my […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12211","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/users\/596"}],"replies":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/comments?post=12211"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12211\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media\/87096"}],"wp:attachment":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/media?parent=12211"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=12211"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=12211"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}