How to append the first zero's of HEX to the BIN string?

Question

I am trying to implement a bits extractor for CAN bus data in Excel. I struggle with the algorithm part therefore this is mainly independent from Excel/openpyxl.

I have a table with the ID being stored in column B and the binary code per time step in column F. I want to parse through the binary numbers and extract the parts which contains the signals' value. One ID can have one or more signals. Depending on the ID, I want to go through all the rows and convert the binary to decimal (as shown below). The IDs contain either 3, 4, 5, 6, 7 or 8 bytes, which is defined for each ID (example below: 2E has 8bytes, 4A has 5bytes).

Input:

A  B  C D  E            F            G   H  .. M
.. 2E .... 0000000000000AA4.. 
...4A ...  02478304B..

Organized Input: A B C D E F G H .. M .. 2E .... 00 00 00 00 00 00 0A A4.. ...4A ... 00 24 78 30 4B..

Current Output:

A B  C D E     F   G    H  .. M
..2E .... ...  101010100100 
..4A .......   100100011110000011000001001011

Wanted Output:

A B  C D E     F   G    H  .. M
..2E .... ...  0000000000000000000000000000000000000000000000000000101010100100 
..4A .......   100100011110000011000001001011

QUESTION: Since in the beginning of the measurement, most of the values are set to zero (in HEX, in column E), the BIN does not include them and the signals cannot be extracted correctly. I do not know how to do this in the convertHEXtoBIN() function (see below). The HEX value in column E is a string from which I did successfully convert to BIN but the zero's in the beginning were not included into the BIN code. Those zero's in the end are included.

Approach: In the function below, I would add an if-clause to catch the first zero's the cell.value contains a zero until the first HEX code != 0 shows up and then these zero's are then appended the eightfold (8 times a zero if two zero's show up, 4 times if one zero shows up) and put before binary code res. Another solution might be to let the convertHEXtoBIN() function to include zero's (instead of neglecting them) but I don't know whether this is even possible...

Do you have an idea how to implement this?

The code so far:

import openpyxl
from openpyxl import Workbook

theFile = openpyxl.load_workbook('Adapted_T013.xlsx')
allSheetNames = theFile.sheetnames 
print("All sheet names {} " .format(theFile.sheetnames)) 
sheet = theFile.active
def convertHEXtoBIN():
    max_row_var = sheet.max_row+1
    for row in sheet.iter_rows(min_row=1, max_row=max_row_var,values_only = True): 
        for cell in sheet["E"]:
            if cell.value is not None:
                inputHEX = str(cell.value)
                res = "{0:08b}".format(int(inputHEX,16))
                sheet.cell(row=cell.row, column=6).value = res 
        break

score 1 · Answer 1 · answered Feb 18 '20 at 09:34

1

You need 8 characters of binary for every 2 characters of hex.

That is an easy calculation and you can let python do the padding for you.

inputHEX = str(cell.value)
res = "{value:0{width}b}".format(value=int(inputHEX, 16), width=len(inputHEX) * 4)
sheet.cell(row=cell.row, column=6).value = res

Apart from that, I think you would get more answers if you would ask simple questions like "how to keep leading zeros when converting hex to binary" or something like that. The layout of your Excel, the fact that this is CAN-data and so on does not really matter.

answered Feb 18 '20 at 09:34

MSpiller

3,500
2
12
24

Thank you for your reply, I am still a beginner here. Therefore, I tried to fit in the information I thought is important. I will take the feedback when asking a question in the future. – Nezuko Feb 19 '20 at 00:43
I tried using your implementation suggestion but got a memory error. When I tried it on a single `inputHex ="0000000001940400"` then the result `res=0000000000000000000000000000000000000001100101000000010000000000` was not correct due to too many zero's in the beginning (it should be 31 zeros but there are 39 zeros). Do you know the reason behind that? – Nezuko Feb 19 '20 at 00:48
Why 31 zeros? There are 9 zero nibbles, which make up 9*4 = 36 zeros plus 3 zeros for the binary representation of 1 = 0001. Makes 39 zeros. – MSpiller Feb 19 '20 at 07:53

How to append the first zero's of HEX to the BIN string?

1 Answers1