Using a Database to Decode J1939 Messages

This page is to share a Python script written to decode a CAN data dump from a J1939 Network. The goal of the program is to decode messages on the J1939 communications bus present in the vehicle.

Assumptions

This page is written with the following assumptions:

Windows Calculator FEF1   Windows Calculator

Data Acquisition

Before decoding data, we need to know where it came from. J1939 data is present on the 9-pin cab diagnostic port in pins C and D shown below. Pinouts for this connector are available from DG Technologies in their Industry Connector Reference Guide.

9-pin

The data was acquired on an SD card with a Vector CANCaseXL Log device.

Vector

The data is saved in a raw binary format (.XLX file) on the SD card. This file can be converted using the CANCase XL Config Software.

Config

Select the correct log file that you are interested in and press Convert...

Logging File Conversion

Pressing the Advanced... button gives the dialog to let us know what format the data will be in.

Log File Settings

Pressing OK and converting the file will produce a comma separated values text file. Opening the data file (DDEC10 20321029.xls) in a text editor (like Notepad ++) gives the first lines shown in the screen capture below. Notice this is actually a comma separated value table and changing the extension from .xls to .csv will make it open directly in MS Excel. To use the file in Vector's CANAlyzer software, it must be exported as a binary log file (.blf) and can be downloaded as DDEC10 20321029.blf.

First Few Lines

The first 3 lines are header information and do not contain data of interest. Starting on line four, we create the following table to mark the element positions:

Data Positions in Raw Log File
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16
2469.217398 0.000628 On/Off CAN (4a8) RX ch:1 Dlc:8 idx: 18eeff19 data: fc 00 20 03 00 15 02 00

The data of interest are the time stamp in position 0, the DLC in position 5, the ID in Position 7, and the data in positions 9-16.

Now that we know the data and its format, we can import and parse the data using Python. The Python distribution used for this work is WinPython 2.7.3 for 64-bit Windows 7 because it contains the scientific libraries.

Importing the Data

In a new Python script file, open and parse the data log with the following commented code that turns the log file into data Python data structures.

fileHandle=open('DDEC10 20321029.xls','r') #Opens the log file of interest
datalines=fileHandle.readlines() #reads the logfile contents into memory as a list
fileHandle.close()
#print(datalines[0:10]) #shows the first few entries in the list. These entries should match the log file

#Parse the log file and create data structure to hold the data for further processing
DLCs={} #Data length Code Dictionary that will be used to find all IDs.
timeList=[] #store the timestamp from the log file
IDList=[] #store the IDs in sequence
rawData=[] #Store the data fields in sequence

for line in datalines[3:]: #iterate through the entire file starting at line 3
    elements=line.split(',') #Separate the entries by commas

    timeList.append(float(elements[0])) #convert the first entry into a floating point number and add it to the list       
    
    ID=elements[7] #extract the text associated with the CAN identifier
    IDList.append(ID) #add the ID to a list       
    
    DLCText=elements[5].split(':') #The DLC text field has a colon, so separate the label from the text
    DLC=int(DLCText[1]) #Get the data length code as an integer
    DLCs[ID]=DLC #Store the DLC in a dictionary with the ID as a key. This will ensure only unique IDs are used as keys.
    
    rawData.append(elements[9:-1]) #store the data field as a list of text strings
  
IDs=DLCs.keys() #the keys in the DLCs dictionary are all the unique IDs that have not been duplicated.

print('\nID\tDLC') #Print the heading of a table with a preceding blank line
for ID in sorted(DLCs.keys()): #iterate through all the sorted dictionary keys
    print('%s\t%i' %(ID,DLCs[ID])) #display the dictionary contents
    
print('Number of Unique IDs: %i' %len(IDs)) #display the total number of messages

This code should produce the following output:

