Excel and Shift-JIS

[Greg Lovern]

I need to get Excel data with Japanese characters uploaded as a text
tab-delimited files to a web server that requires Shift-JIS character-
encoding for Japanese characters. Excel won't save as Shift-JIS.

First, it works fine if we manually:
-- Save out from Excel as Excel's "Unicode Text" file type.
-- Open in Word.
-- Save out from Word as Shift-JIS (automatically prompted by Word
when saving plain text file).
-- Upload to the server using an internal web tool to which I have no
access to or knowledge of the code that makes it work.

To preserve the Japanese characters, I save from Excel as the "Unicode
Text" file type, which is tab-delimited as required. This much is the
same as the manual steps above that work correctly.

Then I use the FileSystemObject to load the Unicode file into a text
variable:

Dim fso As Object
Dim oTextStream As Object
Dim sText as String
Set fso = CreateObject("Scripting.FileSystemObject")
Set oTextStream = fso.OpenTextFile(sFile, , , -1) '-1 = TristateTrue =
Unicode
sText = oTextStream.ReadAll


Then I convert the Unicode text to Shift-JIS:

Dim strShiftJIS As String
Const LOCALE_ID_SHIFT_JIS As Long = 1041 'this is the decimal shift-
jis LCID as opposed to the Hex one.
strShiftJIS = StrConv(sText, vbFromUnicode, LOCALE_ID_SHIFT_JIS)


Then I create an MSXML object, specifiying a content type of "text/
xml" with a shift-jis charset, and upload it to the server:

(I inherited this part, except for specifying a shift-jis charset,
from code that works fine for English, German, and French. The person
who wrote it is no longer with the company.)

Dim httpreq As Object 'IXMLHTTPRequest
Set httpreq = CreateObject("MSXML2.XMLHTTP.3.0")
Call httpreq.Open(bstrmethod:="POST", bstrurl:="http://myurl",
varAsync:=False)
Call httpreq.setRequestHeader("Authorization", "Basic " & <encoded
password>)
Call httpreq.setRequestHeader("Content-Type", "text/xml;charset=shift-
jis")
Call httpreq.send(strShiftJIS)


The upload is successful, but the characters are wrong. They are
Japanese characters but not the right ones.


So the steps are:
-- Save file from Excel as Unicode.
-- Open file with FileSystemObject as Unicode.
-- Convert text from Unicode to Shift-JIS.
-- Upload text as Shift-JIS, to a server that requires Shift-JIS.


Any suggestions on what I'm doing wrong?


Thanks,

Greg

 

[Mark Ivey]

Are you sure that your conversion to Unicode isn't causing the issue?

Can you save the file as a text file?

Mark Ivey

 

[Greg Lovern]

Are you sure that your conversion to Unicode isn't causing the issue?

Yes, about as sure as reasonably possible, because:
-- That step is the same as in the manual procedure that works fine,
the only difference being manually saving it as Unicode Text from
Excel, vs. programmatically saving it as Unicode Text (xlUnicodeText)
from Excel.
-- We tested taking the Unicode file created programmatically and
used it in the rest of the manual procedure (opened that file in Word
and saved as Shift-JIS, etc.), and that worked fine.

The conversion to Unicode happens when saving the Excel file as
Excel's "Unicode Text" file type.

Can you save the file as a text file?

We are saving the file as a text file. When we save the Excel file as
Excel's "Unicode Text" file type, we are saving the file as a text
file. Excel's "Unicode Text" file type is Unicode tab-delimited plain
text.

Greg

 

[James Snell]

Hi again greg...

Looks like your best bet is the windows API function WideCharToMultiByte.

You might want to have a look at the windows API function
WideCharToMultiByte in MSDN. It looks like it will do what you need it to
(convert the unicode into Shift-JIS).

http://msdn.microsoft.com/en-us/library/ms776420(VS.85).aspx

It looks like it will do what you need it to, although you'll have to
remember in your code that you're working with the converted data so you may
want to work in unicode as much as possible and then convert to shift-jis at
the last opportunity.

~ James

 

[Greg Lovern]

Is StrConv not able to correctly convert from Unicode to Shift-JIS?
The line I'm using to do that was taken from a post by Michael Kaplan,
a guru on the subject:

Const LOCALE_ID_SHIFT_JIS As Long = 1041 'this is the decimal shift-
jis LCID as opposed to the Hex one.
strShiftJIS = StrConv(sText, vbFromUnicode, LOCALE_ID_SHIFT_JIS)


Here's the thread in microsoft.public.win32.programmer.international
where he suggested it for converting from Unicode to Shift-JIS:

http://groups.google.com/group/micr...+conversion++++vbFromUnicode#11460a513a102139


I wanted to test StrConv's conversion from Unicode to Shift-JIS by
saving it out as a file and manually uploading, but I didn't find a
way to save it out that preserves Shift-JIS.


Thanks,

Greg

 

[Greg Lovern]

I finally had an opportunity to talk to the developers who work with
the server to which we're uploading. It turns out the server expects
an array of 8 bits per element, or in other words, a Byte array.
Sending plain text, which was how it was being done when I inherited
the project, happened to work fine for English/French/German and
probably all other single-byte character sets, but not for multi-byte
character sets.

Here's what is working fine now:

Save from Excel as plain text, which it turns out is Shift-JIS on
Japanese Excel, preserving the Japanese characters unlike doing the
same on English Excel.

Open as binary:

nSourceFile = FreeFile
Open sFile For Binary As #nSourceFile
sText = InputB$(LOF(1), 1)
Close #nSourceFile


Convert to Byte array:

Dim bytearrayUploadText() As Byte
bytearrayUploadText = sText


That didn't get past IXMLHTTPRequest.Send, which returned an error
saying the parameter (the byte array) was wrong. So, I wrapped the
byte array in a variant:

Dim vArray As Variant 'declare as variant, not array.
vArray = bytearrayUploadText


And that worked! On the first try and all subsequent tries, the
characters were all correct.

Greg