Fervent Coder

Coding Towards Utopia...by Rob Reynolds
posts - 278 , comments - 431 , trackbacks - 0

My Links

News


Rob Reynolds

Subscribe to Fervent Coder RSS
Subscribe to Fervent Coder by Email

About Me

I manage several open source projects. Need...
   ...package management for Windows?
   ...automated builds?
   ...database change management (migrations)?
   ...your application to check email?
   ...a monitoring utility?

I also write for



Like what you are reading? Want to buy me a cup of coffee?
PayPal - The safer, easier way to pay online!

Article Categories

Archives

Post Categories

Sites

Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

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:

 CropperCapture5

This is an example of it in Excel:

CropperCapture6

What I want it to look like is this (where everything is appropriately separated into columns):

CropperCapture7

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...}.

CropperCapture8

3. Find [List Separator] and change to |.

CropperCapture9

CropperCapture10

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!

LaunchyExcelHack

Have I mentioned that Launchy is one of my favorite tools?

Print | posted on Wednesday, July 9, 2008 7:19 PM | Filed Under [ Personal ApplicationsToysOther ]

Feedback

Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

I manually set my regional settings under Number to pipe for list separator but when I save my excel file to CSV the separator is still a comma. What am I doing wrong?

I used the registry edit in the previous post as well and verified that the setting is Pipe for list separator but still my CSV files are comma delimitted.
7/31/2008 4:53 PM | Tyger
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

Two things:
(1) You need to make the setting BEFORE you open Excel.

(2) Did you start out with a CSV file and use Excel to open and edit it?

I don't think we checked that ability to be able to take a file with the XLS extension (or a new file you are saving for the first time) and "Save It As..." and pick CSV and have it save with pipes.

What you can do is to take the file and open it in Notepad and do a find replace on ",". Then re-open the file in excel after applying the pipe setting.
7/31/2008 5:27 PM | Robz
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

My answers:
(1) You need to make the setting BEFORE you open Excel.

I made the regional settings change in control panel before I opened Excel. Heck, I even rebooted. This worked for me before long ago but since then we received IBM lenovo PCs with XP and Office 2003 not that it would make a difference.

(2) Did you start out with a CSV file and use Excel to open and edit it?

I started out with an Excel file and used File > Save As and selected CSV

------------------
So hard to believe there isn't some built-in option in Excel to handle this. Address fields and name fields and many text fields will naturally contain commas. Pipe delimitted should be an option.

I'm using a workaround while I wait for a solution. My workaround is to open my Excel file and perform a Find and Replace all commas with ~.
Then save as CSV. Then open in Notepad and replace all commas with PIPE |. Then replace all ~ with comma.
7/31/2008 5:39 PM | Tyger
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

That workaround looks like it would work great.
What you get in bang for the buck after that is to have Excel open that CSV file with pipes and actually separate the items properly.

From what I can tell there is no real good solution to setting the files up (although it sounds like you have a good idea for changing the separators and not your commas). Once you have them ready to go though, the hack works great!
7/31/2008 7:59 PM | Robz
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

Thank you, Rob, for pointing out what I was missing. There is no way to force Excel to output pipe characters in place of commas when doing the initial Save As to CSV format.

Since my intention is to end up with a .TXT file with pipes versus commas, all I had to do was open the CSV file in Excel - which will have all of the data from one row in one cell with pipes. I then save it as .TXT and I'm fine.

Easy.
8/1/2008 10:06 AM | Tyger
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

Actually, saved a file from XLS to CSV and it saved it with pipes. We have Excel 2007. That may make a difference
8/6/2008 9:59 AM | Robz
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

I have entered some data into an excel spreadsheet and I want double quotes embedded inbetween the characters, so that when I save it as a pipe delimited CSV file, it will have the double quotes.
For eg:
"409-56-7008" |"Bennet" |"Abraham" |"415 658-9932" |"6223 Bateman St."

How do I do that?
8/25/2008 10:27 AM | Pushpa
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

It works great
9/8/2008 6:52 AM | Kaush
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

It works very well !! Thanks a lot !!
11/20/2008 5:03 AM | Vaibhav
Gravatar

# re: Windows XP Hack: How To Use a Pipe ("|") Delimiter in Microsoft Excel With CSV Files

Holy cow - it really works. How cool is that??
These EmEssians are crazy.

Merci / Gracias / Danke!
7/30/2009 6:49 PM | Syg
Comments have been closed on this topic.

Powered by: