Load Data Infile vs Date Type

Every now and again lots of data rows have to be loaded using a csv text file and once in a while it isn't well formatted. In this case we have to manually modify the data but luckily we can use the options of the 'LOAD DATA INFILE' command.

Today I'm going to look into the 'SET' option.

Following you'll find a complete example. 

Let's start creating a table, shall we ?

create table table_ok (`subject` varchar(255), `data_appo` date, `prio` varchar(255), `taskkk` int(11),
`acco` varchar(255), `cont` varchar(255), `case_appo` varchar(255), `assign` varchar(255),  `last_mod` Date);

Here is the data row in detail :

"AAAAAA","6/5/2014","BBBBB","1","CCCCC","DDDDDD","1111111111","EEEEEEE","6/5/2014"

Looking at the second column we can see that the data type isn't properly formatted, indeed mysql server is used to saving and displaying date type using the following format "2014-06-05" that is completely different from the ones seen before.

Here is the solution in detail:

load data infile '/home/mysql/appo.txt'
into table table_ok
fields terminated by ','
optionally  enclosed by '"'
LINES TERMINATED BY '\n'
(subject, @first_data, prio, taskkk, acco, cont, case_appo, assign, @second_data )
set data_appo = str_to_date(@first_data,'%d/%c/%Y'), last_mod = str_to_date(@second_data,'%d/%c/%Y') ;
 
Feel free to leave a comment or ask any further queries regarding the Post.

See you in my next Blog.


Byeeeeee