fabios_ss-ga:
Thank you for the clarification.
If I understand the situation correctly, you have
1) A list of users and billing codes stored in a SQL database, and
2) On a regular basis, you need to send the copier a current list of
users and their billing codes (presumably so that the users can select
their account in order to use the copier), and
3) The copier is unable to display accented characters, most likely
displaying some strange block character, or nothing at all.
There's a couple of possible ways to solve this.
---------------------------
The first way would be to leave the SQL data alone, and just 'clean'
the extracted list before sending it to the copier. This can be very
simple if the list is in the form of a text file (for example, a TXT
or CSV file), and if you are willing to perform a single manual task
each time you send the list to the copier.
Assuming you are working on a Windows computer, then using a wonderful
freeware text editor called PSPad, available from
http://www.pspad.com, you can take advantage of a built-in function
that replaces all accented characters with their non-accented version.
To see what I mean, please download and install PSPad from:
http://www.pspad.com/en/download.php
After you install it, run it and open up any text file that you may
have, which contains accented characters (for example, a text version
of the list of user names and billing codes). Now, go to the Edit
menu, and select Special Conversion>Remove Accent from char. After you
confirm that you want to remove accents from the entire document, the
editor will proceed to do just that, leaving you with a text file
ready to be saved, with no accents in it.
This method does require some manual work by you, though. Since you
mentioned that you want to have something that runs daily, that
implies to me that you are updating the copier daily; if so, then this
method might be too labor-intensive for you in the long run. It is
simple, though, so even if you choose not to use it, I suggest you
keep the PSPad editor around for times when you might need to manually
manipulate the list before sending it to the copier.
---------------------------
The second way to solve this problem would be to handle this all
within the SQL world. You can either validate new entries to detect
accented characters and reject them, or you can search for (and
replace) accented characters whenever you are generating the current
user/code list for the copier. This approach is not that easy, as the
handling of accented characters in SQL can be tricky. If you want to
handle it in SQL, then definitely engage your SQL developer to help
you with the actual coding. To help the developer with his or her
task, please consider the following.
First, there are many different versions of SQL out there, ranging
from MS SQL and mySQL, to postgreSQL and Mimer SQL (visit
http://en.wikipedia.org/wiki/SQL for more info if you are interested).
With so many variants, there are bound to be differences in the
solution. Your developer, though, should be able to follow along with
the following information and provide you with the exact solution that
works in your specific database, on your specific SQL server.
If you do not want accented characters to live in the table with the
user names, then the best thing to do is to trap it when you are
entering new users. If you are using a form to enter the data, and a
validator to check the data, then your developer can use an example
similar to the approach described here to detect and remove accents:
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/726b76c4ceeb3b8b/741233aa55ec0153?lnk=st&q=sql+accents&rnum=3&hl=en#741233aa55ec0153
(yes, it's a long link, unfortunately)
In particular, see the post by 'Daniel Blais' where he provides a
sample SQL FUNCTION declaration for a new function called
replaceAccentChar. This function can be used to return an accent-free
version of any string of up to 255 characters (please note that if the
'Æ' character matters, your developer needs to add one more line to
the function, after the while...end loop:
set @temp = Replace(@temp, 'Æ', 'AE')
Now, if you actually want to leave the accents in the user names in
the SQL database, then your developer should be able to modify your
script for extracting the usernames/billing-codes into a list, so that
all of the usernames get fed through this new function, leaving you
with a clean list. If you have already automated the creation of the
list on a daily basis, then adding this function will seamlessly allow
you to continue with your current practice, without having to worry
about accents in the output.
---------------------------
I hope that one of these two methods helps solve the problem for you.
Accented characters in SQL databases cause quite a lot of grief for
SQL programmers (just do a Google Groups search on 'sql accents' to
see what I mean... http://groups.google.com/groups/search?hl=en&lr=&q=sql+accents
). Fortunately, your case is not as complex as some of the ones you
can read about in the results of that search.
Best of luck with this! Please let me know if any part of this is
unclear, and also let me know if this works for you!
Regards,
aht-ga
Google Answers Researcher |