ID	DLC
10f01a01	8
10fda301	8
10fe6f00	8
10ff2121	8
14f00031	8
14f00131	8
14fd3e00	8
14fda421	8
14fef031	8
14fef121	8
14fef131	8
14ff0121	8
14ff0221	8
14ff2321	8
14ff3131	8
18e00019	8
18e00031	8
18e0ff17	8
18ea0017	3
18ea0025	3
18ea3a19	3
18eaff17	3
18eafffe	3
18eeff00	8
18eeff01	8
18eeff0f	8
18eeff17	8
18eeff19	8
18eeff25	8
18f0000f	8
18f00100	8
18f0010b	8
18f00f3d	8
18fd0417	8
18fd0517	8
18fd0700	8
18fd6601	8
18fd7c00	8
18fd8c3d	8
18fd9401	8
18fdb23d	8
18fdb300	8
18fdb400	8
18fdd001	8
18fe5600	8
18fe6901	8
18fe9701	8
18fe9801	8
18fe9901	8
18fe9a01	8
18fea400	8
18febd00	8
18febf0b	8
18fec100	8
18fec117	8
18feca00	8
18feca01	8
18feca17	8
18feca3d	8
18fedb01	8
18fedd01	8
18fedf00	8
18fee000	8
18fee400	8
18fee500	8
18fee900	8
18feee00	8
18feef00	8
18fef000	8
18fef100	8
18fef117	8
18fef200	8
18fef500	8
18fef521	8
18fef600	8
18fef621	8
18fef700	8
18fef717	8
18fef719	8
18fef721	8
18fef817	8
18fefc17	8
18feff00	8
18feff47	8
18ff1919	8
18ff1b19	8
18ff2221	8
18ff2421	8
18ff7400	8
18ffa03d	8
18ffa13d	8
18ffa23d	8
18ffa33d	8
18ffa43d	8
18ffa53d	8
18ffaa01	8
18ffab01	8
18ffac01	8
1ce8ff00	8
1cebff00	8
1cebff0f	8
1cecff00	8
1cecff0f	8
1cfe9201	8
cf00300	8
cf00400	8
cf00421	8
cf00a01	8
Number of Unique IDs: 108

Now the data is in Python data structures within memory. The next step is to make meaning from it using the J1939 Standard.

Parsing the data

There are two things that are needed to make meaning of the data. First, a set of rules that tell us how to decode the information, and second, the logic to do so. We'll start with the rules (standards) to decode the data.

Building a J1939 Database

A Companion Spreadsheet for SAE J1939 is available in Excel format and can be used to build a relational database. The material from SAE is copyrighted, so it is not available for download on this site.

For this work, we chose to use SQLite Version 3, so you'll need to download and install the executable. Python already has an SQLite3 library that we will used. We need to translate the data from the companion spreadsheet into an SQLite database. Fortunately, both Excel and SQLite can work with comma separated value tables. The process of creating the database is as follows:

  1. Download and save the SAE spreadsheet into a directory. Then save each tab as a .csv file. The directory may look something like this:
    Initial Directory
  2. Open a command line (cmd.exe) and change directories to where the data is contained and enter the command "sqlite3 J1939.db". This will create a new database that needs content.
  3. The database is composed of tables, which need to be created. Using the Companion Spreadsheet as inspiration, we can create a Source Address table:
    CREATE TABLE SourceAddresses (ID INT, Name STRING, Notes STRING, DateModified STRING, Status STRING);
    To populate the table, we import the csv file saved from the spreadsheet.
    .mode csv
    .import SourceAddresses.csv SourceAddresses
    Now we can check the entry:
    SELECT * from SourceAddresses where ID=0;
    which shows:
    0,"Engine #1","The #1 on the Engine CA is to identify that this is the first PA
    being used for the particular function, Engine. It may only be used for the NAME
     Function of 0, Function Instance 0, and an ecu instance of 0, which is commonly
     know as the first engine.","",Published
    and suggests a successful import.
  4. Other tables can be created using the following commands:
    CREATE TABLE Manufacturers (ID INT,MANUFACTURER,LOCATION,DateLastModified,Status);
    CREATE TABLE SLOTS (SLOTIdentifier INT,SLOTName STRING,SLOTType STRING,Scaling STRING,Range STRING,Offset STRING, Length STRING, Status STRING ,DateModified STRING);
    CREATE TABLE SPNandPGN (PGN INT,ParameterGroupLabel,PGNLength,TransmissionRate,Acronym,pos,SPNlength INT,SPN INT,Name,Description,DataRange,OperationalRange,Resolution,Offset,Units,DateSPNAddedToPGN,StatusOfSPNAdditionToPGN,DateSPNModified,StatusSPN,SPNDoc,PGNDoc,BitField); 
    followed by the corresponding import statements:
    .import SPNandPGN.csv SPNandPGN
    .import Slots.csv SLOTS
    .import Manufacturers.csv Manufacturers
    The database is now full and is about 4 Mb in size.
  5. Quit the command line program by typing .quit. The entire record of creating and checking the database is shown below.
    Command
  6. Now that the database is built, we can access and use it from Python using the sqlite3 library. Add the following lines to the Python code:
    import sqlite3 #import the sqlite 3 extensions for Python
    db=sqlite3.connect('J1939.db')
    db.text_factory = str # This command enables the strings to be decoded by the sqlite commands
    cur = db.cursor()
    
    
    
    
    db.close()
    The blank space between cur=db.cursor() and db.close() is for adding the code needed to interpret the J1939 identifiers.

