{"id":12201,"date":"2011-11-03T00:01:00","date_gmt":"2011-11-03T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/11\/03\/search-for-and-replace-words-in-a-csv-file-by-using-powershell\/"},"modified":"2011-11-03T00:01:00","modified_gmt":"2011-11-03T00:01:00","slug":"search-for-and-replace-words-in-a-csv-file-by-using-powershell","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/search-for-and-replace-words-in-a-csv-file-by-using-powershell\/","title":{"rendered":"Search for and Replace Words in a CSV File by Using PowerShell"},"content":{"rendered":"

Summary:<\/strong> Learn how to search for and replace words in a CSV file by using Windows PowerShell.<\/span><\/span><\/p>\n

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

\"Hey,<\/span><\/span><\/span><\/span>Hey, Scripting Guy! I have a comma-separated value (CSV) file that contains user names, user groups, and organizational unit (OU) assignments. The problem is that we are moving one of our office locations, and I need to quickly change the old location name to the new location name. I have been reading all of your articles this week, and I think I should be able to use Import-CSV<\/b> to read the CSV file, and use a foreach<\/b> loop to evaluate each row in the CSV file. <\/span><\/p>\n

Inside there, I should be able to use an if<\/b> <\/i>statement to see if there is a match with the old name. If there is, I want to change it to the new name, and then export the CSV data to a new file. The problem is that this is not really working the way I want it to. For some reason, I appear to be picking up some kind of extra crap that is added by Windows PowerShell. I have spent all afternoon on something that should really be a simple one-liner. Help please. If I have to write a script to do this, I may as well go back to using VBScript. At least I have plenty of years experience using that language. <\/span><\/p>\n

—GM<\/span><\/p>\n

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

\"Hey,<\/span><\/span><\/span>Hello GM, <\/span><\/p>\n

Microsoft Scripting Guy Ed Wilson here. One thing that is rather interesting about Windows PowerShell is that some things are really super easy. Other things appear to be difficult until you find the “correct” approach. One of the things I noticed in the <\/span>2011 Scripting Games<\/span><\/a> is that some people work way too hard on their solutions. The other cool thing about Windows PowerShell is that in the end, if something runs without errors and something does what you need to be done, it is a solution. If I can write a bit of code in five minutes that is ugly but works, it is probably better than something that is elegant but takes me five hours to write. Don’t get too hung up on trying to craft the ultimate one-liner when you have a job to do, and that job does not entail crafting the ultimate one-liner.<\/span><\/span><\/p>\n

On the other hand, there is something to be said for thinking about how to do things more effectively in Windows PowerShell. GM, I am imagining that your approach was to do something like this:<\/span><\/p>\n

import-csv C:\\fso\\usersconsolidated.csv | foreach { If($_.ou -match “Atlanta”) {$_.OU -replace “Atlanta”,”Cobb”}} | export-csv c:\\myfolder\\myfile.csv<\/span><\/p>\n

This command does not work the way you want it to work … in fact, it does not really work very well at all because it does not produce the expected results. You are getting hung up with wrestling with the pipeline, getting confused with the Foreach-Object<\/b> cmdlet (that is interrupting your pipeline), and messing about with the Export-CSV<\/b> command that is not producing a CSV file at all. <\/span><\/p>\n

While it is true that Windows PowerShell has some pretty cool cmdlets for working with CSV and for working with files, it is also true that at times, I want to be able to read the entire contents of a file into memory, and work on it at one time. The Get-Content<\/b> cmdlet does not permit this; it basically creates an array of lines of text, which is great on most occasions. <\/span><\/p>\n

To easily read the contents of a file all at once, I use the readalltext<\/b> <\/i>static method from <\/span>the File <\/i>class<\/span><\/a> from the .NET Framework. The File<\/b> <\/i>class resides in the <\/span>System.IO .NET Framework namespace<\/span><\/a>. When using this class, I use square brackets around the class and namespace name. I can leave off the word system <\/i>if I want to, or I can type it if I wish—it does not matter. If the word system <\/i>is not present, Windows PowerShell will assume that the namespace contains the word system <\/i>in it and will automatically use that when attempting to find the class. The .NET Framework namespaces are similar to the namespaces used in WMI because they are used to group related classes together for ease of reference. The difference is that the .NET Framework namespaces are a bit more granular. Therefore, if I am interested in working with files, directories, paths, file information, and other related items, I would go to the System.IO .NET Framework namespace and look around to see what is available. <\/span><\/span><\/p>\n

After I find the File<\/b> <\/i>class, I can look it up on MSDN to see which methods it includes. The easiest ones to use are the static members (methods, properties, and events taken together become members) because using Windows PowerShell, all I need to do is to put the namespace\/class name combination inside square brackets, use two colons, and the name of the method. And it works. Many times, the things a class provides are available somewhere else. For example, the File<\/b> <\/i>.NET Framework class provides a <\/span>static method called exists<\/span><\/a>. <\/i>This method returns a Boolean value (true or false) that lets me know if a file exists or not. To use this method, I provide a string to the method. This technique is shown here:<\/span><\/span><\/p>\n

PS C:\\> [io.file]::exists(“C:\\fso\\UserGroupNames.txt”)<\/span><\/p>\n

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

PS C:\\> [io.file]::exists(“C:\\fso\\missingfile.xxx”)<\/span><\/p>\n

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

I can accomplish the same thing by using the Test-Path cmdlet. This appears here.<\/span><\/p>\n

PS C:\\> Test-Path C:\\fso\\UserGroupNames.txt<\/span><\/p>\n

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

PS C:\\> Test-Path C:\\fso\\missingfile.xxx<\/span><\/p>\n

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

It is always preferable to use a native Windows PowerShell cmdlet to do something, rather than resorting to .NET Framework, COM, WMI, ADSI, or some other technology—unless you have a compelling reason for doing otherwise. <\/span><\/p>\n

A static method called readalltext<\/b> <\/i>is available from the file<\/b> <\/i>class, and it can be used in two ways. The first way is to <\/span>supply a string that points to the path<\/span><\/a> to the file to open. The second way is to specify the encoding of the file. Most of the time when reading a file, the encoding is not required because the method <\/span>attempts to detect automatically the encoding of a file based on the presence of byte order marks. Encoding formats UTF-8 and UTF-32 (both big endian and little endian) can be detected. The result of using the readalltext<\/b> <\/i>method is that I get back a bunch of text in the form of a String<\/b> class. <\/span>The String <\/i>class<\/span><\/span><\/a> resides in the System .NET Framework namespace, and it contains a large number of methods. One of those methods is the replace<\/b> <\/i>method. I therefore add the replace<\/b> <\/i>method to the end of the readalltext<\/b> <\/i>method. The command to read all of the text in a file (CSV file) and to replace every instance of the word atlanta <\/i>with the word cobb <\/i>is shown here:<\/span><\/span><\/p>\n

[io.file]::readalltext(“C:\\fso\\usersconsolidated.csv”).replace(“atlanta”,”cobb”)<\/span><\/p>\n

The command to read a text file and replace words, and its associated output are shown in the following figure.<\/span><\/p>\n

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

To write this to a text file is simple. I can use the Out-File<\/b> cmdlet as shown here:<\/span><\/p>\n

[io.file]::readalltext(“C:\\fso\\usersconsolidated.csv”).replace(“Atlanta”,”Cobb”) | Out-File c:\\fso\\replacedAtlanta.csv -Encoding ascii –Force<\/span><\/p>\n

The above is a single-line command that wraps in my Word document. I have not added any line continuation to the command. Keep in mind this technique is case sensitive. It will replace Atlanta<\/i>, but not atlanta<\/i>. The newly created text file is shown in the following figure. <\/span><\/p>\n

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

GM, that is all there is to replacing values in a CSV file. Join me tomorrow for more exciting Windows PowerShell fun. <\/span>TTFN<\/span><\/a>.<\/span><\/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><\/p>\n<\/p>\n

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

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

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

Summary: Learn how to search for and replace words in a CSV file by using Windows PowerShell.   Hey, Scripting Guy! I have a comma-separated value (CSV) file that contains user names, user groups, and organizational unit (OU) assignments. The problem is that we are moving one of our office locations, and I need to […]<\/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,14,45],"class_list":["post-12201","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-text-files","tag-windows-powershell"],"acf":[],"blog_post_summary":"

Summary: Learn how to search for and replace words in a CSV file by using Windows PowerShell.   Hey, Scripting Guy! I have a comma-separated value (CSV) file that contains user names, user groups, and organizational unit (OU) assignments. The problem is that we are moving one of our office locations, and I need to […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12201","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=12201"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12201\/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=12201"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=12201"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=12201"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}