Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Shift-JIS
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 a -- 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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Shift-JIS
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" wrote in message ... 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 a -- 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Shift-JIS
On Apr 29, 5:29*pm, "Mark Ivey" (do_not_spam)
wrote: 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 Mark Ivey "Greg Lovern" wrote in message ... 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 a -- 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- Hide quoted text - - Show quoted text - |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Shift-JIS
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/libr...20(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" wrote: 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 a -- 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Shift-JIS
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/micro...460a513a102139 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 On Apr 30, 5:09 am, James Snell wrote: 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/libr...20(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" wrote: 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 a -- 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Excel and Shift-JIS
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 On Apr 29, 3:19 pm, Greg Lovern wrote: 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 a -- 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Shift roster in Excel | Excel Worksheet Functions | |||
Excel Shift Key Lock | Excel Discussion (Misc queries) | |||
Margin shift - Excel | Excel Discussion (Misc queries) | |||
How 2 use right shift "<<" and left shift "" operator in excel? | Excel Programming | |||
Excel shift table | Excel Discussion (Misc queries) |