Decoding the J1939 Identifier

The J1939 header has the following elements as illustrated in SAE J1939:

J1939Header

The ID can be broken into 3 groups of 8 (24bits) and a group of 5. The breakdown is as follows (from the end of the ID):

Counting from the rear is better than the front because the text representation of the priority often leaves off a leading zero, which will alter the position counts in the iD text field. Since strings are indexable in Python, the following code will loop through the IDs and break down the data elements in the ID. This code should be placed in the blank space from the previous step.

SA_Name={}
SA_Note={}
for IDText in sorted(IDs):
    SAText=IDText[-2:]
    SA=int(SAText,16) #Convert the hex string into an integer
    PFText=IDText[-6:-4]
    PF=int(PFText,16)
    if PF <240: #PDU1 format
        DA=int(IDText[-4:-2],16)
        PSText='00'
        PS=DA
    else:        
        PSText=IDText[-4:-2]
        PS=int(PSText,16)
        DA=255 #Broadcast
    PriorityText=IDText[-8:-6]
    Priority=int(PriorityText,16)>>2 #bit shift the priority integer by 2 to account for the DP and EDP fields.

    print('\nMessage ID: %s' %IDText)
    print('Priority (Hex): %s' %PriorityText)
    print('J1939 Priority: %i' %Priority)
    print('PDU Format (PF) in hex: %s and in decimal: %i' %(PFText,PF))
    print('PDU Specific (PS) in hex: %s and in decimal: %i' %(PSText,PS))
    print('Source Address in hex: %s and in decimal: %i' %(SAText,SA)) 
  
    PGNText=PFText+PSText
    PGN=int(PGNText,16)
    print('Parameter Group Number (PGN): %i' %PGN)

An example output from the print statements is as follows:

Message ID: cf00300
Priority (Hex): c
J1939 Priority: 3
PDU Format (PF) in hex: f0 and in decimal: 240
PDU Specific (PS) in hex: 03 and in decimal: 3
Source Address in hex: 00 and in decimal: 0
Parameter Group Number (PGN): 61443

Once the J1939 ID is broken into its elements, we can decode it based on the data from the database we created. Since we have the database cursor defined in the Python script, we can add the following line:

    PGNData=cur.execute('SELECT ParameterGroupLabel,TransmissionRate,Acronym FROM SPNandPGN WHERE PGN=?', [PGN]).fetchone()
    try:
        print('Parameter Group Label: %s' %PGNData[0])
    except TypeError:
        pass
    print('Transmission Rate: %s' %PGNData[1]) 
    acronym=str(PGNData[2])
    print('Acronym: %s' %acronym)

The .fetchone() method pulls out a single record for the elements of the Parameter Group Number (PGN) since the sqlite3 cursor provides an iteratable object. the next block uses the Python Exception handler to display the message. If the data from the database is not present, then nothing is shown. Example output from this code is shown below. The data from the database will show the reader the meaning of the contents of the message.

Message ID: 18fef117
Priority (Hex): 18
J1939 Priority: 6
PDU Format (PF) in hex: fe and in decimal: 254
PDU Specific (PS) in hex: f1 and in decimal: 241
Source Address in hex: 17 and in decimal: 23
Parameter Group Number (PGN): 65265
Parameter Group Label: Cruise Control/Vehicle Speed 1 Transmission Rate: 100 ms
Acronym: CCVS1

