Unicode/utf-8 data in SQL Server

I'm working with a MS SQL Server database created by a program from a
fine US company who seems to have gotten run over by the Unicode truck.
In their infinite wisdom they've decided to store Unicode data directly
in regular varchar fields, utf-8 encoded! (on the bright side, it is
properly utf-8 encoded). One of our customers then wants to use a csv
file created from a report to import in Excel and is getting an
attitude when the text shows up "all garbled" (which I can
understand...)

One method that works is to use Python to pull down the result set from
the database, accumulate the entire result text as a big unicode string
(while decode('utf-8') all text fields in the process) separating each
field with a tab, before encode('utf-16') the result string and writing
it to a file opened in binary mode. This ensures that the file gets a
bom, that it's in a format (utf-16) that Excel can import, and
hopefully tabs are less common than commas in the source data :-( The
csv module doesn't support Unicode.

The customer is of the firm belief that our national characters
(æøå) are part of ascii, presumably because they're
single-byte-encoded in iso-8859-1. He has no understanding for the
issues (either by choice or experience) so there is no purpose to
trying to explain the differences... Be that as it may, he might be
satisfied with a csv file in that (iso-8859-1) encoding since the local
version of Excel can import it transparently (with significant
behind-the-scenes magic I believe...?)

The Python script mentioned above has to be run on the server, since it
doesn't accept remote connections, I'm of course the only one with
access, and I'd like to remove myself from the loop. I've looked at
creating a view on the database that would cast or convert the data,
but all I've run into are vague references to StrConv, which seems to
be a VB function. Giving the customer a macro that he could run in
Excel after importing the data would probably be ok as well, so I also
tried creating an Excel VB macro using the StrConv function, but (a) it
isn't entirely clear to me that this function can do this, and (b) the
third argument to the function is an LCID, a Locale ID, which is
numeric and not defined anywhere I can find it...

Anyone have any advice?

tia,
-- bjorn

.

Relevant Pages

  • Re: Is Excel the best MS Office tool to use for this business appl
    ... I did download several of the MS Office Excel ... database for these items. ... the other end of this topic is the fact that my friend has no ... Compile and maintain my friends list of products and their cost, ...
    (microsoft.public.office.misc)
  • Re: Word 2007/Excell 2007 - random loss of field contents on mailm
    ... I think, for now, I will try to put a few dummy rows in the database while I ... the document to get data from Excel and insert it. ... use that as the data source for the merge. ... I want the field with data to insert that data in the Word doc. ...
    (microsoft.public.word.mailmerge.fields)
  • Re: Access or Excel?
    ... Bear in mind that the Excel can make use of Access data quite easily for the ... lend itself so easily to the sort of processing which a database application ... in fact just a special king of abstract entity type. ... Redundancy is where the database tells us the same 'fact' more than once, ...
    (microsoft.public.access.gettingstarted)
  • Re: My first database (long)
    ... > Hi all - feeling my way trying to create first Access database. ... > I created a database in Excel, which for various reasons would be better ... > The database contains client records. ... One worksheet ...
    (microsoft.public.access.gettingstarted)
  • Re: Best practice to store/manipulate Excel spreadsheet cells
    ... part of an event managment database. ... Ofcourse there are quite a few "cells" that correspond to the aisles/alleys ... excel to have squares properly... ... If it's a module in a database application and the "floor plans" are ...
    (microsoft.public.access.formscoding)