Getting 'Smart Quotes' out of Access
in [General]
|
Prev: Text to Combo Box
Next: Pulling from three tables
From: Rick Brandt on 19 May 2010 02:27 Arvin Meyer [MVP] wrote: > "Excuse me, but my Notepad is using Courier New, and it doesn't have curly > quotes" I think I set that up to match the font I use in the IDE code > window. Smart quotes are only "curly" in some fonts. In others they are just slanted. Regardless of how a particular font displays them they are different characters from ' and " and in many situations outside of Office apps they will not render properly.
From: Arvin Meyer [MVP] on 19 May 2010 09:17 "Mark H" <MarkH(a)discussions.microsoft.com> wrote in message news:42AD9EE0-ECB0-424B-BFC6-5526DD2B8148(a)microsoft.com... > Every week I export a txt file of a table out of Access to a third party > provider who converts it to an XML file for wide distribution and then > upload > to various websites such as Amazon. I am having problems with lots of > characters being converted improperly, most significantly the apostrophes > and > quotation marks. Is there a way I can 'clean' these out of my database > (use a > different font such as Arial Unicode in the table?) or when I export the > txt > file? You can change the table font. Open the table, select the data,by either selecting all the column, or rows, or by clicking on the intersection of them in the opper left corner, now go to Format >>> Font and choose the font you wish. I don't know if this will solve your problem though. -- Arvin Meyer, MCP, MVP http://www.datastrat.com http://www.accessmvp.com http://www.mvps.org/access
From: Douglas J. Steele on 19 May 2010 13:10 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message news:ujXIoW19KHA.5464(a)TK2MSFTNGP05.phx.gbl... > "Mark H" <MarkH(a)discussions.microsoft.com> wrote in message > news:42AD9EE0-ECB0-424B-BFC6-5526DD2B8148(a)microsoft.com... >> Every week I export a txt file of a table out of Access to a third party >> provider who converts it to an XML file for wide distribution and then >> upload >> to various websites such as Amazon. I am having problems with lots of >> characters being converted improperly, most significantly the apostrophes >> and >> quotation marks. Is there a way I can 'clean' these out of my database >> (use a >> different font such as Arial Unicode in the table?) or when I export the >> txt >> file? > > You can change the table font. Open the table, select the data,by either > selecting all the column, or rows, or by clicking on the intersection of > them in the opper left corner, now go to Format >>> Font and choose the > font you wish. I don't know if this will solve your problem though. Unless there's a reason for keeping them in the text, you might try running an Update query to change them permanently. The ASCII value for an opening double smart quote is 147, a closing double smart quote is 148. An opening single smart quote is 145, a closing single smart quote is 146. That means you can use: UPDATE MyTable SET MyField = Replace(Replace(Replace(Replace(MyField, Chr(147), Chr(34)), Chr(148), Chr(34)), Chr(145), Chr(39)), Chr(146), Chr(39)) Alternative, use that awful Replace in a Select query. -- Doug Steele, Microsoft Access MVP http://www.AccessMVP.com/DJSteele (no e-mails, please!)
From: David W. Fenton on 19 May 2010 15:26 "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in news:OzoepOv9KHA.148(a)TK2MSFTNGP06.phx.gbl: > "Excuse me, but my Notepad is using Courier New, and it doesn't > have curly quotes" I think I set that up to match the font I use > in the IDE code window. May well be, but that's not the default. I just Googled how to change the font, and it's quite easy -- just go to the Format menu and choose Font, etc. It never occurred to me that: 1. a text editor would allow you to change the font (other than just changing the display font). 2. changing it in one instance of the text editor is a permanent change. I don't know what version of Courier New you're using that lacks curly quotes. I notice that "Courier" lacks curly quotes, but Courier NEW has them. But as I said, switching the font doesn't cause the curly quotes to become straight quotes *unless* the font you're using has mapped ANSI 0147 and 0148 to use the same idiograph as 0034. On my WinXP PC, the Courier font displays a black box, indicating no idiograph has been assigned to the positions allocated for curly quotes. ( just checked on a relatively pristine Win7 installation, and Notepad defaults to Lucida Console. I definitely have never changed it on that machine, nor can I imagine that I once knew that I could change it in the past and then forgot!) -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/
From: David W. Fenton on 19 May 2010 16:04
"Douglas J. Steele" <NOSPAM_djsteele(a)NOSPAM_gmail.com> wrote in news:utQ1qY39KHA.980(a)TK2MSFTNGP04.phx.gbl: > "Arvin Meyer [MVP]" <arvinm(a)mvps.invalid> wrote in message > news:ujXIoW19KHA.5464(a)TK2MSFTNGP05.phx.gbl... >> "Mark H" <MarkH(a)discussions.microsoft.com> wrote in message >> news:42AD9EE0-ECB0-424B-BFC6-5526DD2B8148(a)microsoft.com... >>> Every week I export a txt file of a table out of Access to a >>> third party provider who converts it to an XML file for wide >>> distribution and then upload >>> to various websites such as Amazon. I am having problems with >>> lots of characters being converted improperly, most >>> significantly the apostrophes and >>> quotation marks. Is there a way I can 'clean' these out of my >>> database (use a >>> different font such as Arial Unicode in the table?) or when I >>> export the txt >>> file? >> >> You can change the table font. Open the table, select the data,by >> either selecting all the column, or rows, or by clicking on the >> intersection of them in the opper left corner, now go to Format >> >>> Font and choose the font you wish. I don't know if this will >> solve your problem though. > > Unless there's a reason for keeping them in the text, you might > try running an Update query to change them permanently. > > The ASCII value for an opening double smart quote is 147, a > closing double smart quote is 148. An opening single smart quote > is 145, a closing single smart quote is 146. Those are the ANSI values. For instance, ASCII 148 is the o with umlaut. I'm very confused here, as when I open Access, Chr(148) does return a curly quote, and Asc("�") (that's a curly close quote in the center returns 148. It surprises me that the Asc() function returns an ANSI value. The help file is silent on the distinction between ASCII and ANSI encoding, and just says "character code." That means you can use: > > UPDATE MyTable > SET MyField = Replace(Replace(Replace(Replace(MyField, Chr(147), > Chr(34)), Chr(148), Chr(34)), Chr(145), Chr(39)), Chr(146), > Chr(39)) > > Alternative, use that awful Replace in a Select query. ....which is to say that your recommendation works, but your terminology was inaccurate. BTW, I've contemplated for years creating a Replace function that would accept an array for both the find and replace arguments, as is the case in PHP, but have never quite gotten round to it. It would look something like this: ODQ = Chr(147) CDQ = Chr(148) OSQ = Chr(145) CDQ = Chr(146) DQ = Chr(34) SQ = Chr(39) strResult = aReplace([InputField], _ Split(ODQ & ",� & CDQ & "," & OSQ & "," & CSQ,","), _ Split(DQ & "," & DQ & "," & SQ & "," & SQ,",") Or another way: Dim strFind(3) As String Dim strReplace(3) As String strFind(0) = Chr(147) strFind(1) = Chr(148) strFind(2) = Chr(145) strFind(3) = Chr(146) strReplace(0) = Chr(34) strReplace(1) = Chr(34) strReplace(2) = Chr(39) strReplace(3) = Chr(39) strResults = aReplace([InputField], strFind(), strReplace()) Obviously, you couldn't do this in a query, where the first solution would work better, but this kind of thing in code would be more useful, particularly if you have to do multiple replace operations with the same find/replace pairs. -- David W. Fenton http://www.dfenton.com/ usenet at dfenton dot com http://www.dfenton.com/DFA/ |