Next we can add the logic to figure out the names of the electronic controllers for a given source address:

    #Source Addresses
    if SA < 90 or SA > 247:
        sourceAddressData=cur.execute('SELECT Name,Notes FROM SourceAddresses WHERE ID=?', [SA]).fetchone()
        SA_Name[SA]=sourceAddressData[0]
        SA_Note[SA]=sourceAddressData[1]
    elif SA > 159 and SA <248:
        sourceAddressData=cur.execute('SELECT Name,Note FROM SourceAddressesOnHighWay WHERE SA=?', [s]).fetchone()
        SA_Name[SA]=sourceAddressData[0]
        SA_Note[SA]=sourceAddressData[1]
    else:
        SA_Name[SA]='SAE Future Use'
        SA_Note[SA]='Used SAE future Use or for dynamic address assignment'
    
    print('Source Controller: %s' %SA_Name[SA])

Two dictionaries (SA_Name and SA_Note) are used to store Source Address data so it is not duplicated. These dictionaries need to be initialized before the iterative loop by setting them equal to an empty dictionary. The output from the above section adds the following lines:

Message ID: cf00421
Priority (Hex): c
J1939 Priority: 3
PDU Format (PF) in hex: f0 and in decimal: 240
PDU Specific (PS) in hex: 04 and in decimal: 4
Source Address in hex: 21 and in decimal: 33
Parameter Group Number (PGN): 61444
Parameter Group Label: Electronic Engine Controller 1
Transmission Rate: engine speed dependent
Acronym: EEC1
Source Controller: Body Controller}

Parsing the Message Payload

Messages are typically 8 bytes long in J1939. To determine what those bytes and bits mean in the message, the J1939 standard defines Suspect Parameter Numbers (SPNs). The database contains the meaning of the SPNs and where the data is located in a J1939 message. For each PGN, which is defined by the message ID, there may be several SPNs. These SPN's are typically defined in J1939-71. An entry in that standard is as follows:

J1939Entry

The entry for Wheel-Based Vehicle Speed (SPN 84) in the Companion Spreadsheet is as follows:

SPN 84 in Excel

To access the database and determine what SPN's are available, we can use the following Python code:

    print('The Following SPNs are available in the message:')
    for SPNData in cur.execute('SELECT SPN,Name,Units,Offset,Resolution FROM SPNandPGN WHERE PGN=?', [PGN]):       
        try:
            SPN=int(SPNData[0])
        except ValueError:
            SPN=-1                
        
        Name=str(SPNData[1])
        Units=str(SPNData[2])
        
        try:
            Offset=float(SPNData[3])
        except ValueError:
            Offset=0
            
        res=SPNData[4].split(' ')
        res=res[0].split('/bit')
        res=res[0].split('/')
        try:
            if len(res)==2:
                Resolution=float(res[0])/float(res[1])
            else:
                Resolution=float(res[0])
        except ValueError:
            Resolution=0
        
        SPNs[acronym]=[SPN,Name,Units,Offset,Resolution]
        #print(SPNData[4])
        print('SPN: %i, Name: %s, Unit: %s, Offset: %g, Resolution: %g' %(SPN,Name,Units,Offset,Resolution)) 
db.close()     

The code above makes use of Python's Exception handling to deal with entries in the database that do not match a certain data type. For example, there may be no entry in the Offset column if the SPN describes a bit field. The output from the above code now contains the SPN data shown below for the CCVS message:

Message ID: 18fef117
Priority (Hex): 18
J1939 Priority: 6
PDU Format (PF) in hex: fe and in decimal: 254
PDU Specific (PS) in hex: f1 and in decimal: 241
Source Address in hex: 17 and in decimal: 23
Parameter Group Number (PGN): 65265
Parameter Group Label: Cruise Control/Vehicle Speed 1
Transmission Rate: 100 ms
Acronym: CCVS1
Source Controller: Instrument Cluster #1
The Following SPNs are available in the message:
SPN: 69, Name: Two Speed Axle Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 70, Name: Parking Brake Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 1633, Name: Cruise Control Pause Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 3807, Name: Park Brake Release Inhibit Request, Unit: bit, Offset: 0, Resolution: 4
SPN: 595, Name: Cruise Control Active, Unit: bit, Offset: 0, Resolution: 4
SPN: 596, Name: Cruise Control Enable Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 597, Name: Brake Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 598, Name: Clutch Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 599, Name: Cruise Control Set Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 600, Name: Cruise Control Coast (Decelerate) Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 601, Name: Cruise Control Resume Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 602, Name: Cruise Control Accelerate Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 86, Name: Cruise Control Set Speed, Unit: kph, Offset: 0, Resolution: 1
SPN: 976, Name: PTO Governor State, Unit: bit, Offset: 0, Resolution: 32
SPN: 527, Name: Cruise Control States, Unit: bit, Offset: 0, Resolution: 8
SPN: 968, Name: Engine Idle Increment Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 967, Name: Engine Idle Decrement Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 966, Name: Engine Test Mode Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 1237, Name: Engine Shutdown Override Switch, Unit: bit, Offset: 0, Resolution: 4
SPN: 84, Name: Wheel-Based Vehicle Speed, Unit: kph, Offset: 0, Resolution: 0.00390625

