CODEWITHSUNDEEP
javamysqlcsv
user2924127
user2924127

Reputation: 6252

java.sql.SQLException: Row 1 was truncated; it contained more data than there were input columns

I am creating a CSV file with rows using this syntax:

  writer.append("SomeString");
  writer.append(',');
  writer.append("SomeValue");
  writer.append(',');
  writer.append("SomeString");
  writer.append(',');
  writer.append("SomeValue");
  writer.append(',');
  writer.append("SomeOtherValue");
  writer.append('\n');

I then try to load the csv files into mysql using this syntax:

  String loadFileSQL = "LOAD DATA INFILE '/home/CSV_FILES/"
  + f.getName().substring(0, f.getName().length() - 4)
  + ".csv' "
  + "INTO TABLE counts "
  + "FIELDS TERMINATED BY ',' "
  + "LINES TERMINATED BY '\n' "
  + "(@col1,@col2,@col3,@col4)"
  + "SET pstr=@col1, pcnt=@col2, rstr=@col3, rcnt=@col4;";

But I get the error:

java.sql.SQLException: Row 1 was truncated; it contained more data than there were input columns

Looking online it seems like a delimiter problem. I am using Linux ubuntu so I have tried to terminate by \n and also \r\n with no luck.

Upvotes: 1

Views: 4863

Answers (1)

Elliott Frisch
Elliott Frisch

Reputation: 201477

I believe you just need to add @col5. You can ignore @col5 after you define it, but the error is valid (your sample has 5 columns, not 4). 

"(@col1,@col2,@col3,@col4,@col5)"

Upvotes: 1

Related Questions