CAN decoding with Google Sheets

Ask for and share advice on using the PicoScope kit to fix Heavy Duty and Off Highway machinery here
Post Reply
ben.martins
Pico Staff Member
Pico Staff Member
Posts: 550
Joined: Tue May 16, 2017 1:02 pm

CAN decoding with Google Sheets

Post by ben.martins »

This article follows on from the many wonderful discussions here on the forum regarding CAN decoding.

Recently I was asked to take a look at a mobile crane which had an intermittent warning on the dash for AdBlue being incorrect. This wasn’t present when starting the crane up from cold and took around 30mins of running before the warning appeared. On a key cycle the warning was gone again.
Fault.jpg
Picking up from the following forum post, topic22993.html, where we were looking at an AdBlue level and using the serial decoding tool, we can calculate the level according to the sensor output. Using the same PDF we can also see the quality.

With AdBlue, or DEF, there is a magic concentration that it should be at in order for the chemistry to work correctly once it is injected into the exhaust gas stream. This number is 32.5% Urea where water makes up the other 67.5%. One tool to determine this is a refractometer but what if this is correct? Could the sensor be sending out incorrect data which leads to this warning on the instrument cluster.

As seen in the other forum post, we can decode the data to determine the values and we will apply the same techniques but then look to export the data to CSV and see what else we can do with it. We touch on this process in yet another forum post here - topic22271.html. A lot of this post features the old software which didn’t have the J1939 decoder. Safe to say, because of this decoder, a lot of this process is improved.

Before we get started, we must remember that Pico isn’t a replacement for a scan tool or a CAN decoder. PicoScope can be used to complement the other tools by capturing details the others can’t. In this case when connecting to the DLC connector, the serial tool was able to see the machine and what was connected but unfortunately we were unable to see any of the emission data. Hooking up the PicoScope to the CAN H and CAN L, we could see that it was most likely a J1939 network due to the machine type and the 29 bit ID. What was odd is that with just a quick look at the source address with the J1939 decoder, we can see an address associated with an emission controller. In this case it may well be the operator of the scan tool, me, not seeing the right menu but we tried a number of options but still couldn’t get what we needed. What was also interesting is that despite the warning on the instrument panel, we weren’t seeing any fault codes present for the aftertreatment system.

Connecting Pico to the CAN H and CAN L at the DLC we could see what was available to the the serial tool.
Pico decode 1.png
Straight away though and on the first buffer we can see a source address A3. Looking through the PDF- https://www.ssi-sensors.com/perch/resou ... ulc-an.pdf, A3 is the source address for the AdBlue/DEF sensor. Using the filtering functions within Pico we can highlight those that belong to A3.
Pico decode 2.png
Here we can see all the messages that have come from source address A3 which we expect to be our DEF sensor. According to the PDF and also the www.isobus.net website, this is the address for Catalyst Fluid Sensor. Still using the PDF we can identify that the second byte 83 can be used to determine the concentration of the DEF. By converting 83 to a decimal, 131, and then applying the scaling of 0.25 we come to a figure of 32.75%.

Now, this isn’t exactly a problem as it allows +-3% for accuracy and given the warning was not present, we can say that this value is within the limits and shouldn’t throw a fault code. The vehicle was left running for a bit and we reviewed the concentration value it had shifted up one to 84 HEX = 132 DEC = 33%. If that was to continue increasing then we could get to 3% which may bring the light on. Unfortunately, as with all intermittent faults, it did not reappear for the rest of the time we were looking at the crane and ultimately we couldn’t wait around in case it did go wrong.

Question is, could we determine anything else from this CAN data and if so is there a better way to present it.

As described in the Smart Sensor post, we can export the decoded data to CSV. This allows us to open in Excel along with Google Sheets, which for the following I’ve found to be a lot easier!

So to begin with we need to export our decode table to CSV. Clicking Export in the top right of the decode table will open a new window asking you where you would like to save the file and what filename you would like to use. I try to keep it easy to remember but include what format the decode table is in HEX, DEC, BIN or ASCII. Click Save and this will now store all the CAN data onto a CSV file.


Once you have this file saved and in a location that is easy to find we then need to open it. If you have Excel already installed, then simply double click the file and it should open. However, I would like to show you Google Sheets. So, head to an internet browser and search for Google Sheets. You will need a Google account to use sheets but they are free and easy to set up.

