How do I fix this problem with special characters in a *.csv file?

Never mind. I just figured out the problem: I'd saved *.csv file in "CSV (MS-DOS) (*.csv)" format. I just now noticed that, in Excel, there is another format for saving a csv file: CSV UTF-8 (Comma delimited) (*.csv). Using that format fixed the problem.

I use notepad or other text based editors (pe2, kedit, slick...) for the work.
MS Excel is still good for viewing though.

You had me wondering. After 15 years of creating .csv files using Notepad to create the .txt file then merely renaming the .txt file to .csv I wondered if I had missed something. Nah. Glad you figured it out. Apparently using Notepad eliminates any of the mumbo-jumbo you mentioned. Here's a sample line from one of my POI files created using Notepad:

-81.006280,41.740424,Debonne Vineyards,7840 Doty Rd•Madison OH 44057•440-466-3485

Phil

We may be among the few still living in the late 1900s, but I also use Notepad to create my POI files.

Yeah, that plus Google Earth go a long way.

Phil

I use Excel and the only problems I've had are the ones that I created with typos. I also us "CSV (MS-Dos)".

https://pteo.paranoiaworks.mobi/diacriticsremover/

strips characters less competent devices cannot display

https://pteo.paranoiaworks.mobi/diacriticsremover/

posted the same thing twice, sorry

OK, I missed that lesson.......What does • represent?
Why is it used?

