Search code examples
mysqldatetimeload-data-infile

MySQL Format Date with a Load Data command

Hello I am trying to load data from a csv file but need to format the date when loading the file.

The date format of my csv file is: 1/1/2008 0:00 i'd like to format the date and to just the year with the load command

load data infile 'C:/mysql/ca_pop_educational_attainment.csv'
into table ca_pop.educational_attainment
fields terminated by ','
enclosed by '"'
ignore line 1
set year = STR_TO_DATE(@year,"%Y")
(ea_id, @year, age, educational_attainment, personal_income,pop_count)
Solution

  • The list of columns must come before the SET clause. That is, you list the columns or variables to import into, then following that, you can set individual columns.

    Also, you need to use STR_TO_DATE() to parse the whole string into a date. Then you can wrap that in another function or expression to extract individual parts like the year.

    Something like the following, but I have not tested it:

    load data infile 'C:/mysql/ca_pop_educational_attainment.csv'
into table ca_pop.educational_attainment
fields terminated by ','
enclosed by '"'
ignore line 1
(ea_id, @year, age, educational_attainment, personal_income, pop_count)  
set year = YEAR(STR_TO_DATE(@year,'%m/%d/%Y %k:%i'))