Converting Long HEX string to ASCII

[jamesg]

Hope someone can help.
I did an export of our users from Active Directory using CSVDE.exe. This generated a csv file which I then work with in Excel and word.
My problem is that any field that has either a carrige return, or an accent in the name come out as a long HEX string with an x' at the start and an ' at the end instead of the proper word.
For example Stephane (The e should have an accent, but I can't display that here) comes out as X'5374c3a97068616e65'.
What I need help with is a formula/macro/vba code, that would search for all cells whos data starts with x'. Once found I need to remove the x' from the begining and the ' from the end. Then pair each 2 characters of hex together and convert then to ascii. If we take the example above, X'5374c3a97068616e65' would become 5374c3a97068616e65, then 53 becomes S, 74 becomes t, c3 and a9 form e with an accent, 70 is p... and so on and so forth. Then once that's all done, put the converted name/title back into the orginal field.
I thank everyone in advance for any help they can give me with this.
If you need more information, examples, or know of a better way for me to do this, please feel free to let me know.
Thanks
James

 

[new_vba_pa]

I also tried this and ended up getting the sub routine error. Both the toolpaks installed and referenced. Copy/Pasted the above into a new VBA function. Tried on two different PCs, one with Excel 2010 and one with 2013. Any thoughts?

 

[shg]

Post an example of what you have and what you want.

 

[new_vba_pa]

Post an example of what you have and what you want.

simply put i'd like to convert hex like this from a CSVDE export:

X'323120317374207374726565740d0a6275696c64696e672031320d0a0d0a'

<colgroup><col width="766"></colgroup> <tbody>
</tbody>

to something like this in excel:
21 1st street
building 12

if need be can convert the line feeds 0d0a to commas so output is in one line:

21 1st street, building 12

thanks for the reply!

 

[shg]

Function Asc2Str(sInp As String) As String
    Dim i           As Long

    For i = 1& To Len(sInp) Step 2&
      Debug.Print Mid$(sInp, i, 2&) & " ";
        Asc2Str = Asc2Str & Chr$("&H" & Mid$(sInp, i, 2&))
      Debug.Print Asc2Str
    Next i
End Function

Row\Col	A​	B​	C​
1​	X'323120317374207374726565740d0a6275696c64696e672031320d0a0d0a'	21 1st street building 12	B1: =Asc2Str(MID(A1, 3, LEN(A1) - 3))

 

[thisoldman]

The original file appears to be encoded as UTF-8. That would be Windows code page 65001. Excel does not always recognize UTF-8.

One solution:
Use File > Open from within Excel. You want to see the Text Import Wizard dialog box. The important item to change is on the first page of the wizard, the dropdown box labeled 'File origin:'. Select '65001 : Unicode (UTF-8)'.

Possible solution 2
Open the file in a good code editor and change the file encoding there. If you do this, note that Windows programs, including Excel, often call UTF-16 "UNICODE". It's not correct but...

Aside: Oddly, the files from an unzipped *.xlsx file are encoded as UTF-8. I think Microsoft programs have trouble recognizing the encoding when there is no initial byte order mark, or BOM. The BOM is optional with UTF-8.

 

[new_vba_pa]

Function Asc2Str(sInp As String) As String
    Dim i           As Long

    For i = 1& To Len(sInp) Step 2&
      Debug.Print Mid$(sInp, i, 2&) & " ";
        Asc2Str = Asc2Str & Chr$("&H" & Mid$(sInp, i, 2&))
      Debug.Print Asc2Str
    Next i
End Function

Row\Col	A​	B​	C​
1​	X'323120317374207374726565740d0a6275696c64696e672031320d0a0d0a'	21 1st street building 12	B1: =Asc2Str(MID(A1, 3, LEN(A1) - 3))

SGH -- You Totally Rock! My other laptop is still "reinstalling" office 2013 (going on 4+ hours) so I figured I'd try it on my company laptop w/ Excel 2010; company is tight on using VBA/Macros. Your code/example worked great...I had to change the cell reference in the formula to match my CSV/Excel file but it worked like a charm and now shows the street addresses for the exported user accounts. Thank you so much! So to summarize for others searching wildly like myself...

I ran CSVDE against AD to get a list of key info for all active users:

csvde -f "GETLISTOFALLEmployees.csv" -r "(&(objectClass=user) (objectcategory=person)(Mail=*)(sn=*))" -l "dn, postalcode, name, o, c, co, st, l, company, department, physicalDeliveryOfficeName, streetAddress" -s DOMAIN001.COM

The CSV export had HEX output for "streetAddress" column for entries with line feeds in the text.

I added another column in the spreadsheet next to the HEX to convert to text; and used the Code and Formula posted above from SGH to resolve (convert) HEX to Text. The HEX entries are now readable and can be used like any other data for further sorting/filtering.

 

[shg]

You're welcome.