ABOUT ME

-

Today
-
Yesterday
-
Total
-
  • DoIP asc to xlsx
    diagnostic 2025. 4. 28. 23:56

    DoIP asc 데이터 파일을 xlsx로 변환하기

    • DoIP 통신 데이터가 저장된 asc 파일을 읽는다.
    • 이더넷 메시지가 있는 줄만 추출한다.
    • 각 줄을 구성 요소별로 분리한다.
    • 각 줄을 행으로 요소를 열로 하여 xlsx에 저장한다.
    # import
    from pathlib import Path
    import pandas as pd
    # asc 파일을 줄 별로 읽어오기
    dir_asc = Path('.').absolute()/'asc'
    file_asc = dir_asc/'obd_ethernet_log.asc'
    with open(file_asc, "r") as file:
        lines = file.readlines()
    # asc 파일의 내용을 본다.
    lines[:25]
    ['date Fri Sep 13 02:57:32.035 pm 2024\n',
     'base hex  timestamps absolute\n',
     'internal events logged\n',
     '// version 16.2.0\n',
     '// Measurement UUID: 8f6e9e0a-7641-444b-9606-158020c610f2\n',
     'Begin TriggerBlock Fri Sep 13 02:57:32.035 pm 2024\n',
     '   0.000000 Start of measurement\n',
     '   0.000184 ETH 1 STAT Link:Link_up Ports:ECU\n',
     '   0.000464 ETH 1 STAT Link:Link_up LinkSpeed:100Mbit/s Physical:IEEE802.3 Duplex:Full MDI:Crossover Connector:RJ45 Ports:Port5\n',
     '   0.000559 ETH 1 STAT Link:Link_up LinkSpeed:1000Mbit/s Physical:IEEE802.3 Duplex:Full MDI:Direct Connector:RJ45 BRClockMode:Slave Ports:Port6\n',
     '   0.089961 ETH 1 BUSSTATISTIC HwRxPkts:0 HwTxPkts:0 HwRxError:0 HwTxError:0 HwRxBytes:0 HwTxBytes:0 HwRxNoBuffer:0 HwSQIValue:6 Ports:ECU\n',
     '   0.100263 ETH 1 BUSSTATISTIC HwRxPkts:0 HwTxPkts:0 HwRxError:0 HwTxError:0 HwRxBytes:0 HwTxBytes:0 HwRxNoBuffer:0 HwSQIValue:6 Ports:Port5\n',
     '   0.100373 ETH 1 BUSSTATISTIC HwRxPkts:0 HwTxPkts:0 HwRxError:0 HwTxError:0 HwRxBytes:0 HwTxBytes:0 HwRxNoBuffer:0 HwSQIValue:6 Ports:Port6\n',
     '   0.189962 ETH 1 BUSSTATISTIC HwRxPkts:0 HwTxPkts:0 HwRxError:0 HwTxError:0 HwRxBytes:0 HwTxBytes:0 HwRxNoBuffer:0 HwSQIValue:6 Ports:ECU\n',
     '   0.200263 ETH 1 BUSSTATISTIC HwRxPkts:0 HwTxPkts:0 HwRxError:0 HwTxError:0 HwRxBytes:0 HwTxBytes:0 HwRxNoBuffer:0 HwSQIValue:6 Ports:Port5\n',
     '   0.200374 ETH 1 BUSSTATISTIC HwRxPkts:0 HwTxPkts:0 HwRxError:0 HwTxError:0 HwRxBytes:0 HwTxBytes:0 HwRxNoBuffer:0 HwSQIValue:6 Ports:Port6\n',
     '   0.257025  ETH 1 Rx     46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D FCS:734d6816 Ports:Port6 Sim:0\n',
     '   0.257025  ETH 1 Tx     46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D Ports:ECU Sim:0\n',
     '   0.257032  ETH 1 Tx     46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D FCS:734d6816 Ports:Port5 Sim:0\n',
     '   0.272157  ETH 1 Rx     9d:333300010002A81374BD389D86DD60054E1700671101FE800000000000004C705F11449FCF51FF0200000000000000000000000100020222022300672D4501D30E160008000202BF0001000E000100012A621DD5A81374BD389D0003000C0DA81374000000000000000000270011000F4445534B544F502D4B524E4E5330370010000E0000013700084D53465420352E30000600080011001700180027 FCS:40a51d09 Ports:Port6 Sim:0\n',
     '   0.272157  ETH 1 Tx     9d:333300010002A81374BD389D86DD60054E1700671101FE800000000000004C705F11449FCF51FF0200000000000000000000000100020222022300672D4501D30E160008000202BF0001000E000100012A621DD5A81374BD389D0003000C0DA81374000000000000000000270011000F4445534B544F502D4B524E4E5330370010000E0000013700084D53465420352E30000600080011001700180027 Ports:ECU Sim:0\n',
     '   0.272170  ETH 1 Tx     9d:333300010002A81374BD389D86DD60054E1700671101FE800000000000004C705F11449FCF51FF0200000000000000000000000100020222022300672D4501D30E160008000202BF0001000E000100012A621DD5A81374BD389D0003000C0DA81374000000000000000000270011000F4445534B544F502D4B524E4E5330370010000E0000013700084D53465420352E30000600080011001700180027 FCS:40a51d09 Ports:Port5 Sim:0\n',
     '   0.289962 ETH 1 BUSSTATISTIC HwRxPkts:0 HwTxPkts:2 HwRxError:0 HwTxError:0 HwRxBytes:0 HwTxBytes:251 HwRxNoBuffer:0 HwSQIValue:6 Ports:ECU\n',
     '   0.300264 ETH 1 BUSSTATISTIC HwRxPkts:0 HwTxPkts:2 HwRxError:0 HwTxError:0 HwRxBytes:0 HwTxBytes:251 HwRxNoBuffer:0 HwSQIValue:6 Ports:Port5\n',
     '   0.300375 ETH 1 BUSSTATISTIC HwRxPkts:2 HwTxPkts:0 HwRxError:0 HwTxError:0 HwRxBytes:251 HwTxBytes:0 HwRxNoBuffer:0 HwSQIValue:6 Ports:Port6\n']
    # 분석 대상 라인들만 추출한다.
    
    lines_oi = []   # lines of interest
    elements_oi = []    # elements of interest
    for line in lines:
        elements = line.strip().split()
    
        interesting = True
    
        # 첫 element가 타임스탬프가 아니면 분석 대상이 아니다. 
        try:
            ts = float(elements[0])
        except ValueError:
            interesting = False
    
        # Start, STAT, BUSSTATISTIC가 포함된 라인은 분석 대상이 아니다. 
        for element in elements:
            if ('STAT' in element) or ('BUSSTATISTIC' in element) or ('Start' in element):
                interesting = False
                break
    
        # 분석 대상을 lines_oi에 추가한다.
        if interesting:
    
            # 3번째 element가 Tx인 경우, FCS가 없는 경우가 있다. 이 경우, FCS를 추가한다.
            if elements[3] == 'Tx':
                fcs_found = False
    
                for element in elements[4:]:
                    if 'FCS' in element:
                        fcs_found = True
                        break
    
                if not fcs_found:
                    elements.insert(5, 'FCS:00000000')
    
            elements_oi.append(elements)
            lines_oi.append(line.strip())
    # linses_oi의 내용을 본다.
    lines_oi[:5]
    ['0.257025  ETH 1 Rx     46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D FCS:734d6816 Ports:Port6 Sim:0',
     '0.257025  ETH 1 Tx     46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D Ports:ECU Sim:0',
     '0.257032  ETH 1 Tx     46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D FCS:734d6816 Ports:Port5 Sim:0',
     '0.272157  ETH 1 Rx     9d:333300010002A81374BD389D86DD60054E1700671101FE800000000000004C705F11449FCF51FF0200000000000000000000000100020222022300672D4501D30E160008000202BF0001000E000100012A621DD5A81374BD389D0003000C0DA81374000000000000000000270011000F4445534B544F502D4B524E4E5330370010000E0000013700084D53465420352E30000600080011001700180027 FCS:40a51d09 Ports:Port6 Sim:0',
     '0.272157  ETH 1 Tx     9d:333300010002A81374BD389D86DD60054E1700671101FE800000000000004C705F11449FCF51FF0200000000000000000000000100020222022300672D4501D30E160008000202BF0001000E000100012A621DD5A81374BD389D0003000C0DA81374000000000000000000270011000F4445534B544F502D4B524E4E5330370010000E0000013700084D53465420352E30000600080011001700180027 Ports:ECU Sim:0']
    # elements_oi의 내용을 본다.
    elements_oi[:5]
    [['0.257025',
      'ETH',
      '1',
      'Rx',
      '46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D',
      'FCS:734d6816',
      'Ports:Port6',
      'Sim:0'],
     ['0.257025',
      'ETH',
      '1',
      'Tx',
      '46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D',
      'FCS:00000000',
      'Ports:ECU',
      'Sim:0'],
     ['0.257032',
      'ETH',
      '1',
      'Tx',
      '46:333300000002A81374BD389D86DD6000000000103AFFFE800000000000004C705F11449FCF51FF0200000000000000000000000000028500674D000000000101A81374BD389D',
      'FCS:734d6816',
      'Ports:Port5',
      'Sim:0'],
     ['0.272157',
      'ETH',
      '1',
      'Rx',
      '9d:333300010002A81374BD389D86DD60054E1700671101FE800000000000004C705F11449FCF51FF0200000000000000000000000100020222022300672D4501D30E160008000202BF0001000E000100012A621DD5A81374BD389D0003000C0DA81374000000000000000000270011000F4445534B544F502D4B524E4E5330370010000E0000013700084D53465420352E30000600080011001700180027',
      'FCS:40a51d09',
      'Ports:Port6',
      'Sim:0'],
     ['0.272157',
      'ETH',
      '1',
      'Tx',
      '9d:333300010002A81374BD389D86DD60054E1700671101FE800000000000004C705F11449FCF51FF0200000000000000000000000100020222022300672D4501D30E160008000202BF0001000E000100012A621DD5A81374BD389D0003000C0DA81374000000000000000000270011000F4445534B544F502D4B524E4E5330370010000E0000013700084D53465420352E30000600080011001700180027',
      'FCS:00000000',
      'Ports:ECU',
      'Sim:0']]
    # lines_oi를 파일로 저장한다. 위에서 한 데이터 처리를 다시 안 해도 되도록.
    file_new_asc = file_asc.with_suffix('.new.asc')
    with open(file_new_asc, "w") as file:
        for line in lines_oi:
            file.write(line + "\n")
    # elememts_oi를 DataFrame으로 변환하기
    # asc의 한 줄에는 8개의 element가 있다.
    # 0: ts, 1: type, 2: ch, 3: tx/rx, 4: data, 5: fcs, 6: port, 7: sim
    df = pd.DataFrame(elements_oi, columns=['ts', 'type', 'ch', 'tx_rx', 'data', 'fcs', 'port', 'sim'])
    df['ts'] = df['ts'].astype(float)
    df['ch'] = df['ch'].astype(int)
    # df의 내용을 본다.
    df.head(5)
      ts type ch tx_rx data fcs port sim
    0 0.257025 ETH 1 Rx 46:333300000002A81374BD389D86DD6000000000103AF... FCS:734d6816 Ports:Port6 Sim:0
    1 0.257025 ETH 1 Tx 46:333300000002A81374BD389D86DD6000000000103AF... FCS:00000000 Ports:ECU Sim:0
    2 0.257032 ETH 1 Tx 46:333300000002A81374BD389D86DD6000000000103AF... FCS:734d6816 Ports:Port5 Sim:0
    3 0.272157 ETH 1 Rx 9d:333300010002A81374BD389D86DD60054E170067110... FCS:40a51d09 Ports:Port6 Sim:0
    4 0.272157 ETH 1 Tx 9d:333300010002A81374BD389D86DD60054E170067110... FCS:00000000 Ports:ECU Sim:0
    # df를 xlsx로 저장한다. 
    df.to_excel(file_new_asc.with_suffix('.xlsx'), index=False)

     

     

    참고

    obd_ethernet_log.asc
    0.36MB
    obd_ethernet_log.new.xlsx
    0.04MB

     

     

    DoIP 메시지 분석 :: hsl's tsmaster 사용기     

    목차 :: hsl's tsmaster 사용기