Excel UTF-8 / Unicode

Is there any way to import an excel file that contains, for instance =
korean and russian characters?

I tried

- import from ...
- automation with createObject() in combination with reading out =
.Cells(1,1).Value
- COMPROP(loExcel, 'UTF8', 1)
- SYS(987, .t.)

but always the characters come up as ???

All I need to do is read out the strings and store them in a SQL Server =
database.

Any ideas and/or good example would be appreciated.

Robert

Bernhard
Thu Apr 12 06:42:55 CDT 2007

Hi Robert,

> Is there any way to import an excel file that contains, for instance korean and russian characters?
>
> I tried
>
> - import from ...
> - automation with createObject() in combination with reading out .Cells(1,1).Value
> - COMPROP(loExcel, 'UTF8', 1)
> - SYS(987, .t.)
>
> but always the characters come up as ???
>
> All I need to do is read out the strings and store them in a SQL Server database.
>
> Any ideas and/or good example would be appreciated.
Just some idea:
If you only read from excel and write to SQL, then switch off any UTF8
conversion (according to help it ist switched on by default) and treat the data
as binary:
COMPROP(loExcel, 'UTF8', 0)
In the SQL command for writing to SQL Server, maybe have some CAST() or
conversion function to convert data from binary to text.

SYS(987) seems to be useful only if you read from ODBC, not when writing.

Regards
Bernhard Sander

RobertVanGeel
Thu Apr 12 07:22:11 CDT 2007

Thanks, yes I tried COMPROP(loExcel, 'UTF8', 0) but it does not work, I =
still get the ??? in spite of that (so all the characters in my string =
have ascii value 63)

The problem is not getting my data into SQL Server, the problem is =
getting it out of Excel.

Robert

"Bernhard Sander" <fuchs@no.spam> wrote in message =
news:uGxT3ePfHHA.208@TK2MSFTNGP05.phx.gbl...
> Hi Robert,
>=20
>> Is there any way to import an excel file that contains, for instance =
korean and russian characters?
>>=20
>> I tried
>>=20
>> - import from ...
>> - automation with createObject() in combination with reading out =
.Cells(1,1).Value
>> - COMPROP(loExcel, 'UTF8', 1)
>> - SYS(987, .t.)
>>=20
>> but always the characters come up as ???
>>=20
>> All I need to do is read out the strings and store them in a SQL =
Server database.
>>=20
>> Any ideas and/or good example would be appreciated.
> Just some idea:
> If you only read from excel and write to SQL, then switch off any UTF8 =

> conversion (according to help it ist switched on by default) and treat =
the data=20
> as binary:
> COMPROP(loExcel, 'UTF8', 0)
> In the SQL command for writing to SQL Server, maybe have some CAST() =
or=20
> conversion function to convert data from binary to text.
>=20
> SYS(987) seems to be useful only if you read from ODBC, not when =
writing.
>=20
> Regards
> Bernhard Sander

Olaf
Thu Apr 12 08:44:04 CDT 2007

>but always the characters come up as ???
Where do they come up as ???
In VFP?

VFP has no or at least only very limited
unicode support, you need to convert to
some codepage or treat it as binary.
With Multibyte strings UTF-8 seems to
be the most universal way to treat unicode.

But then, sys(987) does not need to be the wrong
idea, as Rick Strahl also found it to be useful, see
below.

>All I need to do is read out the strings and store them
>into a SQL Server database via SPT.

Did you try to insert the unicode data into SQL
Server and then read it yout from C# or VB.net
or with Access?

Take a look here:
http://www.west-wind.com/wconnect/weblog/ShowEntry.blog?id=608
and here
http://www.west-wind.com/presentations/foxunicode/foxunicode.asp

Maybe that gives you some additional ideas.

Sys(987,.T.) makes VFP turn unicode to ansi,
still you need special treatment to those ansi
strings to show them within VFP.

Bye, Olaf.

Anders
Thu Apr 12 09:55:17 CDT 2007

I would try exporting to XML in Excel and reading that with XMLTOCURSOR.
Doublebyte characters can be handled. See Help.
You can also save to Text (Unicode). That can handled in VFP with STRCONV.
-Anders

"RobertVanGeel" <robert@bign.nl> wrote in message
news:ezI4fCPfHHA.284@TK2MSFTNGP05.phx.gbl...
Is there any way to import an excel file that contains, for instance korean
and russian characters?

I tried

- import from ...
- automation with createObject() in combination with reading out
.Cells(1,1).Value
- COMPROP(loExcel, 'UTF8', 1)
- SYS(987, .t.)

but always the characters come up as ???

All I need to do is read out the strings and store them in a SQL Server
database.

Any ideas and/or good example would be appreciated.

Robert

RobertVanGeel
Thu Apr 12 10:11:37 CDT 2007

