Editing Playback Files in Excel (CSV)

If you use Excel to edit .CSV files, specific formatting is lost when the file is saved. However, this can be corrected by using a text editor.

The Problem

When the .CSV file is imported into Excel, data appears in specific columns and rows. Because data does not appear in all columns for each row, Excel will "append" or remove commas as field delimiters – when the file is exported – to enforce the column count for each row. This results in incorrect extra/missing commas that will prevent the file from being successfully loaded.

The required file format

Dearborn Group Format x15

Mon Oct 06 18:35:01 2003

Mon Oct 06 18:35:09 2003

40
Trigger Frame
Absolute
Timestamp,Channel,Frame ID,Frame Acronym,Protocol,DataCount,Data,Tx/Rx 00:00:00:000:000,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 78 E0 00 00,Tx, 00:00:00:000:080,2,12D,12D,CAN - STD,8,00 00 00 00 78 E0 00 00,Rx, 00:00:00:043:830,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 55 F0 00 00,Tx, 00:00:00:043:910,2,12D,12D,CAN - STD,8,00 00 00 00 55 F0 00 00,Rx, 00:00:00:075:150,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 3E 80 00 00,Tx, 00:00:00:075:250,2,12D,12D,CAN - STD,8,00 00 00 00 3E 80 00 00,Rx, 00:00:00:075:500,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 2E E0 00 00,Tx, 00:00:00:075:590,2,12D,12D,CAN - STD,8,00 00 00 00 2E E0 00 00,Rx, 00:00:00:075:850,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 20 00 00 00,Tx, 00:00:00:075:910,2,12D,12D,CAN - STD,8,00 00 00 00 20 00 00 00,Rx, 00:00:01:005:550,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 78 E0 00 00,Tx, 00:00:01:005:640,2,12D,12D,CAN - STD,8,00 00 00 00 78 E0 00 00,Rx, 00:00:01:025:450,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 55 F0 00 00,Tx, 00:00:01:025:530,2,12D,12D,CAN - STD,8,00 00 00 00 55 F0 00 00,Rx, 00:00:01:035:550,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 3E 80 00 00,Tx, 00:00:01:035:630,2,12D,12D,CAN - STD,8,00 00 00 00 3E 80 00 00,Rx, 00:00:01:045:540,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 2E E0 00 00,Tx, 00:00:01:045:620,2,12D,12D,CAN - STD,8,00 00 00 00 2E E0 00 00,Rx,

How the file is saved by Excel

Dearborn Group Format x15,,,,,,,

Mon Oct 06 18:35:01 2003,,,,,,,

Mon Oct 06 18:35:09 2003,,,,,,,,

40,,,,,,,
Trigger Frame,,,,,,,
Absolute,,,,,,,
Timestamp,Channel,Frame ID,Frame Acronym,Protocol,DataCount,Data,Tx/Rx 00:00:00:000:000,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 78 E0 00 00,Tx 00:00:00:000:080,2,12D,12D,CAN - STD,8,00 00 00 00 78 E0 00 00,Rx 00:00:00:043:830,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 55 F0 00 00,Tx 00:00:00:043:910,2,12D,12D,CAN - STD,8,00 00 00 00 55 F0 00 00,Rx 00:00:00:075:150,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 3E 80 00 00,Tx 00:00:00:075:250,2,12D,12D,CAN - STD,8,00 00 00 00 3E 80 00 00,Rx 00:00:00:075:500,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 2E E0 00 00,Tx 00:00:00:075:590,2,12D,12D,CAN - STD,8,00 00 00 00 2E E0 00 00,Rx 00:00:00:075:850,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 20 00 00 00,Tx 00:00:00:075:910,2,12D,12D,CAN - STD,8,00 00 00 00 20 00 00 00,Rx 00:00:01:005:550,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 78 E0 00 00,Tx 00:00:01:005:640,2,12D,12D,CAN - STD,8,00 00 00 00 78 E0 00 00,Rx 00:00:01:025:450,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 55 F0 00 00,Tx 00:00:01:025:530,2,12D,12D,CAN - STD,8,00 00 00 00 55 F0 00 00,Rx 00:00:01:035:550,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 3E 80 00 00,Tx 00:00:01:035:630,2,12D,12D,CAN - STD,8,00 00 00 00 3E 80 00 00,Rx 00:00:01:045:540,1,12D,ECM_12Dh,CAN - STD,8,00 00 00 00 2E E0 00 00,Tx 00:00:01:045:620,2,12D,12D,CAN - STD,8,00 00 00 00 2E E0 00 00,Rx

The Solution

Use a text editor to modify the file created by Excel. Remove all the extra commas at the end of the first six lines. Add a comma at the end of each message line (ends with Tx or Rx). This can be done using the "Search and Replace" feature of the text editor to change all occurrences of 'Tx' with 'Tx," and all occurrences of 'Rx' with 'Rx,'.

Creating New Files within Excel (CSV)

This method can also be used when files are created in Excel. The file needs to contain the "Frame Acronym" and format is a required column that can just repeat the "Frame ID" information. Once this is complete, save as a .CSV file. The export issue described above needs to be included as part of this process.