Creating a Results Database

Once the IDs have been parsed to determine all the available data, we can build a results database that contains the translated messages for use later. The database is a way to hold data that can be extracted using SQL commands. We can use this for plotting dynamic data. The database is constructed using the following Python code:

print('Loading Values into Database. Please wait...')        
import os
filename='RunData.db'
if os.path.isfile(filename): 
    os.remove(filename) #delete the database if it already exists. This prevents errors for duplicate tables.
    
dataDB=sqlite3.connect(filename)
dataDB.text_factory = str # This command enables the strings to be decoded by the sqlite commands
cursor = dataDB.cursor()
cursor.execute('CREATE TABLE dataValues (time FLOAT,ID STRING, PF INT, PS, INT, DA INT, PGN INT, SA INT, Priority INT, Acronym STRING, Meaning STRING, Units STRING) ')

for SPN in sorted(SPNs.keys()):
    #print('%i\t%s' %(SPN,SPNs[SPN][0]))
    cursor.execute('ALTER TABLE dataValues ADD COLUMN SPN%i' %SPN) #This adds only the SPNs that are in the data stream.

Opening the new database in the command line and examining its schema gives the following result:

New Schema

Once the database table is created, we can add data using the insert command. The following code inserts only the wheel-based vehicle speed from the data set. This should be expanded and generalized to use the J1939 database and accommodate any data set.

for t,ID,payload in zip(timeList,IDList,rawData):
    #Here is an example that needs to be generalized to fill the results database    
    if ID[-6:]=='fef100':
        SPN=84        
        position=2
        Resolution=0.00390625
        Offset=0
        binary=int(payload[position]+payload[position-1],16) #reverse byte order
        value=Resolution*binary+Offset
        
        if value>250:
             value=9999
                
        meaning='tailshsft Speed'
        cursor.execute('INSERT INTO dataValues(time,SPN%i) VALUES(%g,%g)' %(SPN,t,value) )
        
dataDB.commit()
dataDB.close()

With engineering data (i.e. speed in km/h) now in the database, we can save the interpreted data for later use. One of those uses would be to visualize the data by plotting it. The code below will create a graph of the Wheel-Based Vehicle Speed in kph.

import matplotlib.pyplot as plt
dataDB=sqlite3.connect(filename)
dataDB.text_factory = str # This command enables the strings to be decoded by the sqlite commands
cursor = dataDB.cursor()
speedHistory=cursor.execute('SELECT time,SPN84 FROM dataValues').fetchall()
dataDB.close()

time=[]
speed=[]
for entry in speedHistory:
    time.append(entry[0]-speedHistory[0][0])
    speed.append(entry[1])
f1=plt.figure(1)
plt.plot(time,speed)
plt.xlabel('Time(s)')
plt.ylabel('Wheel-Based Vehicle Speed, kph')
plt.legend(['Source: Engine #1',])
plt.grid()
ttl=plt.title('Data from %s showing SPN 84 from the J1939 CCVS Message' %filename)
ttl.set_y(1.03) #pushes the title up as to not crowd the axis labels
plt.savefig('Wheel-Based Vehicle Speed.png',dpi=600,transparent=True, bbox_inches='tight', pad_inches=0.05)
plt.savefig('Wheel-Based Vehicle Speed.pdf')

The plot generated from these commands is saved as Wheel-BasedVehicleSpeed.pdf and a low resolution PNG, which is shown below.

Speed graph

The entire Python Code to accompany this page is available as ParseLogFile.py with the logfile DDEC10 20321029. The output from all the print commands are contained in IDAnalysisOutput.txt.

Summary

This page shows how to implement a database system to interact and store engineering data from J1939 data logs. It is motivated by a specific example, but the use of a database system enables a transportable methodology as access to the SQLite3 databases is language agnostic. In other words, if the data from data log is interpreted and stored in a database, it can be accessed later using any technique desired. This strategy may have commercial applications.