Thanks, I'm aware of sys(987) but it doesn't work:

SYS(987, .t.) && .t. or .f. does not matter here
x =3D cMyExcelCell.Value
?occurs('?', x) && gives non-0 value

Robert

"Olaf Doschke" <olaf.doschke@t-aufderlinie.de> wrote in message =
news:%23a8DyiQfHHA.1388@TK2MSFTNGP05.phx.gbl...
> >but always the characters come up as ???
> Where do they come up as ???
> In VFP?
>=20
> VFP has no or at least only very limited
> unicode support, you need to convert to
> some codepage or treat it as binary.
> With Multibyte strings UTF-8 seems to
> be the most universal way to treat unicode.
>=20
> But then, sys(987) does not need to be the wrong
> idea, as Rick Strahl also found it to be useful, see
> below.
>=20
>>All I need to do is read out the strings and store them
>>into a SQL Server database via SPT.
>=20
> Did you try to insert the unicode data into SQL
> Server and then read it yout from C# or VB.net
> or with Access?
>=20
> Take a look here:
> http://www.west-wind.com/wconnect/weblog/ShowEntry.blog?id=3D608
> and here
> http://www.west-wind.com/presentations/foxunicode/foxunicode.asp
>=20
> Maybe that gives you some additional ideas.
>=20
> Sys(987,.T.) makes VFP turn unicode to ansi,
> still you need special treatment to those ansi
> strings to show them within VFP.
>=20
> Bye, Olaf.

RobertVanGeel
Thu Apr 12 10:13:59 CDT 2007

I already tried the text/unicode path but I ran into parsing woes there =
trying to figure out which cell is which (with possible chr(10),, quotes =
etc in the texts).

The exporting to XML was the first next thing on my list to try, guess =
that's the only way to go...

Thanks!

Robert

"Anders Altberg" <anders.altberg> wrote in message =
news:e4RQmKRfHHA.1216@TK2MSFTNGP03.phx.gbl...
>I would try exporting to XML in Excel and reading that with =
XMLTOCURSOR.
> Doublebyte characters can be handled. See Help.
> You can also save to Text (Unicode). That can handled in VFP with =
STRCONV.
> -Anders
>=20
> "RobertVanGeel" <robert@bign.nl> wrote in message
> news:ezI4fCPfHHA.284@TK2MSFTNGP05.phx.gbl...
> Is there any way to import an excel file that contains, for instance =
korean
> and russian characters?
>=20
> I tried
>=20
> - import from ...
> - automation with createObject() in combination with reading out
> .Cells(1,1).Value
> - COMPROP(loExcel, 'UTF8', 1)
> - SYS(987, .t.)
>=20
> but always the characters come up as ???
>=20
> All I need to do is read out the strings and store them in a SQL =
Server
> database.
>=20
> Any ideas and/or good example would be appreciated.
>=20
> Robert
>=20
>=20
>

RobertVanGeel
Thu Apr 12 10:19:30 CDT 2007

PS I'm aware of the woes that Rick describes but I did not encouter =
them, I can just read and write UTF-8 to my SQL Server using SQLExecutes =
without using any sys(whatever), it just worked without any further =
action.

Im my ODBC connection setup I de-selected "perform translation for =
character data", that's all, maybe that's the trick?

Robert

"Olaf Doschke" <olaf.doschke@t-aufderlinie.de> wrote in message =
news:%23a8DyiQfHHA.1388@TK2MSFTNGP05.phx.gbl...
> >but always the characters come up as ???
> Where do they come up as ???
> In VFP?
>=20
> VFP has no or at least only very limited
> unicode support, you need to convert to
> some codepage or treat it as binary.
> With Multibyte strings UTF-8 seems to
> be the most universal way to treat unicode.
>=20
> But then, sys(987) does not need to be the wrong
> idea, as Rick Strahl also found it to be useful, see
> below.
>=20
>>All I need to do is read out the strings and store them
>>into a SQL Server database via SPT.
>=20
> Did you try to insert the unicode data into SQL
> Server and then read it yout from C# or VB.net
> or with Access?
>=20
> Take a look here:
> http://www.west-wind.com/wconnect/weblog/ShowEntry.blog?id=3D608
> and here
> http://www.west-wind.com/presentations/foxunicode/foxunicode.asp
>=20
> Maybe that gives you some additional ideas.
>=20
> Sys(987,.T.) makes VFP turn unicode to ansi,
> still you need special treatment to those ansi
> strings to show them within VFP.
>=20
> Bye, Olaf.

Olaf
Thu Apr 12 10:24:11 CDT 2007

>SYS(987, .t.) && .t. or .f. does not matter here
>x = cMyExcelCell.Value
>?occurs('?', x) && gives non-0 value
You can't judge x that way.
you are searching a single byte string '?',
how do you think x is treated by occurs()?