It just makes a nice special character to use in place of commas (which of course are anathema to Excel if you're creating a POI file).
FWIW, I maintain a Word document of special characters that I can use when required. For example, here's a small sample:

‼ (Double exclamation mark) ™ ( Trade mark) ~ (tilde)
• (Bullet) ® (Registered sign) ℠ (Service mark)
↑ (Up arrow) ↓ (Down arrow)

If I need one I just do a copy/paste.

Phil

Many of your samples are available via keyboard shortcuts, at least for Word. And they will be superscripts as they should be.

To insert the copyright symbol, press Ctrl+Alt+C.
To insert the trademark symbol, press Ctrl+Alt+T.
To insert the registered trademark symbol, press Ctrl+Alt+R.
Put these on a Post It note near your workplace as a reminder and soon they become 2nd nature. But note the C, T, and R for the first letters of copyright, trademark and registered trademark.

You can use the Autocorrect feature to insert symbols and many are already set up. So typing (tm) in Word will usually get you the Trademark symbol ™. In similar ways (c) gets the Copyright symbol. This is somewhat dependent of fonts chosen and it also carries over to Excel. To set up Autocorrect, go to Go to File > Options > Proofing and select AutoCorrect Options. On the AutoCorrect tab, select or clear Replace text as you type. Various things can be set up that may be useful. "deg" in my case (without the quote marks) , inserts the degree symbol which I use frequently as an engineer. An advantage of AutoCorrect is it usually works in multiple Microsoft products. So my "deg" will also insert the degree symbol in Excel or Outlook.

I agree with all of your suggestions and examples, EXCEPT that when I'm creating POIs I use Notepad, and with Notepad all that stuff goes out the window. Perhaps I could have mentioned that I use Notepad in my post although it'd be interesting to take a poll to see what is the POI-creating app of choice here in the Factory.
So what is it, boys and girls, what app do you use to create your custom POIs?

Phil

this is what I get in Excel when I open the CSV with that line.
7840 Doty Rd•Madison OH 44057•440-466-3485

Since I edit POI files in Excel for my preferences, this character creates problems. There should be something better to use like : or _ or :..what do you think?

I think you can use Word to create the wanted character (I use ● for my files) then save that Word document in the folder where you do your POI work. Here's what my Debonne record looks like.

-81.006280,41.740424,Debonne Vineyards,7840 Doty Rd•Madison OH 44057•440-466-3485

I see that you live in KC. In your example I recognize the address as Debonne Vineyards in Madison, Ohio. What POI file is that record part of? My wife and I and Ivy the Airedale hit Dobonne pretty often - it's kind of our go-to winery.

Phil

When opening a CSV file in Excel, the right single quotation mark character is converted into the "’" symbol. This is because Excel opens the CSV file using the default windows encoding (Windows-1252). So in original content, are you inserting the single quotation mark or is it getting included by some copy > paste operation?

On anything already existing, and giving you problems, you can use the replace feature in Excel and replace the character sequence with what you want. You find "replace" on the Home tab and then over at the right.

Or use Notepad instead of Excel. I know both but I can't see the advantage of Excel. With Notepad I just string out the line replacing any commas (after the first three, of course) with the special character of my choice, in my case, a ● . Then I either rename my POI file from name.txt to name.csv or do a DOS copy name.txt name.csv if you want to keep both the .txt and .csv versions.

Phil

@plunder points out to consider using Notepad instead of Excel and that is probably good advice. Keep the task and how you manipulate that task as simple as possible. Excel can present another issue with using POI files in that a negative sign for longitude may be seen as an error by Excel. Chose the wrong corrective action, and you just dig yourself deeper.

Notepad has a "replace" feature as well, just click on Edit, then chose "Replace" from the drop down.

You should be able to use the ASCII symbols in your notebook.
The complete list is available on the Internet.
Note that the numeric entries may require entries from the numeric keypad only.
Check out the symbol you want to use and verify the output.
Most characters are available by holding down the ALT key and entering the correct number (usually from the numeric keypad).
Examples: ALT + 1 = ☺ ; ALT + 2 = ☻ ; ALT + 3 = ♥ ; ALT + 4 = ♦ ; ALT + 5 = ♣ ; ALT + 6 = ♠ ; ALT + 7 = • (bullet symbol) ; ALT + 248 = ° (degree symbol) ; ALT + 0169 = © (copywrite symbol) ; ALT + 0174 = ® (registered symbol).

I copied that character ● to a txt file and when I look at it with HxD hex editor or Notepad++ Ascii 2 hex converter it comes out E2978F.
Even ALT 7, • , shows as E280A2.
So, I have no idea what ALT sequence produces it and why it shows up as 3 hex bytes.

The advantage of Excel is that I can create formulas to rearrange things as I like to see them, so I have fairly consistent POI files. Since everyone wants to do things differently, it would be a real chore with Notepad. It would be nice if there was a standard to work toward, but I tried to start that and no one was interested.

Try this:
1. Open the Excel spreadsheet you're working with.
2. Click on the INSERT tab.
3. Click on SYMBOL on the far right in the SYMBOLS thingie.
4. Click your choice for your comma replacement symbol.
5. Click INSERT.

Note: I'd do this for the first occurrence then just copy/paste for subsequent uses.

Phil

Your making things too complicated!

The bullet symbol is acquired with a numeric keyboard by using Alt-0149. In Excel, go to Insert, then Symbol (at far right). At the bottom, in the box next to Character code: type 149 and you will get that symbol (•). This will also add the symbol to the "Recently used symbols:" area at the bottom.

Once inserted, select the symbol, copy the symbol using the shortcut combination Ctrl-C. Then you can paste it where needed using the shortcut combination Ctrl-V.

utf-8
the first 128 characters are ascii,
subsequent characters are represented by delimiter strings and a number of hex characters that codepage
e2 (and on to infinity)

the small bullet • is E2 80 A2 (utf8)(code 80 on shift a2)
the large bullet ● is E2 97 8F (utf8)(code 97 on shift 8f)
when 2 hex characters are full as I understand it, could be backwards utf 8 sticks another delimiter in and starts again,

there are utf 8 characters multibytes long
-utf 8 includes all 4000+ kanji characters, khmer, sanskrit, emojis tens of thousands of glyphs, all the characters of all written laqnguages-,
It confuses the heck out of me