CODEWITHSUNDEEP
javamysqljdbc
user8929822
user8929822

Reputation: 323

Row 1 doesn't contain data for all columns MySQL JDBC Eclipse Error

I am trying to insert the contents of a .csv file to a database table using a java program in eclipse. However, I am getting the following error message.

Exception in thread "main" java.sql.SQLException: Row 1 doesn't contain data for all columns

I did look into similar posts, but could not figure out the solution for my code. Any idea how I can resolve the issue ?

Sample data in csv file:

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
tt3,short,Pauvre Pierrot,Pauvre Pierrot,0,1892,0000,4

My Code:

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

Upvotes: 0

Views: 2511

Answers (1)

Blasanka
Blasanka

Reputation: 22437

The problem is in your java code. You are separating by \t(tab) instead of ,:

"   columns terminated by '\\t' \n" +

This line should be:

"   columns terminated by ',' \n" +

If you want to separate columns by \t you can change your csv file data to like below:

tt1 short   Carmencita  Carmencita  0   1894    0000    1
tt3 short   Pauvre_Pierrot  Pauvre_Pierrot  0   1892    0000    4
tt4 short   Pauvre_Pierrot  Pauvre_Pierrot  0   1892    0000    4

By the way, csv meaning Comma Separated Values.

Upvotes: 2

Related Questions