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.
Find
([0-9]+)/([0-9]+)/([0-9]+),([0-9]+),([0-9]+),([0-9]+),([0-9.]+),
Replace
'\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.