hex to text(ascii) conversion

[barmatt80]

I have a xml file that I import to access and I have two fields that are in the hex format. Is there away to convert those fields to text within access? I am thinking to do it after the import or should I do it on import?

Thanks,

Also thanks for all previous help, much appreciated.

[FishVal]

Hi, there.

Import hex number to a text field of a table (tbl.txtHex in the example). Run the following query:

UPDATE tbl SET tbl.txtHex = Chr(Val("&H" & [txtHex]));
 

Regards,
Fish

P.S. Assumed you have two-digit hexadecimal number.

[barmatt80]

thanks i'll give it a try. I will post back success or failure.

[barmatt80]

it is a base 16 hexadecimal number i think. The field has 1024 characters. I guess that makes that query not work or am i missing something? If that query would still apply, then i get the error that it is not updatable.

[FishVal]

it is a base 16 hexadecimal number i think. The field has 1024 characters. I guess that makes that query not work or am i missing something? If that query would still apply, then i get the error that it is not updatable.

More details would be nice.
So, you mean hexadecimal number with more than 2 hexadecimal digits (this means more than 1 ASCII character). So far so good.
Have you import the data to an Access table? What is the type of field in the table where this hexadecimal number is stored?

[barmatt80]

thanks FishVal. I do have the data in an access table, and the column name is called RemoteData with a data type memo(used memo because on importing the xml file, the RemoteData node had 1024 characters or less).

Here is a sample of my data in one of the fields:

485454502f312e3120323030204f4b0d0a446174653a205475652c2031382044656320323030372031323a35323a323320474d540d0a5365727665723a204170616368652f322e302e3532202843656e744f53290d0a4c6173742d4d6f6469666965643a204672692c203138204d61722032303035

Which using an online converter should be converted to:

HTTP/1.1 200 OK
 
Date: Tue, 18 Dec 2007 12:52:23 GMT
 
Server: Apache/2.0.52 (CentOS)
 
Last-Modified: Fri, 18 Mar 2005

Anything else should I provide?

[FishVal]

Quite enough. Try this.

Public Function Hex2ASCII(strInput As Variant) As Variant
 
    'if field is null then exit function
    If IsNull(strInput) Then Exit Function
    'make number of digits even
    If Len(strInput) / 2 <> Int(Len(strInput) / 2) Then strInput = "0" & strInput
    'convert hex codes to ASCII
    For i = 1 To Len(strInput) Step 2
        Hex2ASCII = Hex2ASCII & Chr(Val("&H" & Mid(strInput, i, 2)))
    Next i
 
End Function
 

UPDATE tbl SET tbl.txtHex = Hex2ASCII([txtHex]);
 

[ADezii]

I have a xml file that I import to access and I have two fields that are in the hex format. Is there away to convert those fields to text within access? I am thinking to do it after the import or should I do it on import?

Thanks,

Also thanks for all previous help, much appreciated.

I followed in FishVal's footsteps but only took a different approach. I Delimited the String by the Carriage Return/Line Feed Characters (0d0a in Hex), then used the Split() Function to populate a Variant Array which would then consist of each sentence (terminated by 0d0a). I then used FishVal's code to convert each Byte. The code and subsequent Output are listed below. If you have any problem implementing this approach, let us know:

Dim strHex As String, varHex As Variant, intCounter As Integer
Dim T, strString As String
 
strHex = "485454502f312e3120323030204f4b0d0a446174653a205475" & _
         "652c2031382044656320323030372031323a35323a323320474" & _
         "d540d0a5365727665723a204170616368652f322e302e3532202" & _
         "843656e744f53290d0a4c6173742d4d6f6469666965643a2046" & _
         "72692c203138204d61722032303035"
 
varHex = Split(strHex, "0d0a")
 
For intCounter = LBound(varHex) To UBound(varHex)
  For T = 1 To Len(varHex(intCounter)) Step 2
    strString = strString & Chr$(Val("&H" & Mid$(varHex(intCounter), T, 2)))
  Next
    Debug.Print strString
    strString = ""        'Reset String
Next

OUTPUT:

HTTP/1.1 200 OK
Date: Tue, 18 Dec 2007 12:52:23 GMT
Server: Apache/2.0.52 (CentOS)
Last-Modified: Fri, 18 Mar 2005

P.S. - FishVal, Hope you didn't mind the intrusion, just found this problem interesting. If I stepped on your toes, I do apologize.

[FishVal]

P.S. - FishVal, Hope you didn't mind the intrusion, just found this problem interesting. If I stepped on your toes, I do apologize.

Hello, ADezii.

Apologies not needed. I appreciate every comment concerning my posts.

[barmatt80]

FishVal and ADezii thank you so much. I greatly appreciate it.

FishVal I got yours to work flawless and fast. ADezii I kept getting a compile error: invalid outside procedure. I haven't had time to look into it, but it hopefully i can figure it out.

Thanks once again.

[ADezii]

FishVal and ADezii thank you so much. I greatly appreciate it.

FishVal I got yours to work flawless and fast. ADezii I kept getting a compile error: invalid outside procedure. I haven't had time to look into it, but it hopefully i can figure it out.

Thanks once again.

Very interesting, I had no problem running this code in Access 2000 and 2003. If you find out what the problem is, please let me know. In any event, I'm glad you got FishVal's code to work flawlessly. Glad we were able to assist you, I'm not speaking for FishVal, but I'm sure he will feel the same way also. See you around.