{"id":12361,"date":"2011-10-18T00:01:00","date_gmt":"2011-10-18T00:01:00","guid":{"rendered":"https:\/\/blogs.technet.microsoft.com\/heyscriptingguy\/2011\/10\/18\/convert-a-semicolon-delimited-file-to-a-csv-file\/"},"modified":"2011-10-18T00:01:00","modified_gmt":"2011-10-18T00:01:00","slug":"convert-a-semicolon-delimited-file-to-a-csv-file","status":"publish","type":"post","link":"https:\/\/devblogs.microsoft.com\/scripting\/convert-a-semicolon-delimited-file-to-a-csv-file\/","title":{"rendered":"Convert a Semicolon-Delimited File to a CSV File"},"content":{"rendered":"

Summary:<\/strong> Learn how to convert easily a semicolon delimited file into a CSV file using Windows PowerShell.<\/span><\/span><\/p>\n

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

\"Hey,Hey, Scripting Guy! I have a problem at work, and I hope you can help. We have an application that writes one log file every four hours. These log files are formatted with semicolons, which makes them a pain to work with. My boss wants me to write a script to open each log file, search for all the semicolons, replace them with commas, save the file to a temporary file, delete the old file, and then rename the temporary file to a new format. He says I can use Windows PowerShell to do this. I have heard that Windows PowerShell is easy to use, but this sounds like a lot of work. In fact, it sounds pretty much like I would have to do the process in VBScript. <\/span><\/p>\n

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

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

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

Microsoft Scripting Guy Ed Wilson here. BL, you are right, that does sound like something one might do in VBScript. I hope your boss does not mind if you take a shortcut. In fact, we do not need to open and read the contents of the semicolon-separated file. Nor do we need to do a search-and-replace operation for the semicolon in order to replace it. We can do this because everything in Windows PowerShell is an object—even the result of an Import-CSV<\/b> cmdlet. <\/span><\/p>\n

To emulate your problem, I used the Export-CSV<\/b> cmdlet to export process information to a text file. In the export, I specified that I wanted to use a semicolon for the delimiter. The command I used is shown here:<\/span><\/p>\n

Get-Process | Export-Csv -Path c:\\fso\\procSemi.txt -Delimiter ‘;’ –notype<\/span><\/p>\n

The semicolon-separated file is shown in the following figure.<\/span><\/p>\n

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

I can use the Import-CSV<\/b> cmdlet to read a semicolon-separated file, but I must specify that the delimiter is a semicolon. If I do not specify the delimiter, the command runs, produces no errors, and does nothing but display the columns I choose. This is shown here:<\/span><\/p>\n

PS C:\\> Import-Csv -Path C:\\fso\\procSemi.txt  | select name, handles<\/span><\/p>\n

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

Name<\/span>                                                   handles<\/span><\/span><\/p>\n

\n

\n

When I use the delimiter<\/i> parameter and specify the semicolon, the command works. The command is shown here:<\/span><\/p>\n

Import-Csv -Path C:\\fso\\procSemi.txt -Delimiter ‘;’  | select name, handles<\/span><\/p>\n

The command and associated output are shown in the following figure.<\/span><\/p>\n

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

To convert from one separator to another separator, it is only necessary to pipe the results from the Import-CSV<\/b> cmdlet to the Export-CSV<\/b> cmdlet. When using the Import-CSV<\/b> cmdlet, specify the semicolon as the delimiter. When using the Export-CSV<\/b> cmdlet, specify the comma as the delimiter. The command to do this is shown here:<\/span><\/p>\n

Import-Csv -Path C:\\fso\\procSemi.txt -Delimiter ‘;’ | Export-Csv -Path c:\\fso\\procCSV.csv -Delimiter ‘,’ -NoType<\/span><\/p>\n

BL, you also had the requirement to delete the original text file, and to rename the newly created CSV file with the original text file’s name. This is easy to do with Windows PowerShell by using the Remove-Item<\/b> cmdlet and the Rename-Item<\/b> cmdlet. The use of these two cmdlets is shown here:<\/span><\/p>\n

Remove-Item -Path C:\\fso\\procSemi.txt<\/span><\/p>\n

Rename-Item -Path C:\\fso\\procCSV.csv -NewName procsemi.txt<\/span><\/p>\n

Because you really want to impress your boss, this can all be accomplished on a single line. It is best to use aliases when attempting to create a single-line command because one-liners can get quite long, and it is best to conserve space. To find out aliases for the commands I used in today’s article, I use the Get-Alias<\/b> cmdlet. But rather than having to type Get-Alias<\/b> four different times, I can type a single command and retrieve all the aliases I used. This command and associated output are shown here: <\/span><\/p>\n

PS C:\\> Get-Alias -Definition Import-Csv, Export-Csv, Remove-Item, Rename-Item<\/span><\/p>\n

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

CommandType<\/span>              Name<\/span>                           Definition<\/span><\/span><\/p>\n

Alias                              ipcsv                             Import-Csv<\/span><\/p>\n

Alias                              epcsv                            Export-Csv<\/span><\/p>\n

Alias                              del                                Remove-Item<\/span><\/p>\n

Alias                              erase                            Remove-Item<\/span><\/p>\n

Alias                              rd                                 Remove-Item<\/span><\/p>\n

Alias                              ri                                  Remove-Item<\/span><\/p>\n

Alias                              rm                                Remove-Item<\/span><\/p>\n

Alias                              rmdir                            Remove-Item<\/span><\/p>\n

Alias                              ren                               Rename-Item<\/span><\/p>\n

Alias                              rni                                Rename-Item<\/span><\/p>\n

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

I deleted my test file, so first I need to delete the newly renamed ProcSemi.txt file, and then recreate it. Here is the command I use from my history:<\/span><\/p>\n

Get-Process | Export-Csv -Path c:\\fso\\procSemi.txt -Delimiter ‘;’ –notype<\/span><\/p>\n

Now I need to recreate the import\/export CSV command using aliases from my list above. The command shown here is a single command that imports the semicolon-separated file, exports it as a comma-separated file, deletes the original semicolon-separated file, and renames the comma-separated file to the name of the original file. This is a single logical command, and I did not include any line continuation commands:<\/span><\/p>\n

ipcsv C:\\fso\\procSemi.txt -del ‘;’ | epcsv c:\\fso\\procCSV.csv -del ‘,’ -not; ri C:\\fso\\procSemi.txt ; rni c:\\fso\\proccsv.csv -new procsemi.txt<\/span><\/p>\n

The newly created and renamed CSV file is shown in the following figure.<\/span><\/p>\n

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

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

BL, that is all there is to converting the format of a delimited file. Join me tomorrow for more Windows PowerShell fun. <\/span><\/p>\n

I invite you to follow me on <\/span>Twitter<\/span><\/a><\/span> and <\/span>Facebook<\/span><\/a><\/span>. If you have any questions, send email to me at <\/span>scripter@microsoft.com<\/span><\/a><\/span>, or post your questions on the <\/span>Official Scripting Guys Forum<\/span><\/a><\/span>. 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 convert easily a semicolon delimited file into a CSV file using Windows PowerShell.   Hey, Scripting Guy! I have a problem at work, and I hope you can help. We have an application that writes one log file every four hours. These log files are formatted with semicolons, which makes them […]<\/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-12361","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 convert easily a semicolon delimited file into a CSV file using Windows PowerShell.   Hey, Scripting Guy! I have a problem at work, and I hope you can help. We have an application that writes one log file every four hours. These log files are formatted with semicolons, which makes them […]<\/p>\n","_links":{"self":[{"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12361","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=12361"}],"version-history":[{"count":0,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/posts\/12361\/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=12361"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/categories?post=12361"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/devblogs.microsoft.com\/scripting\/wp-json\/wp\/v2\/tags?post=12361"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}