Beginner - Help w/multiple record edits

Bill Campbell

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

Claudia Frank

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

Bill Campbell

Claudia,

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

Bill Campbell