MySQL Forums :: Newbie :: Load Infile - Not able to import microsecond level datetime from CSV


Advanced Search

Load Infile - Not able to import microsecond level datetime from CSV
Posted by: Chet Desai ()
Date: July 28, 2010 12:12AM

I am not able to load data from CSV file that has a datetime field that has microsecond level granularity.

mysql> desc gtp;
+--------------------+------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+------------+------+-----+---------+-------+
| Papu_Id | tinyint(4) | YES | | NULL | |
| Physical_Papu_Id | tinyint(4) | YES | | NULL | |
| Report_Time | datetime | YES | | NULL | |
| Ul_Bytes | int(11) | YES | | NULL | |
| Dl_Bytes | int(11) | YES | | NULL | |
| Ave_Buf_Fill_Denom | tinyint(4) | YES | | NULL | |
| Ave_Buf_Fill_Numer | tinyint(4) | YES | | NULL | |
+--------------------+------------+------+-----+---------+-------+


mysql> load data infile 'C:\\temp\\Test_GTP.dat' into
-> table gtp
-> fields terminated by ','
-> lines terminated by '\r\n'
-> (Papu_Id, Physical_Papu_Id, @Rpt, Ul_Bytes, Dl_Bytes, Ave_Buf_fill_Denom,
Ave_Buf_Fill_Numer)
-> set Report_Time = str_to_date(@Rpt, '%Y-%m-%d %H:%i:%s.%f');

ERROR 1292 (22007): Truncated incorrect datetime value: '2010-07-18 00:00:07.020
000000'



Test_GTP.dat :
==============
0,0,2010-07-18 00:00:07.020000000,10543890,110391275,6,0
1,1,2010-07-18 00:00:07.020000000,3652696,47139832,6,0




But following works:
====================
mysql> select str_to_date('2010-07-18 00:00:07.020000000', '%Y-%m-%d %H:%i:%s.%f
');
+----------------------------------------------------------------------+
| str_to_date('2010-07-18 00:00:07.020000000', '%Y-%m-%d %H:%i:%s.%f') |
+----------------------------------------------------------------------+
| 2010-07-18 00:00:07.020000 |
+----------------------------------------------------------------------+
1 row in set, 1 warning (0.00 sec)




Thank you in advance !!

-Chetan.

Options: ReplyQuote


Subject Written By Posted
Load Infile - Not able to import microsecond level datetime from CSV Chet Desai 07/28/2010 12:12AM
Re: Load Infile - Not able to import microsecond level datetime from CSV Devart Team 07/28/2010 01:22AM
Re: Load Infile - Not able to import microsecond level datetime from CSV Rick James 07/30/2010 12:48AM
Re: Load Infile - Not able to import microsecond level datetime from CSV Chet Desai 07/30/2010 08:20AM
Re: Load Infile - Not able to import microsecond level datetime from CSV Chet Desai 07/30/2010 08:29AM
Re: Load Infile - Not able to import microsecond level datetime from CSV Rick James 07/30/2010 09:19AM
Re: Load Infile - Not able to import microsecond level datetime from CSV Chet Desai 09/15/2010 11:46AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.