By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,506 Members | 2,311 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,506 IT Pros & Developers. It's quick & easy.

hex to text(ascii) conversion

P: 55
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.
Jan 2 '08 #1
Share this Question
Share on Google+
10 Replies


FishVal
Expert 2.5K+
P: 2,653
Hi, there.

Import hex number to a text field of a table (tbl.txtHex in the example). Run the following query:
Expand|Select|Wrap|Line Numbers
  1. UPDATE tbl SET tbl.txtHex = Chr(Val("&H" & [txtHex]));
  2.  
Regards,
Fish

P.S. Assumed you have two-digit hexadecimal number.
Jan 2 '08 #2

P: 55
thanks i'll give it a try. I will post back success or failure.
Jan 2 '08 #3

P: 55
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.
Jan 2 '08 #4

FishVal
Expert 2.5K+
P: 2,653
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?
Jan 2 '08 #5

P: 55
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:

Expand|Select|Wrap|Line Numbers
  1. 485454502f312e3120323030204f4b0d0a446174653a205475652c2031382044656320323030372031323a35323a323320474d540d0a5365727665723a204170616368652f322e302e3532202843656e744f53290d0a4c6173742d4d6f6469666965643a204672692c203138204d61722032303035
Which using an online converter should be converted to:

Expand|Select|Wrap|Line Numbers
  1. HTTP/1.1 200 OK
  2.  
  3. Date: Tue, 18 Dec 2007 12:52:23 GMT
  4.  
  5. Server: Apache/2.0.52 (CentOS)
  6.  
  7. Last-Modified: Fri, 18 Mar 2005
Anything else should I provide?
Jan 2 '08 #6

FishVal
Expert 2.5K+
P: 2,653
Quite enough. Try this.

Expand|Select|Wrap|Line Numbers
  1. Public Function Hex2ASCII(strInput As Variant) As Variant
  2.  
  3.     'if field is null then exit function
  4.     If IsNull(strInput) Then Exit Function
  5.     'make number of digits even
  6.     If Len(strInput) / 2 <> Int(Len(strInput) / 2) Then strInput = "0" & strInput
  7.     'convert hex codes to ASCII
  8.     For i = 1 To Len(strInput) Step 2
  9.         Hex2ASCII = Hex2ASCII & Chr(Val("&H" & Mid(strInput, i, 2)))
  10.     Next i
  11.  
  12. End Function
  13.  
Expand|Select|Wrap|Line Numbers
  1. UPDATE tbl SET tbl.txtHex = Hex2ASCII([txtHex]);
  2.  
Jan 2 '08 #7

ADezii
Expert 5K+
P: 8,599
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:
Expand|Select|Wrap|Line Numbers
  1. Dim strHex As String, varHex As Variant, intCounter As Integer
  2. Dim T, strString As String
  3.  
  4. strHex = "485454502f312e3120323030204f4b0d0a446174653a205475" & _
  5.          "652c2031382044656320323030372031323a35323a323320474" & _
  6.          "d540d0a5365727665723a204170616368652f322e302e3532202" & _
  7.          "843656e744f53290d0a4c6173742d4d6f6469666965643a2046" & _
  8.          "72692c203138204d61722032303035"
  9.  
  10. varHex = Split(strHex, "0d0a")
  11.  
  12. For intCounter = LBound(varHex) To UBound(varHex)
  13.   For T = 1 To Len(varHex(intCounter)) Step 2
  14.     strString = strString & Chr$(Val("&H" & Mid$(varHex(intCounter), T, 2)))
  15.   Next
  16.     Debug.Print strString
  17.     strString = ""        'Reset String
  18. Next
OUTPUT:
Expand|Select|Wrap|Line Numbers
  1. HTTP/1.1 200 OK
  2. Date: Tue, 18 Dec 2007 12:52:23 GMT
  3. Server: Apache/2.0.52 (CentOS)
  4. 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.
Jan 3 '08 #8

FishVal
Expert 2.5K+
P: 2,653
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.
Jan 3 '08 #9

P: 55
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.
Jan 3 '08 #10

ADezii
Expert 5K+
P: 8,599
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.
Jan 3 '08 #11

Post your reply

Sign in to post your reply or Sign up for a free account.