Once logged in you now need to create a new sheet by clicking the + symbol at the bottom right of the screen.
Sheets1.png
With a new sheet open you have the option to Open or Import. Given we have a sheet already open we can go right ahead and select import. When importing, you will want to hit the Upload tab which will allow you to bring in the files from your PC. Important, ensure you untick the section which will convert text to numbers. Where we are working with HEX values we want to ensure it is imported as it should be.
Sheets2.png
Click Import data to load the file into Google Sheets.

You may find that the data isn’t in the correct ascending order. If this is the case, select all the columns by highlighting column A and then hold down shift on the keyboard and then click the final column which is likely to be Q. With the columns highlighted click the add filter icon.
Sheets3.png
This will then add a drop down icon for each column where you will be able to select Sort A to Z, which will then sort the table accordingly. This will put all the data into time order.
Sheets4.png
As most PGN values are listed in Decimal format, the next step is to convert the ID2 PGN into Decimal. In row R Label the column PGN in DEC. In cell R2 type the following:

=HEX2DEC(F2)

When pressing Enter, you will hopefully see a message to Autofill the rest of the column as shown below.
Sheets5.png
Alternatively, as in Excel, hover the cursor over the bottom right corner of the cell you would like to autofill which should turn to a bold +. Double click and it should auto fill.

You can then use the same process to complete the Source address column. In order to Auto fill, there has to be data in the column to the left though so please bear this in mind if trying to keep space between columns. You can always tidy up after you have the data.

Next is the fun part as we want to separate out the PGN’s into their own worksheets. At the bottom of the screen you will see a + symbol which when clicked will create a new worksheet.
Sheets6.png
Rename the worksheet the PGN of choice. For this example we will use 61444.

From the previous J1939 decoding work we have done on the forum using Excel, the one downside was the large formula required in order to pull in data from another sheet. Google Sheets massively simplifies this process by using the function Filter.

On the newly named worksheet for 61444, select a cell eg A2 and type the PGN - 61444 in this case. Underneath this cell in A3 type start with =FILTER(

When looking at the description for this function it states that it returns a filtered version of the source range, returning only rows and columns that meet the specified conditions.
Sheets7.png
From here we just need to set up the formula so that it meets the criteria. The range in this case will be the column with the data from our decoder as this indicates the column to return if the conditions for the filter are met.

=FILTER('Data from Pico in HEX'!K:K,

Next is condition 2 which is where we set the conditions. In this case we want the function to look through the column with the PGN in decimal format as this is the most used format with J1939. If using another protocol you could just use the ID column from the decode table.

=FILTER('Data from Pico in HEX'!K:K,'Data from Pico in HEX'!R:R

Finally we need to set the value we would like to filter which in this case is 61444 which is in cell A2. The completed formula looks like this:

=FILTER('Data from Pico in HEX'!K:K,'Data from Pico in HEX'!R:R=A2)

This formula can now be copied to create new sheets and set up other ID’s to filter with.

You should see that the column will ask if you would like to automatically fill in the rows based on the data it is filtering. Alternatively, if this option doesn’t appear, hover over the bottom right hand corner of the cell to show a bold crosshair and double click to autofill.
Sheets8.png
You are now seeing the filtered data from the decode table which has the ID/PGN 61444. Now, if you know the byte positions, scale and offset values for the PGN, you can start to extract them using the MID function and then perform the HEX2DEC value. This is explained in other forum posts but below you can see the completed formula used to extract bytes 4 and 5 from PGN 61444 which is the engine RPM. To help visualise this further, I’ve then added in a graph.
Sheets9.png
The same technique can be applied therefore to another known PGN, the DEF sensor which we know is 64923. By copying the formula over for the filter and adding 64923 into A2, it will now pull in all the data cells which are in the row for 64923. Again, as the position scale and offset are known for the temperature and concentration levels, I’ve added those in, although it isn’t quite as exciting as the engine speed!
Sheets10.png
What’s nice about Google sheets is it is very easy to share - click here to view the https://docs.google.com/spreadsheets/d/ ... sp=sharing. I have removed some permissions as I don’t want everyone uploading and changing the data but hopefully it shows what is possible.

I appreciate that some of this repeats from what has been mentioned in some of the other posts but hopefully you can see that combining software such as Pico and Google Sheets, there is more to gain from the decoder.

I hope this helps

Ben

Post Reply