with SYS(987,.t.) you are forcing x to
be an ANSI string.

Store x via SPT and a paramterized INSERT
to SQL Server and see what you get there.

Bye, Olaf.

Olaf
Thu Apr 12 10:35:08 CDT 2007

From Rick Strahls Unicode article,
I'd go this route:

Sys(3101,65001)
Local loExcel, lcUTF8CellValue, lcUnicodeCellValue, lcBinaryCellValue
loExcel=Createobject("Excel.Application")
... load xls
lcUTF8CellValue = loExcel.Activeworkbook.cell(1,1) && or somthing like that

*** UTF-8 to Unicode conversion
lcUnicodeCellValue = STRCONV(lcUTF8CellValue,12)

*** Must explicitly force to binary - can also use CAST in 9.0
lcBinaryCellValue = CREATEBINARY(lcUnicodeCellValue )

*** SQL Server requires CASTS on the server!
SqlExec([insert into ForeignData (ID,Cellvalue) values ] +;
[(?pcID, CAST(?lcBinaryCellValue as nVarChar(4000)))])

CAST within the Insert-SQL is ugly, also you are limited to
4000 Bytes, but give that a try.

Bye, Olaf.

Olaf
Thu Apr 12 10:37:56 CDT 2007

>Im my ODBC connection setup I de-selected "perform translation for
>character data", that's all, maybe that's the trick?
yes, that plus the right choice of field type within SQL Server
cold make it work, true.

So what's your table definition in what SQL Server version
and what is your final vfp code to make it work?

Would be interesting to others too...

Bye, Olaf.

RobertVanGeel
Thu Apr 12 11:31:45 CDT 2007

So how can it find the full lenght of the string only to consist of '?'

I don't want single byte '?' signs, that's exactly the problem, i want =
nice and fine UTF8 gibberish.

Thanks anyway.

Robert
"Olaf Doschke" <olaf.doschke@t-aufderlinie.de> wrote in message =
news:06B34A52-C03E-4EF3-9268-DAEAB35469A8@microsoft.com...
> >SYS(987, .t.) && .t. or .f. does not matter here
>>x =3D cMyExcelCell.Value
>>?occurs('?', x) && gives non-0 value
> You can't judge x that way.
> you are searching a single byte string '?',
> how do you think x is treated by occurs()?
>=20
> with SYS(987,.t.) you are forcing x to
> be an ANSI string.
>=20
> Store x via SPT and a paramterized INSERT
> to SQL Server and see what you get there.
>=20
> Bye, Olaf.

RobertVanGeel
Thu Apr 12 11:32:55 CDT 2007

As I wrote, the problem is not my communication with SQL Server or =
storing/retreiving data there.
The problem is in getting data from Excel.

Robert

"Olaf Doschke" <olaf.doschke@t-aufderlinie.de> wrote in message =
news:4E1ED7A1-8ED8-4120-A1BD-82B67A6DF0BE@microsoft.com...
> >Im my ODBC connection setup I de-selected "perform translation for=20
>>character data", that's all, maybe that's the trick?
> yes, that plus the right choice of field type within SQL Server
> cold make it work, true.
>=20
> So what's your table definition in what SQL Server version
> and what is your final vfp code to make it work?
>=20
> Would be interesting to others too...
>=20
> Bye, Olaf.=20
>

RobertVanGeel
Thu Apr 12 11:37:07 CDT 2007

My god I'm such an idiot, this works perfectly...

Thanks!

Robert

"Olaf Doschke" <olaf.doschke@t-aufderlinie.de> wrote in message =
news:59C5047E-8212-4255-A76D-3C87D05ED097@microsoft.com...
> From Rick Strahls Unicode article,
> I'd go this route:
>=20
> Sys(3101,65001)
> Local loExcel, lcUTF8CellValue, lcUnicodeCellValue, lcBinaryCellValue
> loExcel=3DCreateobject("Excel.Application")
> ... load xls
> lcUTF8CellValue =3D loExcel.Activeworkbook.cell(1,1) && or somthing =
like that
>=20
> *** UTF-8 to Unicode conversion
> lcUnicodeCellValue =3D STRCONV(lcUTF8CellValue,12)
>=20
> *** Must explicitly force to binary - can also use CAST in 9.0
> lcBinaryCellValue =3D CREATEBINARY(lcUnicodeCellValue )
>=20
> *** SQL Server requires CASTS on the server!
> SqlExec([insert into ForeignData (ID,Cellvalue) values ] +;
> [(?pcID, CAST(?lcBinaryCellValue as nVarChar(4000)))])
>=20
> CAST within the Insert-SQL is ugly, also you are limited to
> 4000 Bytes, but give that a try.
>=20
> Bye, Olaf.