Page 1 of 1
Converting Date and time for import
Posted: Fri Jun 03, 2016 12:08 am
by DavJans
I'm importing a csv file into mySQL. Part of the import process I convert a date and a time and convert them to the mySQL format but it seems clunky and I was wondering if there is a better way. Here is what I have so far that works but its ugly..
Example line from csv file:
5/20/2016,11:18AM,6-Jan,MFAB1075,2414815,,09905:GLOVE|CO1680 MEDIUM BRONCO-PREM SIDE LEATHER,1,ISSUE,4.75,4.75
here is the script I wrote to convert it
put item 1 of tLine into tempdate
put item 2 of tLine into temptime
replace "/" with "," in tempdate
replace ":" with "," in temptime
replace "AM" with ",AM" in temptime
replace "PM" with ",PM" in temptime
put item 1 of tempdate into tempmonth
put item 2 of tempdate into tempday
if tempday < "10" then
put 0 before tempday
end if
if tempmonth < "10" then
put 0 before tempmonth
end if
put item 3 of tempdate into tempyear
put item 1 of temptime into temphour
put item 2 of temptime into tempminute
if item 3 of temptime = "PM" then
put temphour + 12 into temphour
end if
put tempyear & "-" & tempmonth & "-" & tempday & " " & temphour & ":" & tempminute & ":00" into mySQLdatetime
Here is the result in mySQLdatetime:
2016-05-20 11:18:00
Thank you
Re: Converting Date and time for import
Posted: Fri Jun 03, 2016 12:21 am
by dunbarx
Hi.
I did not go through each line in your handler, but I see what you are doing. I see no other way to reFormat the initial data into its final form than the way you do, that is, parse the elements in the starting string and reformulate them piece by piece into the final string.
Could you save a few steps? Maybe, by simply bypassing, say, "put item 3 of tempDate into tempYear", and just place that snippet into the final line as one of the string building elements. But that would make the whole less readable, and anyway seems relatively unimportant.
Craig Newman
Re: Converting Date and time for import
Posted: Fri Jun 03, 2016 7:39 am
by Opaquer
I had a bit of a play around with it, and got it down to 12 lines, which is almost half as short if that helps
. The main thing was I cheated a bit with having everything in the one variable. Also with the dates, I used a repeat loop for that, and did the time calculation in a few less steps
Code: Select all
put item 1 of tLine&":"&item 2 of tLine into tempdate
put replacetext(tempdate,"[/:]",",") into tempdate
if item 5 of tempdate contains "PM" then
put item 4 of tempdate+12 into item 4 of tempdate
end if
delete char 3 to 4 of item 5 of tempdate
repeat with i=1 to 2
if the length of item i of tempdate<2 then
put 0 before item i of tempdate
end if
end repeat
put item 3 of tempdate&"-"&item 1 of tempdate&"-"&item 2 of tempdate&" "&item 4 of tempdate&":"&item 5 of tempdate&":00" into mySQLdatetime
Edit: Also sorry if you've seen this code change, I kept finding less and less lines to do the code with
Re: Converting Date and time for import
Posted: Fri Jun 03, 2016 11:48 am
by AxWald
Hi,
A.) If you have to work with date fields & databases it is a good idea to have a set of predefined functions to handle it. These are mine for European date & time stuff:
Code: Select all
function DI2Sys DI
-- DateItems -> 23.03.2016 12:44
set the itemdelimiter to ","
return format("%02s", item 3 of DI) & "." & format("%02s", item 2 of DI) & "." & item 1 of DI \
& " " & format("%02s", item 4 of DI) & ":" & format("%02s", item 5 of DI)
end DI2Sys
function Sys2DI MyTime
-- 23.03.2016 12:44 -> DateItems
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "."
put item 3 of TheDate & "," & item 2 of TheDate & "," & item 1 of TheDate & "," into MyVar
set the itemdelimiter to ":"
put item 1 of TheTime & "," & item 2 of TheTime & ",00,0" after MyVar
return MyVar
end Sys2DI
function Sys2SQL MyTime
-- 23.03.2016 12:44 -> 2016-03-23 12:44
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "."
put item 3 of TheDate & "-" & item 2 of TheDate & "-" & item 1 of TheDate & " " into TheDate
return TheDate & TheTime
end Sys2SQL
function SQL2SYS MyTime
-- 2016-03-23 12:44:00 -> 23.03.2016 12:44
set the itemdelimiter to " "
put item 1 of MyTime into TheDate
put item 2 of MyTime into TheTime
set the itemdelimiter to "-"
put item 3 of TheDate & "." & item 2 of TheDate & "." & item 1 of TheDate & " " into TheDate
return TheDate & TheTime
end SQL2Sys
For sure, you'd need to change them to your suits ...
When working with date data within LC I usually use dateItems as my base format.
B.) Databases have SQL functions that can help tremendously. For instance,
Code: Select all
"strftime('%Y,%m,%d,%H,%M,%S,%w',t_table.j_creationTime)"
makes SQLite returning nice dateItems ;-)
For MySQL have a look
here, I'd thing especially
STR_TO_DATE() could be helpful - let the database itself format its content!
Have fun!
Re: Converting Date and time for import
Posted: Fri Jun 03, 2016 4:34 pm
by DavJans
Thank you