Incompatible date format when importing data from Excel to Mysql (00-00-0000 VS 0000-00-00)

Asked

Viewed 2,192 times

0

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

java mysql excel date

3 answers

2

You can both go in excel file and change the column format so that the date is in format ISO 8601, Mysql default, or even use a substring in Java.

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

Excel

1

Rafael, I did not understand how you want to import this spreadsheet to Mysql, if you are using the Java language to import it you can use the Simpledateformat method (java.text.Simpledateformat Javadoc) to format your date, it is very simple and easy to be implemented:

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();
}

That bit of Codic will do for you. However, if you only want to give an initial load in Mysql you can use a very useful tool, PDI (Pentaho Data Integration), sofware very good and that particurlamente taste very much.

I hope I’ve helped!

Abs

1

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

A tip for you, would be to create a Trigger in the database before inserting (before insert) and convert the result to that expected by the bank.

Below is the link to the Mysql documentation on triggering. Trigger Syntax

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

Browser other questions tagged java mysql excel date

You are not signed in. Login or sign up in order to post.