Convert Excel file data into PIPE delimited file

Previously when I had Excel 2002 , i was able to save as pipe ( | ) delimited file. Now since we were upgraded to Excel 2007 I am unable to save the excel file to pipe delimited anymore, i can only save as comma separated file (csv). Does anyone know how to create a pipe delimited file from excel 2007 directly ?
vemi007Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ken ButtersCommented:
See this site:
http://www.excelforum.com/excel-general/571895-save-csv-file-as-pipe-delimited-text-file.html

you can go to
1 ) Control Panel
2) Select Regional and Language Options
3) click on Customize
4) change List Separator from comma to pipe

Then you can save file as CSV in excel, and it will use pipe as the delimiter.
vemi007Author Commented:
I went ahead and changed the list separator to "|" , I then opened a xlsx file and saved it as a csv file. I still see commas as delimiter not the pipe. Do I have to reboot my PC ?
RichardSchollarCommented:
*** No Points if applicable ***

You would definitely need to restart Excel - not sure if you had done that or not?
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Ken ButtersCommented:
You may very well have to reboot your pc.... because this is a windows change... not just an excel change.
vemi007Author Commented:
I have restarted excel , logged off my PC and finally rebooted the PC. None of it is helping, when i open the xlsx file in excel 2007 and then save as csv file, it is still saving it using commas. Tried multiple times with same result.
Ken ButtersCommented:
It seems to work fine for me. Just to go over the steps to make sure we are doing the same thing....

First :
go back into regional settings to make sure that your delmiter has been saved correctly as a pipe.  ---- "|"
Second:
1) Open your original excel file
2) Select "Save As"
3) Select "Other Formats"
4) Now you should be in the "Save As" dialog box.   Here in the bottom of the window... in the "Save as Type" dropdown.... select "CSV (Comma delimited)(*.CSV)
5) Hit the save Button
6) Open the .csv file with wordpad or notepad (or your favorite editor) to view the results.  Your file should be delimited with pipes instead of commas.
 
Ken ButtersCommented:
Additional note.... in order to switch back and forth between a comma and a pipe... I did not have to reboot my machine.  ----I am running windows xp.
vemi007Author Commented:
yes I exactly followed those steps, please check the screenshots as well.. it is not working for me here .. very weird
Confidential information in images has been redacted - thermoduric
http://www.experts-exchange.com/Q_26242426.html

Open in new window

scr1.jpg
scr3.jpg
scr2.jpg
Ken ButtersCommented:
This may be a long shot... but after you have your regional settings changed to a pipe...  can you check the following registry setting?

Verify that sList had changed to "|"

HKEY_CURRENT_USER\Control Panel\International
"sList"="|"
vemi007Author Commented:
yes it is.
scr.jpg
Ken ButtersCommented:
One more thing to check.... in excel...

Select excel options / Advanced
then in the first section labeled : "Editing Options"...
I have "Use system separators checked"....


systemSepar.jpg
vemi007Author Commented:
Yes I have that checked too.
Ken ButtersCommented:
Sorry.... everything I researched... seemed to indicate that should work... I'm afraid I'm out of ideas...
vemi007Author Commented:
its ok , thanks anyway..appreciate your time

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.