• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 873
  • Last Modified:

Saving Excel with Pipe Separator - Not Changing Windows List Separator

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
CynSzcz
Asked:
CynSzcz
1 Solution
 
Matthew OzogCommented:
CSV stands for Comma Seperated Values.  Try saving with a txt extension.
0
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
Rob HensonFinance AnalystCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Rob HensonFinance AnalystCommented:
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
 
CynSzczAuthor Commented:
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
 
QlemoBatchelor, Developer and EE Topic AdvisorCommented:
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
 
Glenn RayExcel VBA DeveloperCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now