Specifying delimiter for Excel 2007 export

Solution 1:

From this page:

• Bring up your default options window by clicking Start -> Settings -> Control Panel -> Regional Settings.
• Click the "Customize" button.
• Click the “Number” tab and in the “List Separator” field, replace the current default separator with the one you want to use (in your case, the pipe symbol).
• Click “OK” to save the change and close the window.

Now exporting as CSV in Excel will produce pipe-delimited files.

Solution 2:

Also, assuming no commas in the actual data, you can export to regular .CSV, and then use notepad to Find/Replace all commas with pipes. If you do have commas in your data, then before exporting to .CSV, Find/Replace (within Excel) all of the commas to something that is not used in your data (e.g., the left curly brace, or the tilde). Then, after exporting and find/replacing commas to pipes, you can find/replace that new character back to commas.

Solution 3:

Changing the list separator back and forth is a pain.

Leaving it as | is not acceptable as it screws up a lot of things. Saving as a TAB-separated text file, then using a text editor to globally replace the TABs with |s works well.

To do this with Notepad, highlight a TAB character in the text file, then copy it, then invoke Replace, then paste it into the search for box, then put whatever delimiter you want in the replace with box.

Solution 4:

Why don't you try Libreoffice!

When you open or save a CSV file, it asks what delimiter should be used!