Data truncation error using mysql jdbc when inserting data from csv file to table

Question

I am inserting data from a .csv file to my MySQl database table in eclipse using "load data local infile". However, I am getting the error message shown below.

Exception in thread "main" com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect date value: '1894' for column 'startYear' at row 1

Sample Data: tconst_titles,titleType,primaryTitle,originalTitle,isAdult,startYear,endYear,runtimeMinutes

tt1,short,Carmencita,Carmencita,0,1894,0000,1

tt2,short,Le clown et ses chiens,chiens,0,1892,0000,5

A similar thread mentioned that it has to do with the date values not being in the correct format. However, I have declared "startYear" as a date when creating the table and it should recognize '1894' as a year shouldn't it ?

Code for creating the table:

String sql1 = "CREATE TABLE Titles" +

          "(tconst_titles VARCHAR(255) PRIMARY KEY, " +

          " titleType VARCHAR(255), " + 

          " primaryTitle VARCHAR(255), " + 

          " originalTitle VARCHAR(255), " + 

          " isAdult TINYINT(1), " + 

          " startYear DATE, " + 

          " endYear DATE, " + 

          " runtimeMinutes INT)";

Code for inserting data from .csv file:

import java.sql.*;

public class populate { 

    public static void main(String[] args) throws SQLException {

        Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/IMDB","root","user");
        Statement stmt = con.createStatement();
        String sql = 
        "load data local infile 'titles.csv' \n" +
        "   replace \n" +
        "   into table Titles \n" +
        "   columns terminated by '\\t' \n" +
        "   ignore 1 lines";
        stmt.execute(sql);
      }
}

If you are using Java code then you can use integration solution. You can use it by XML configuration or if your file and table has fixed format, You can directly use Java code to use. dbisweb.wordpress.com — compyutech
– compyutech, Commented May 4, 2018 at 17:34
Using the above with Java code check stackoverflow.com/a/50180272/2480620 — compyutech
– compyutech, Commented May 4, 2018 at 18:02

Bill Karwin · Accepted Answer · 2018-03-06 16:55:40Z

1

https://dev.mysql.com/doc/refman/5.7/en/datetime.html says:

The DATE type is used for values with a date part but no time part. MySQL retrieves and displays DATE values in 'YYYY-MM-DD' format. The supported range is '1000-01-01' to '9999-12-31'.

Your data is only YYYY. This has no month or day, so it's not a date in the format required by MySQL.

If you only want to store a year with no month or day, use the YEAR data type if you have values in the supported range of 1901 - 2155.

If you have other years (like you have 1894), use SMALLINT UNSIGNED.

answered Mar 6, 2018 at 16:55

Bill Karwin

567k87 gold badges709 silver badges869 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user8929822 Over a year ago

Thanks. Using SMALLINT UNSIGNED removed the error. However, I have a concern regarding that declaration. I need to write queries that require me to work with dates. For e.g. checking for leap year, getting count for rows based on the year, etc. Would declaring the 'startYear' as a 'SMALLINT' limit working with date queries ?

Bill Karwin Over a year ago

You can certainly search for a specific value in your SMALLINT column, or group by these values to get counts, etc. Those aren't date-specific functions.

Collectives™ on Stack Overflow

Data truncation error using mysql jdbc when inserting data from csv file to table

Code for creating the table:

Code for inserting data from .csv file:

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

Code for creating the table:

Code for inserting data from .csv file:

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related