Learn about Windows PowerShell
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 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.
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 cmdlet.
To emulate your problem, I used the Export-CSV 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:
Get-Process | Export-Csv -Path c:\fso\procSemi.txt -Delimiter ';' –notype
The semicolon-separated file is shown in the following figure.
I can use the Import-CSV 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:
PS C:\> Import-Csv -Path C:\fso\procSemi.txt | select name, handles
When I use the delimiter parameter and specify the semicolon, the command works. The command is shown here:
Import-Csv -Path C:\fso\procSemi.txt -Delimiter ';' | select name, handles
The command and associated output are shown in the following figure.
To convert from one separator to another separator, it is only necessary to pipe the results from the Import-CSV cmdlet to the Export-CSV cmdlet. When using the Import-CSV cmdlet, specify the semicolon as the delimiter. When using the Export-CSV cmdlet, specify the comma as the delimiter. The command to do this is shown here:
Import-Csv -Path C:\fso\procSemi.txt -Delimiter ';' | Export-Csv -Path c:\fso\procCSV.csv -Delimiter ',' -NoType
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 cmdlet and the Rename-Item cmdlet. The use of these two cmdlets is shown here:
Remove-Item -Path C:\fso\procSemi.txt
Rename-Item -Path C:\fso\procCSV.csv -NewName procsemi.txt
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 cmdlet. But rather than having to type Get-Alias four different times, I can type a single command and retrieve all the aliases I used. This command and associated output are shown here:
PS C:\> Get-Alias -Definition Import-Csv, Export-Csv, Remove-Item, Rename-Item
CommandType Name Definition
Alias ipcsv Import-Csv
Alias epcsv Export-Csv
Alias del Remove-Item
Alias erase Remove-Item
Alias rd Remove-Item
Alias ri Remove-Item
Alias rm Remove-Item
Alias rmdir Remove-Item
Alias ren Rename-Item
Alias rni Rename-Item
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:
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:
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
The newly created and renamed CSV file is shown in the following figure.
BL, that is all there is to converting the format of a delimited file. Join me tomorrow for more Windows PowerShell fun.
I invite you to follow me on Twitter and Facebook. If you have any questions, send email to me at email@example.com, or post your questions on the Official Scripting Guys Forum. See you tomorrow. Until then, peace.
Ed Wilson, Microsoft Scripting Guy
Right! ... the CSV Cmdlets do a very good job in converting or ---porting CSV data!
My very first idea, having read through this blog article, was to try this ( which of course is not a good idea :-)
"ipcsv D:\fso\procSemi.txt -del ';' | epcsv D:\fso\procsemi.txt -del ',' -not"
Just read the file and rebuild it with the changed character!
If you know, how the pipe works in this case: read one record and write one record just after it has been read ... it is clear that the second record read might already by corrupted by the first record written.
I ended up with an empty file "D:\fso\procsemi.txt" ... so if you don't know how to create an empty file, use this approach :-))) One thing to mention here is that I even haven't got an error or exception telling me that something went wrong!
Well another thing I did, that sounds more reasonable, is: Store the imported CSV data in a variable and Export it alter on!
"$csv = ipcsv D:\fso\procSemi.txt -del ';'; $csv | epcsv D:\fso\procsemi.txt -del ',' -not"
or you can also omitt the variable use a subexpression in parentheses and just type
"(ipcsv D:\fso\procSemi.txt -del ';') | epcsv D:\fso\procsemi.txt -del ',' -not"
This does a better job but adds additional double quotes for empty elements ( WHY? ) as could be seen here:
So i still wonder if we can change the delimiter without creating a new file, deleting the original file and renaming the new file ... ?!?!?!
Why don't you just open the file with Notepad++. Then select Replace ; with ,.
@Klaus Schulte I like your technique of using the parentheses to force evaluation before passing over the pipeline. It does allow for direct modification of the file without an intervening save / rename / delete operation.
@Kevin I do not even need to use notepad ++, I can do search and replace in notepad.exe itself. However, the email from the reader who wrote to Scripter@Microsoft.Com was that they had a file that is created every 4 hours and therefore they needed an automated solution. I cannot easily automate notepad.exe