1

Step by Step – Import a BusMaster Log in Excel

4 min read

In previous articles, we looked at taking a CANBus log using BusMaster. Getting useable information from the Logs can be a little bit daunting. In this article, we’ll take a step by step look at converting the BusMaster Log into a Microsoft Excel spreadsheet.

I apologise now if you’re already proficient with Excel, some aren’t, and importing and filtering are not common tasks for all. Hopefully, this step by step guide will help those with less experience with Excel and might be a memory jog for those that need it.

Step 1 – Importing a log file to Excel

Okay, first up open Excel and save your workbook somewhere you can find it again!.

Excel external data controls
In Excel, us the ‘Get External Data’ controls

In the workbook, we are going to use the data import feature. This can be found in the ‘Data’ tab of the navigation ribbon. Then we will use the ‘From Text’ function in the ‘Get External Data’ group.

Using Excel to import the log
Load the log file into the Text Importer

We will then need to tell the ‘Text Import’ tool which files we want to import. Although the BusMaster log file is a simple text file, it used a file type of .LOG. We need to ensure ‘All Files (*.*)’ is selected, as shown, in order to select the log file.

Select Text Import options in Excel
Select Text Import options in Excel

We want to make some changes to the default import options. 1) We want to use ‘Delimited’ import as our log has a space between each recorded field. 2) We want to start importing at row 14 which save a little time tidying up later. 3) Okay, that’s everything for this page, select next.

Set the import delimiter to 'Space'
Set the import delimiter to ‘Space’

Make sure to select the ‘Space’ delimiter on Step 2 of 3 before selecting ‘Finish’. The default import location is ‘=$A$1’ which is fine for us so we can go ahead and import the data. Hopefully, the log we are importing is only short or this could take a very long time.

It is unusual to need a log of more than 60 seconds for any data review. The slowest messages are sent every 50ms, and the longest chain I have seen is around 40 cycles long, so it takes around 2 seconds on the medium speed bus for all messages to be captured!

Step 2 – Formatting and Filtering the data

Apply filtering to all columns
Apply filtering to all columns

Now we have some log data we can start to do some formatting.

  • Delete the last two rows from the data file (file end information)
  • Insert a row before the first line
  • Type in column headers
    • Time TX/RX Ch Addr Type Len d0 d1 d2 d3 d4 d5 d6 d7
  • Select all populated columns and apply a filter (as shown above)
Applying formatting to the data columns
Applying formatting to the data columns

Next, we want to format the data columns to give them a more traditional ‘HEX’ appearance. This simply means we have two characters in each box. To do this we will select a custom format as shown in the steps above.
1) Select the formatting drop down from the ‘Home’ tab.
2) Select more number formats.
3) Select ‘Custom’.
4) Select the ‘0’ pre-defined format
5) Modify the ‘Type’ to show ’00’ (Zero, Zero).

Filtering out unwanted 'ghost' messages in Excel
Filtering out unwanted ‘ghost’ messages

Now we have something we can pretty much work with. Go ahead and filter the order of the data so that we have the received data first.
1) Click the drop-down next to the ‘Tx/Rx’ column title
2) Select ‘sort A to Z’

Have a little scroll up and down and you might see some rows with no data values in, like the box highlighted above. This is another quirk of BusMaster. While it doesn’t seem to lose messages, when comparing against industry-standard software, it does seem to gain some.

This does happen on a vehicle which is why I think there is an issue with BusMaster, but these ghost lines in the data occur far more on bench testing. Perhaps the 120Ohm termination resistor needs to be less. Either way, filtering the ‘Length’ column to only include ‘0’ then deleting all the remain lines will tidy up our log.

Make sure to save regularly between changes. If you import a lot of data, you’ll end up with a very big file which can make Excel unstable and crash.

Hopefully, you are now confident to import and filter your own data!

M

One Comment

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.