Beginner - Help w/multiple record edits



  • I’ve got a CSV-formatted UAV telemetry file with 2700 records. The first record is the header. For some reason the first field, the datetime field, is not labeled in the header. So I need to add the word “Time” to the header:

    from:
    ,fsk_rssi,voltage,current,altitude,latitude,longitude,tas,gps_used,fix_type,satellites_num,

    to:
    Time,fsk_rssi,voltage,current,altitude,latitude,longitude,tas,gps_used,fix_type,satellites_num,

    Next, the beginning of each record is a timestamp, as follows:

    20151227 11:34:03:070,

    but my visualizer program requires the format to be:

    2015-12-27 11:34:03.070,

    adding a dash between year and month; adding a dash between month and day; and changing the last colon (:) to a period (.).

    I’ve manually made these changes using column mode but figure there must be an easier way. Any ideas? Thanks!

    Bill



  • Hello Bill-Campbell,

    if the data is always in the same layout you could record a macro and save it with a meaningful name.
    After it has been saved it appears in the Macro menu.
    What needs to be done, after having the document with the needed data loaded, is

    • press record macro
    • press strg+home (this is to make sure that cursor is on the first line on first position)
    • tpye time,
    • press strg+h (replace dialog opens, check regular expression and put in the following)
    • Find what: (\d{4})(\d{2})(\d{2})(\s\d{2}:\d{2}:\d{2})(:)(.*)
    • Replace with: \1-\2-\3\4.\6
    • press alt+a
    • stop recording and save it.

    Requirements, the layout must be always
    year 4 digits = (\d{4})
    month 2 digits = (\d{2})
    day 2 digits = (\d{2}) and
    (\s\d{2}:\d{2}:\d{2})(:)(.*) is
    a whitespace char \2 followed by
    hour 2 digits + colon + minute 2 digits + colon + seconds 2 digits + colon + the rest.

    If the layout hasn’t always this structure, eg month january is reported as 1 instead of 01,
    than I guess a python script, which means installation of python script plugin, is needed or
    someone else has more sophisticated idea of using regex.

    Cheers
    Claudia



  • Claudia,

    My data is always in the same format, so a macro would work. Thank you very much for the coding!

    Bill Campbell


Log in to reply