I've got a MySQL load script that almost works, it is perfect except for the date columns, which are not in a MySql friendly format.
load data infile '/Users/pfarrell/sandbox/waybase/folklore/Titles_1976.csv' into table fix76 fields terminated by ',' enclosed by '"' ignore 1 lines ( patentId, USPatentNum, title, grantDate, filedDate)
The problem is that my dates are in mm/dd/yyyy format. Looks like the str_to_date
function is what I want, but I can't figure out how to use it in the load command.
I'm envisioning something like:
grantDate = STR_TO_DATE(something, '%m/%d/%Y'),
but that doesn't work.
Use STR_TO_DATE() method from MySQL to convert. The syntax is as follows wherein we are using format specifiers. The format specifiers begin with %. SELECT STR_TO_DATE(yourDateColumnName,'%d.
MySQL retrieves and displays DATE values in ' YYYY-MM-DD ' format. The supported range is '1000-01-01' to '9999-12-31' . The DATETIME type is used for values that contain both date and time parts.
You can set a custom date format for a specified column in a DataSet job in Workbench 4. You do this by adding the Custom Date Format transform to the DataSet job and then selecting the column and specifying the desired date format.
You can load the date strings into user-defined variables, and then use STR_TO_DATE(@date, '%m/%d/%Y')
to convert them to MySQL dates.
Try this:
load data infile '/Users/pfarrell/sandbox/waybase/folklore/Titles_1976.csv'
into table fix76
fields terminated by ','
enclosed by '"'
ignore 1 lines
( patentId, USPatentNum, title, @grantDate, @filedDate)
set grantDate = STR_TO_DATE(@grantDate, '%m/%d/%Y'),
filedDate = STR_TO_DATE(@filedDate, '%m/%d/%Y')
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With