Have you ever wanted to open CSV (Comma Separated Values) files that use something besides comma (",") in Microsoft Excel? Perhaps you have had to use pipe ("|") delimiters or something else before because of some limitation of comma. For whatever reason, it's a pain in the butt to use Excel with CSV files that use some other delimiter besides comma (CSV by name is supposed to use commas). You can still open the file in Excel, but it's all shoved in one column instead of each column getting separated nicely.
This is an example of a pipe-delimited CSV file in Notepad:
This is an example of it in Excel:
What I want it to look like is this (where everything is appropriately separated into columns):
Today at work someone figured out how to do this in a way that doesn't require anything fancy with Excel. In fact, you don't have to make a change to Excel at all. I put this down as a Windows XP hack, but it works for other Windows operating systems. Excel uses an external setting to determine how to parse CSV files:
Excel uses the list separator of the current locale settings...
How To Manually Set Up Windows XP So That Excel Can Open CSV Files That are Not Delimited by Comma
1. Go to the Control Panel and open Regional and Language Options.
2. Click on {Customize...}.
3. Find [List Separator] and change to |.
4. Click {Apply}, then click {OK} and click {OK} again.
5. To change it back to comma, you just follow the steps again and put in "," instead of "|".
NOTE: Excel will need to be closed when you do this.
That's Great, but That's More Than One Step. Is There a Way to Automate That?
I thought you would never ask. We took the time to find the registry setting for it. Now it becomes a one click event to change it back and forth. This is pretty rudimentary, but it is super easy to implement. Someone could develop the concept a little further to a small console application that would take care of the pop ups and make the change for you.
Save the following as ChangeExcelToPipes.reg (or whatever you want, as long as it is a .REG file).
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Control Panel\International]
"sList"="|"
Save the following as ChangeExcelBackToCommas.reg.
Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Control Panel\International]
"sList"=","
This Works Great With Launchy, Too!
Have I mentioned that Launchy is one of my favorite tools?