23rd May 2016, 3 min read

Convert ASCII to Hex and vice versa in C and Excel VBA

In Downloading Binary Data, for example Boost C++ Library I already complained about some company policies regarding the transfer of binary data. If the openssl command is available on the receiving end, then things are pretty straightforard as the aforementioned link shows, in particular you then have Base64 encoding. If that is not the case but you have a C compiler, or at least Excel, then you can work around it.

C program ascii2hex.c converts from arbitrary data to hex, and vice versa. Excel VBA (Visual Basic for Applications) ascii2hex.xls converts from hex to arbitrary data.

To convert from arbitrary data to a hex representation

ascii2hex -h yourBinary outputInHex

Back from hex to ASCII:

ascii2hex -a inHex outputInBinary

I took particular care that the C program does not use getopt() as this is not available in Visual Studio 2010 out of the box. If there is no Visual Studio available then Fabrice Bellard's excellent Tiny C Compiler (tcc) will do.

Converting ASCII to hex is just

while ((c = fgetc(fpin)) != EOF)
    fprintf(fpout,"%02x%s",c, ++i%40 ? "":"\r\n");

The main part from converting hex to ASCII (arbitrary) is

while ((a[i] = fgetc(fpin)) != EOF) {
    if (isspace(a[i])) continue;
    if (i > 0) fputc(16*map[a[0]]+map[a[1]],fpout);
    i = 1 - i;
}

The map[] array is

        map['0'] = 0;
        map['1'] = 1;
        map['2'] = 2;
        map['3'] = 3;
        map['4'] = 4;
        map['5'] = 5;
        map['6'] = 6;
        map['7'] = 7;
        map['8'] = 8;
        map['9'] = 9;
        map['a'] = 10;
        map['b'] = 11;
        map['c'] = 12;
        map['d'] = 13;
        map['e'] = 14;
        map['f'] = 15;

If even plain hex is hindered by company policies then the map[] array can be initialized differently, i.e., instead of characters 0-9, A-F, one can use any other 16 different characters. For example, map['R']=0, map['S']=1, etc. Additionally to that one can insert random white space, which is savely ignored in above code snippet.

If you are hampered by file size limitations then the simple split program depicted in Splitting Large Files on Microsoft Windows helps.

The VBA program closely follows the logic of the C program, although things are a little more clumsy:

    cnt = 0
    i = 0
    Get #1, , a(i)
    Do While Not EOF(1)
        If a(i) <> 32 And a(i) <> 13 And a(i) <> 9 And a(i) <> 10 Then
            If i > 0 Then
                cnt = cnt + 1
                c = 16 * map(a(0)) + map(a(1))
                Put #2, , c
            End If
            i = 1 - i
        End If
        Get #1, , a(i)
    Loop

To enter VBA in Excel or Outlook press Alt-F11.

Opening a file for reading binary data in VBA is as follows:

Open ifile For Binary Access Read As #1

Similarly, opening a file writing binary data is:

Open ofile For Binary Access Write As #2

Unfortunately, when writing to a file in VBA the file is not erased, but rather you modify an already existing file. Therefore you have to explicitly delete the file with

    On Error Resume Next
    If GetAttr(ofile) <> 0 Then
        Kill ofile  ' delete file
    End If

The On Error Resume Next is necessary to catch for exceptions thrown in GetAttr().

Reading a single byte in VBA is done with Get #1,,byte. Writing a single byte is done with Put #2,,byte. It is important to calculate 16 * map(a(0)) + map(a(1)) separately and writing this to a separate variable c.