#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0

    Missing :00 in date-time field of csv file


    The files that I download have a date and time field that mostly looks like this:

    2013/05/03 05:19:19,

    However, wherever the seconds are zero (00), it does not include the :00, so it looks like this:
    2013/05/03 05:32,
    instead of like this:
    2013/05/03 05:32:00,

    I need to apply a regex to the files, to modify all records that are missing :00 so that they appear with :00.

    Here are 3 records from the file, the second one has the problem.

    2013/05/03 05:30:38,6.63,6.6275,6.63,6.63,25
    2013/05/03 05:32,6.6325,6.6275,6.6275,6.63,26
    2013/05/03 05:33:29,6.63,6.6275,6.63,6.6275,27

    Can someone provide a regex that will make this modification in place throughout the file? Thanks. (Note: This is not a school homework assignment.)
  2. #2
  3. Jealous Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,302
    Rep Power
    9400
    Can you write an expression that matches those lines?
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2013
    Posts
    2
    Rep Power
    0
    Text editor solution:

    I used EditPadLite, with the search/replace function set to Regex.


    in the search box:
    (\s\d\d:\d\d),

    in the replace box:
    $1:00,

    Then I did a 'replace all'.

    ---------

    In LibreOffice Calc (after loading the csv file)

    Menu>Edit>Find and Replace
    Click "More Options" button, check Regular Expressions

    In find box:

    ([:space:][:digit:][:digit:]:[:digit:][:digit:])$

    In replace box:
    $1:00

    Click 'Replace all'

IMN logo majestic logo threadwatch logo seochat tools logo