[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3351
  • Last Modified:

"load data infile" with different date format

Hi all,

I've got a csv file to import to my mysql table, but the date format in the csv is different from the default settings in mysql, and my import result in "0000-00-00 00:00:00" in all datatime field, how can I get rid of this?

Thanks
0
ping1234
Asked:
ping1234
2 Solutions
 
ldbkuttyCommented:
what is the dateformat is csv and in table field? How do you import the file?

date time functions of mysql should be of help: http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html
0
 
virmaiorCommented:
the most promising function is: STR_TO_DATE(str,format) (if you have 4.1+)


I just tried since I wasn't getting the best quality on my data:

UPDATE `actusers` SET `appindate` = CONCAT(
SUBSTRING( appindate FROM 7 FOR 4 ) ,
SUBSTRING( appindate FROM 1 FOR 2 ) ,
SUBSTRING( appindate FROM 4 FOR 2 ) ) WHERE  `appindate` LIKE '__/__/____'

UPDATE `actusers` SET `appindate` = CONCAT(
SUBSTRING( appindate FROM 6 FOR 4 ) ,
'0',  SUBSTRING( appindate FROM 1 FOR 1 ) ,
SUBSTRING( appindate FROM 3 FOR 2 ) ) WHERE `appindate` LIKE '_/__/____'

UPDATE `actusers` SET `appindate` = CONCAT(
SUBSTRING( appindate FROM 5 FOR 4 ) ,
'0', SUBSTRING( appindate FROM 1 FOR 1 ) ,
'0',SUBSTRING( appindate FROM 3 FOR 1 ) ) WHERE `appindate` LIKE '_/_/____'

UPDATE `actusers` SET `appindate` = CONCAT(
SUBSTRING( appindate FROM 6 FOR 4 ) ,
SUBSTRING( appindate FROM 1 FOR 2 ) ,
'0',SUBSTRING( appindate FROM 4 FOR 1 ) ) WHERE `appindate` LIKE '__/_/____'

which converts mM/dD/YYYY to YYYYMMDD
0
 
almost5Commented:
Mysql expects dates in yyyy-mm-dd format.

Open csv file in Excel, format the date column(s) as Custom as yyyy-mm-dd hh:mm:ss, save as csv file.  Then do the following command line...

mysql> LOAD DATA LOCAL INFILE 'd:/fpcMigrate/tb_fpc_Category.txt'
    -> INTO TABLE tb_fpc_Category
    -> FIELDS TERMINATED BY ',' ENCLOSED BY '"'
    -> LINES TERMINATED BY '\r\n';
Query OK, 10 rows affected (0.00 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 0
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 
salmonsmCommented:
Hello all,

I'm doing this very thing and still get the following error:

ERROR 1292 (22007): Incorrect date value: '"2001-01-01"' for column 'BirthDate' at row 1

Does this have to with the punctutation? How can I get around this?

Thanks.

Michael
0
 
almost5Commented:
Try adding the "hh:mm:ss" after the yyyy-mm-dd even if it's "00:00:00" like
"2001-01-01 00:00:00"
0
 
Steve_FenwickCommented:
Since Version 5.0.3 MySQL has allowed variables in the LOAD DATA INFILE command.

By way of an example

Create a table:
SNIP
> create table test_import
    (field1       varchar(15),
      field2      varchar(15,
      datefield date);
END SNIP

Now create a function in the database similar to
SNIP
CREATE FUNCTION `ImportDate`(strdate varchar(12)) RETURNS date
BEGIN
     Declare Result varchar(50);
     set result := concat(substring(strdate,7,4),'-',substring(strdate,4,2),'-',substring(strdate,1,2));
     Return cast(Result as date);
END

END SNIP

This function is assuming that the date is in British format of DD/MM/YYYY. You may need to adjust for other (US) date formats - just change the order of the concat statements.

Now we can try to import the data.

Create a small csv file with a couple of varchars in and importantly a date as the final field.
"surname","firstname","date_of_birth"
"Bloggs","Fred","20/01/1960"
"Doe","John","23/02/1964"

Now open the SQL Query browser and enter the following
SNIP
LOAD DATA INFILE 'c:/csv/test.csv'
INTO TABLE test_import
FIELDS TERMINATED BY ","
ENCLOSED BY '"'
LINES TERMINATED BY '\r\n'
(field1,field2,@datefield)
SET datefield = ImportDate(@datefield)

END SNIP

The last bit tells MySQL to import the fileds in the order of field1, field2 and for the next field.... import the data in the third column of the csv file after applying the ImportData function. Ie changing it from '20/01/1960' to the correct MySQL format of '1960-01-20'
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now