Importing a CSV to MySQL with different date format

by

You can replace format during importing data from the CSV file, for example –

LOAD DATA INFILE 'file_name.csv'
INTO TABLE table_name
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(id, column2, column3, @date_time_variable) -- read one of the field to variable
SET date_time_column = STR_TO_DATE(@date_time_variable, '%d-%b-%Y'); -- format this date-time variable

It will format the string like ’31-Jan-2011′ to a correct DATETIME data type.

More information here – LOAD DATA INFILE Syntax.

More Related Contents:

  1. SQL split values to multiple rows
  2. How can I output MySQL query results in CSV format?
  3. MySQL Conditional Insert
  4. Get the new record primary key ID from MySQL insert query?
  5. What is the opposite of GROUP_CONCAT in MySQL?
  6. Which is faster: multiple single INSERTs or one multiple-row INSERT?
  7. LAST_INSERT_ID() MySQL
  8. SQL – IF EXISTS UPDATE ELSE INSERT INTO
  9. MySql Table Insert if not exist otherwise update
  10. Dump all tables in CSV format using ‘mysqldump’
  11. How to skip columns in CSV file when importing into MySQL table using LOAD DATA INFILE?
  12. MySQL trigger On Insert/Update events
  13. MySQL Error 1264: out of range value for column
  14. BULK INSERT in MYSQL
  15. How do I get the ID of multiple inserted rows in MySQL?
  16. MySQL trigger to update a field to the value of id
  17. mysql pivot query results with GROUP BY
  18. How to insert data to MySQL with auto-incremented column(field)?
  19. How to insert values in table with foreign key using MySQL?
  20. MySQL Insert & Joins
  21. How to add a where clause in a MySQL Insert statement?
  22. MySQL LAST_INSERT_ID() used with multiple records INSERT statement
  23. How to join two tables using a comma-separated-list in the join field
  24. MySQL – insert data from another table merged with constants
  25. Import CSV to Update only one column in table
  26. MySQL optimizing INSERT speed being slowed down because of indices
  27. MySQL – How to parse a string value to DATETIME format inside an INSERT statement?
  28. Incorrect integer value: ” for column ‘id’ at row 1
  29. How to import a csv file into MySQL workbench?
  30. Insert and set value with max()+1 problems

Leave a Comment