I was given a flat file that had the date in this format
\n
\nDate,Hour,Min,Seconds,Temperature,Unused field\n11\/25\/2013,14,0,0,59.69,\n11\/25\/2013,14,15,0,60.38,\n11\/25\/2013,14,30,0,61.85,\n11\/25\/2013,14,45,0,62.57,\n<\/code><\/pre>
\nbut the table that I need to put it into is formatted as
\n\n(64349,'2014-01-06 20:45:00',44.258,NULL),\n(64350,'2014-01-06 21:00:00',44.978,NULL),\n<\/code><\/pre><\/p>\nIn BBEdit I used this grep to convert the date and separate out the temp into a separate field.<\/p>\n
\nFind\n([0-9]+)\/([0-9]+)\/([0-9]+),([0-9]+),([0-9]+),([0-9]+),([0-9.]+),\nReplace\n'\\3-\\2-\\1 \\4:\\5:\\6',\\7,NULL),\n<\/code><\/pre><\/p>\nWhat I\u2019ve 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\u2019m done so I added the ‘ field separators and the NULL field and line terminator. <\/p>\n
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.<\/p>\n
After I rearranged the date, I did need to fix months and days that were only one digit\u2014they 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. <\/p>\n","protected":false},"excerpt":{"rendered":"
I was given a flat file that had the date in this format Date,Hour,Min,Seconds,Temperature,Unused field 11\/25\/2013,14,0,0,59.69, 11\/25\/2013,14,15,0,60.38, 11\/25\/2013,14,30,0,61.85, 11\/25\/2013,14,45,0,62.57, 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. … Continue reading Using grep to convert date and time.<\/span><\/a><\/p>\n","protected":false},"author":6,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[8],"tags":[],"class_list":["post-1823","post","type-post","status-publish","format-standard","hentry","category-coding"],"_links":{"self":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1823","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1823"}],"version-history":[{"count":0,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=\/wp\/v2\/posts\/1823\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1823"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1823"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.wellgolly.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1823"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}