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:



    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!


  • 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.


  • 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