How to change string date to MySQL date format at time of import of CSV using MySQL’s LOAD DATA LOCAL INFILE

by

You need to use the SET clause, along with a variable to reference the contents of the row at that column. In your column list, you assign your date column to a variable name. You can then use it in your SET statement. (Note, I haven’t got MySQL in front of me to test this on.)

LOAD DATA LOCAL INFILE 'file.csv' INTO TABLE my_table
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(name, address, @var1)
set dateOfBirth = STR_TO_DATE(@var1, '%d-%b-%y')

See examples a way down the page at: http://mysql2.mirrors-r-us.net/doc/refman/5.1/en/load-data.html (Not sure why this page seems to differ from the main documentation in that it actually contains an example of SET usage.)

More Related Contents:

  1. generate days from date range
  2. MySQL’s now() +1 day
  3. Create date from day, month, year fields in MySQL
  4. Mysql strip time component from datetime
  5. MySql bulk load command line tool
  6. How to return only the Date from a SQL Server DateTime datatype
  7. MySQL Insert query doesn’t work with WHERE clause
  8. Delete from two tables in one query
  9. Why would an IN condition be slower than “=” in sql?
  10. Get day of week in SQL Server 2005/2008
  11. Fulltext Search with InnoDB
  12. MySQL dump by query
  13. Set NOW() as Default Value for datetime datatype?
  14. Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
  15. Rewriting mysql select to reduce time and writing tmp to disk
  16. How do I convert from BLOB to TEXT in MySQL?
  17. Calculate business hours between two dates
  18. Can you access the auto increment value in MySQL within one statement?
  19. Which SQL query is better, MATCH AGAINST or LIKE?
  20. What does DELIMITER // do in a Trigger?
  21. How to create a multi-tenant database with shared table structures?
  22. PHP – Import CSV file to mysql database Using LOAD DATA INFILE
  23. MySQL #1140 – Mixing of GROUP columns
  24. MySQL: GROUP_CONCAT with LEFT JOIN
  25. Boolean vs tinyint(1) for boolean values in MySQL
  26. How to use Timestamp_to_scn and Scn_to_timestamp in Oracle?
  27. Find rows that have the same value on a column in MySQL
  28. How to find all IDs of children recursively?
  29. In SQL how do I get the maximum value for an integer?
  30. Get the first and last date of next month in MySQL

Leave a Comment