Search And Replace

Hey all,

When I export an Excel file into a tab-delimited file, it seems to work just fine at retaining the special characters. When I try to read that file from Cold Fusion into a query, it converts many of the special characters (-#@%&$*/-, etc.) into a small box, which traditionally means that it does not have an equivelent for the character in the given font.

My font setting is (verdana, arial, helvetica), and I have seen every character it seems to not recognize in that font before. Any idea on how to prevent Cold Fusion from loosing what those characters are?

As an example, I have a sentence that says something like:

"The target area is within reach of the reader's grasp."

... which converts to ...

"The target area is within reach of the reader[]s grasp."

... where the "[]" represents a box.

I do not need to have Cold Fusion change the sentence automatically (especially since whenever I copy a box and paste it into HomeSite + it reads as a "?" regardless of what it originally was.), I just need to see if the actual boxes are there so I can alert the user that a change needs to be made.

Your help on this question would be greatly appreciated!

Many thanks ...

OK folks, once again I find myself answering my own question (third time in a row) just in case anyone else encounters the same problem.

It seems that the way Excel thinks of punctuation characters is different than the way a text file does. When saving from Excel to a tab-delimited text file, whatever default font (or lack thereof) the text file uses does not recognize the way Excel spits out the punctuation (with a couple of exceptions). So it displays a box telling the user that there is no character atching the saved character (like when you look at your fonts and see a bunch of boxes where there is no equivelent character.

What the text file is displaying and what it records for itself are two different things, and we can use this to our advantage. So lets say that I have the sentence in my original question:

"The target area is within reach of the reader's grasp."

... whch shows up to me something like:

"The target area is within reach of the reader[]s grasp."

... again the "[]" representing a box. What the file is actually saving looks more like:

"The target area is within reach of the readerÂ's grasp."

Hopefully you can see the capital "A" with a hat above it. The character I am referring to would be written in HTML like "Â".

So as we can see, the original apostrophe is saved and a second character is added to it, but the combination is treated as one character for some reason, which the text file does not recognize, and therefore displays incorrectly. Cold Fusion displays the same as the text file, but when saved in a variable, actually retains the individuality of the two characters.

So we can do a simple search and replace to remove the covered "A", which leaves only the punctuation character. It seems like a work-around, but so far I have had no problem.

If this does not work for you, then push the variable into a SQL database and check to see which character it is putting next to the punctuation.

The search and replace looks like:

<cfset question = #REPLACENOCASE(question, "Â", "", "all")#>

There is probably something else that can be done on the Excel side, but this works.

Hope this can help someone.

Fletch

Sorry to bring back such an old message - but the above solution didn't work in my case.


I have a script that is inserting data into an Access database, and then I'm recalling it another. The text going in has single apostrophes, and they are coming back up as the boxes described above. In my case, the boxes also appear if I do a "View Data..." of the database in Dreamweaver.

Do I need to perform the REPLACENOCASE function as the data is going in? It doesn't work with the data already inserted.

I would almost guarantee that the single apostrophe does not look like this: '

But instead is an angled apostrophe something like this: `

This often happens when cutting and pasting from something like MS Word. Your best option is to replace these sorts of characters prior to insertion into the database.