Getting 'Smart Quotes' out of Access

  • Thread starter Thread starter Mark H
  • Start date Start date
M

Mark H

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?
 
Mark H said:
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?
Click to expand...

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 said:
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.
Click to expand...

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.
 
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.
Click to expand...

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.
Click to expand...

....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.
 
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."
Click to expand...

Well, you know the old saying - ASCII a silly question, get a silly ANSI!
 
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
solve your problem though.
Click to expand...

Fonts don't change character encoding, so this can't possibly solve
the problem. I don't know why you keep suggesting this. The problem
described is a mismatch between source and destination character
encodings. Resolving that mismatch the only thing that's going to
definitively solve the problem.
 
*GROAN*
Click to expand...

I'm sure that John will take much pleasure in your response. As a career
punster, he knows that if it doesn't elicit a "groan", it can't possibly be
a great pun.

Larry
Access newsgroup support is alive and well in USENET
comp.databases.ms-access
 
Back
Top