Solved

Saving Excel with Pipe Separator - Not Changing Windows List Separator

Posted on 2014-04-29
7
516 Views
Last Modified: 2014-10-28
I need to import a file into Excel that is a pipe delimited file.  I can open the file but when I go to save it, it must be a csv file which saves with commas, not pipes.  It would not be a useful process to change the List Separator from comma to pipes within the advanced settings of the control panel in Windows.  Is there a way to save from within Excel so we can change the comma to pipe?
0
Comment
Question by:CynSzcz
7 Comments
 
LVL 1

Expert Comment

by:Matthew Ozog
ID: 40030945
CSV stands for Comma Seperated Values.  Try saving with a txt extension.
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40031005
As it seems, the only way is either to
*  (programmatically) change the regional settings, save to CSV, and reset the regional settings
* use VBA file I/O to generate a text file
* use PowerShell (or other Automation capable languages)
0
 
LVL 32

Expert Comment

by:Rob Henson
ID: 40031819
When you open the pipe delimited file in excel I would expect the pipes to disappear as Excel will recognise them as a delimiter and create a new column instead. Is this the case here?

If so, then just saving as a CSV format will create a comma separated file. Does your file contain commas that you will need to keep, eg  a filed such as "Last Name, First Name"?

Thanks
Rob H
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 32

Expert Comment

by:Rob Henson
ID: 40031823
Thinking about it, pipe isn't one of the standard delimiters but when doing doing the text import you can force it to recognise the the pipe by selecting the "Other" option and typing a pipe in the entry box against the "Other" option.

Thanks
Rob
0
 

Author Comment

by:CynSzcz
ID: 40032043
I am bringing a file into Excel that originally contains the pipe deliminator.  It is an electronic invoice that must be uploaded to a billing audit house and has to be in a LEDES98 format.  My problem is I need to revise the file after I create it but before I upload it to the website.  After I revise it, I need to save it and NOT lose the pipes. It can be saved as a txt file but I was wondering if there was a way to "save with the pipes" without changing the List Separator in Windows from the comma to the pipe each time (and then having to remember to go back and change it back).
0
 
LVL 68

Expert Comment

by:Qlemo
ID: 40032285
I wonder if you considered to read http:#a40031005 ?
There are a lot of options, but all require programming if you don't want to go thru the manual process.
0
 
LVL 27

Accepted Solution

by:
Glenn Ray earned 500 total points
ID: 40060257
I encountered this same problem last year and our solution was quite simple:

Save the Excel data in CSV format.
Open the file using a text editor (Notepad will work).
Search and replace commas with pipes.
Save the file.

The small amount of time spent doing that is far less than any time invested in creating an automated solution to write out the data file.

Regards,
Glenn
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now