How can i get "I" (pipe) instead of "," (comma) in CSV file created by excel?

Question

How can i get "I" (pipe) instead of "," (comma) in CSV file created by excel? - I am trying to create CSV file from existing excel file i want "|" instead of "," since comma is used in many fields. Kindly help. Thanks in advance

Answer 1

There may be an easier way than this but I don't know it. Try accessing the Windows Character Map:



start > programs > accessories > system tools > character map



This should give you the pipe symbol on the first page. If you think you'll use the Character Map often enough to make it worthwhile, you can create a shortcut by right clicking on the program (when you get to it from System Tools.) As I said, there may be an easier way, especially if you're trying to replace a lot of commas.



Hope this helps.

Answer 2

Forgive me for being pedantic, but CSV is Comma Separated Values, so the commas are required.



Putting that aside, there's no easy, one-step way to create text files from (recent versions of) Excel with separators other than comma or tab. You can specify a custom separator when importing, but not exporting. Best approach depends on whether you need quotes around the values or not.



One way would be to create a new column or sheet, and use a formula to concatenate all the text. Then save-as the result, choosing Unicode Text, MS-DOS Text, etc. E.g '=B1 & "|" & C1'. Then file–save-as the result, choosing Unicode Text, MS-DOS Text, etc. It's a kludge, but it works. The CONCATENATE function does the same thing as '&' but may save some typing.



You could also copy the data and paste it into a word processor and manipulate it there. For Word there's two options:

1. Paste Special as unformatted text, then find-and-replace to change the Tab characters into pipes.

2. Paste (as Word table), then use Table to Text ("Table" menu — "Convert" submenu in Word 2003).



If you need the quotes around the fields,

e.g.

"value1"|"value2"

not

value1|value2

then your best bet is to save as CSV, open the CSV file in a text editor or word processor, find-and-change the commas into pipes.



[Edit:

Doh! my bad. I just tried it and Excel 2003 only puts quotes around cells that contain a comma. (Other tools I've used put quotes on everything, sorry for confusion.) So if you really *need* the quotes on everything, it gets messy.

If your data contains commas within cells, then global replace will mess those up. (But not if you do copy-paste rather than save-as-CSV)

If your data contains pipe characters within cells, and you only want quotes where the pipes are and not on everything, then it gets very messy.





Final answer: easiest way is to paste into Word and do Table to Text. (If you only need one character between the fields and if there's no pipes in your original data.)

]

Answer 3

wat is that?



just open the file in an editor and use replace-All commas into pipes. whats the big deal to think about?