hex to text(ascii) conversion | Member | | Join Date: Dec 2007
Posts: 45
| | |
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.
|  | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: hex to text(ascii) conversion
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.
| | Member | | Join Date: Dec 2007
Posts: 45
| | | re: hex to text(ascii) conversion
thanks i'll give it a try. I will post back success or failure.
| | Member | | Join Date: Dec 2007
Posts: 45
| | | re: hex to text(ascii) conversion
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.
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: hex to text(ascii) conversion
Quote:
Originally Posted by 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. 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?
| | Member | | Join Date: Dec 2007
Posts: 45
| | | re: hex to text(ascii) conversion
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?
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: hex to text(ascii) conversion
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]);
-
|  | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: hex to text(ascii) conversion
Quote:
Originally Posted by 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. 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.
| | Expert | | Join Date: Jun 2007 Location: Israel
Posts: 2,584
| | | re: hex to text(ascii) conversion
Quote:
Originally Posted by ADezii 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.
| | Member | | Join Date: Dec 2007
Posts: 45
| | | re: hex to text(ascii) conversion
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.
| | Expert | | Join Date: Apr 2006 Location: Philadelphia
Posts: 5,218
| | | re: hex to text(ascii) conversion
Quote:
Originally Posted by 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. 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.
|  |
Similar Microsoft Access / VBA bytes | | |  /bytes/about
We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights.
Get the best answers to your questions from over 226,471 network members.
|
