1

Hi, I want to convert a .xls file to a pipe delimited(|) or ~ delimited. I can do this by saving the file to .csv and then replacing the , by | or ~. But there are values with commas in the .xls file, which may cause problem while reading the file column by column. Can anyone tell me how can I convert the file to .txt with | delimiter.

2

For Creating Pipe (|) demited file from Excel Follow the Steps…
1)Go to Control Panel and Change the List separated comma (,) to Pipe (|).
2)Apply and Ok.
3)Open Excel File that to be create as Pipe (|) Delimited.
4)SaveAs Comma Separated File (.CSV) Format.
5)Open that CSV file in Notepad and replace Comma to Nothing and Save.
6)Change the List Separator from Pipe (|) to Comma (,) from Control Panel.

Satyendra Chauhan

3

I have been using this free excel macro for years
http://www.smokeylake.com/excel/text_write_program.htm
The Text Write Program is a Microsoft Excel workbook with a macro
program that writes any sheet of any open workbook to a text file with
lots of options.
Good luck

4

All you need to do is.

Go to the Start Button

Go to Settings

Go to Control Panel

Go to Regional and Language Options

Press Customize button

Go o List Separator

Replace the , with a |

Now when you save a excel file it will be pipe delimited instead of a comma. You still save it as a .CSV file.

5

I have been using this free excel macro for years:

http://www.smokeylake.com/excel/text_write_program.htm

The Text Write Program is a Microsoft Excel workbook with a macro
program that writes any sheet of any open workbook to a text file with
lots of options.

Good luck

George Lewycky

6

What about saving it as a tab delmited file and then replacing the tabs with pipes?

7

Thanks David. :slight_smile:
I am following the same.

8

hey very good idea… bit time consuming but a way out :slight_smile:

9

Knee jerk reaction . . . alter all , to ~ or other unused special
character, save as a csv file, alter all , to | then alter all ~ to ,
and save as .TXT

10

Create a column to do all the concatenation - like, assuming you have data in columns A, B and C then in column D, you might put:

=a2&char(124)&b2&char(124)&c2

The char(124) is the ascii code for the pipe character.

Copy the formula down a column. Then copy the entire block to a separate sheet. Do a save as on the separate sheet as a csv file which you can later change the file extension from csv to txt.

11

You should use ASAP utilities. It has about 200 predefined macros listed including concatenating Excel files with Pipe, Comma, whatever with the click of a button. Saves me an average of 2 to 3 hours a week. Download the trial for like 30 days or so. You will buy it before the end of your trial.