Using grep to convert date and time.

I was given a flat file that had the date in this format

Date,Hour,Min,Seconds,Temperature,Unused field

but the table that I need to put it into is formatted as

(64349,'2014-01-06 20:45:00',44.258,NULL),
(64350,'2014-01-06 21:00:00',44.978,NULL),

In BBEdit I used this grep to convert the date and separate out the temp into a separate field.

'\3-\2-\1 \4:\5:\6',\7,NULL),

What I’ve done is separate out the different parts of the date with parentheses. Then I rearranged the month, day, year with \3-\2-\1 and added the dash separator. The time is in the correct order so all I need to do is put in the right separator. I want an SQL file when I’m done so I added the ‘ field separators and the NULL field and line terminator.

I’ll use the numbering feature of BBEdit to add the row numbers and then add a paren at the beginning of the line. I also need to remember to change the last comma to a semi-colon. Then I can import the data to the MySQL table.

After I rearranged the date, I did need to fix months and days that were only one digit—they need a 0 before the digit. I did this manually with a find and replace. Same with the time. If you did this a lot, you could write a grep that it looked for records with only one digit and prefix it with a zero. A ? will look for exactly one occurrence of a digit. Then do a second pass to get the rest. Or you could use the # to look for one or two digits.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.