Date format incompatible when importing data from Excel to MySQL (00-00-0000 VS 0000-00-00)

Question

Date format incompatible when importing data from Excel to MySQL (00-00-0000 VS 0000-00-00)

Navigation

#1 by (2 votes)
#2 by (1 votes)
#3 by (1 votes)

0

I'm trying to import data from an Excel spreadsheet to MySQL, the problem is that I have a column in Excel that the dates are in dd-mm-yyyy format and the format accepted by MySQL is yyyy-mm-dd . I want to know how to convert the dates to yyyy-mm-dd before importing it into MySQL.

mysql java data excel

asked by anonymous 14.03.2014 / 21:00

3 answers

Error - You must supply a resource ID for a TextView Read Blocking in C

score 2 · Answer 1

You can either go to the excel file and change the column format so that the date is in the format ISO 8601 , standard MySQL, or even use a substring in Java.

For Excel you can use the customize and use this mask option: yyyy / mm / dd

score 1 · Answer 2

I believe by your saying in the question, that you are importing a file directly without opening it correct?

A tip for you would be to create a trigger on the database before inserting ( before insert ) and converting the result to what was expected by the database.

Below is a link to the MySQL documentation on creating triggers. Trigger Syntax

This is a solution, there are others like opening the file before saving and through the convert system for example.

score 1 · Answer 3

Rafael, I did not quite understand how you want to import this spreadsheet into MySQL, if you are using the Java language to import it, you can use the SimpleDateFormat method (#) to format your date, it is very simple and easy to implement:

SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
String dateInString = "07/06/2013";

try {

    Date date = formatter.parse(dateInString);
    System.out.println(date);
    System.out.println(formatter.format(date));

} catch (ParseException e) {
    e.printStackTrace();
}

This snippet of code is already there. However, if you are just wanting to load up MySQL you can use a very useful tool, the Pentaho Data Integration ( Pentaho Data Integration >), very good software and I particularly like it a lot.

I hope I have helped!

Abs