2

I have looked on previous forums for this, websites etc and can't find a solution. I keep getting this error despite me having 8 columns in my database and my csv file which I'm trying to load into the database.

I have included screenshots of my command line, database table which im loading into and my csv file.

Any help is much appreciated!

Any suggestions on this please??

SQL Command Line

enter image description here

enter image description here

Mahmoud Yassine
  • 61
  • 2
  • 8
  • You should show sample rows from csv file as TEXT not as an image of something which read the csv file. AND if on windows try '\r\n' – P.Salmon May 08 '20 at 15:07
  • @P.Salmon I tried '\r\n' earlier before posting and same error message came up? – Mahmoud Yassine May 08 '20 at 15:40
  • If none of the standard line terminators then that suggests some other problem please add a few rows of your file as text to the question. – P.Salmon May 09 '20 at 06:40

2 Answers2

6

It’s the line endings. MySQL isn’t getting what it expects, so specify the format of the file using LINES TERMINATED BY ‘\r\n’ or whatever is appropriate for you:

‘\r\n’ for files that came from Windows systems ‘\r’ for files from VMS ‘\n’ for every other source.

jithu thomas
  • 279
  • 1
  • 3
1

The issue is not related to the new lines but related to the commas within the data itself as seen in the "Full Name" column in your data.

Was able to replicate and fix the issue.

Database table used for the replication of the issue, please note that the data definitely fits within the column.

mysql> describe import;
+-------+--------------+------+-----+---------+-------+
| Field | Type         | Null | Key | Default | Extra |
+-------+--------------+------+-----+---------+-------+
| col1  | varchar(255) | YES  |     | NULL    |       |
| col2  | varchar(255) | YES  |     | NULL    |       |
+-------+--------------+------+-----+---------+-------+
2 rows in set (0.01 sec)

Replication:

I am sure that the line terminator is \n as the file was created on linux.

# cat /var/lib/mysql-files/import.csv
col1,col2
test1,value1,value2
test2,value3

SQL statement that gives the issue:

LOAD DATA INFILE '/var/lib/mysql-files/import.csv' 
INTO TABLE import
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Error:

ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns

Solution:

I had to change the data file and the SQL statement to make the issue go away.

I made sure that the data contained double quotes around the columns:

# cat /var/lib/mysql-files/import.csv 
"col1","col2"
"test1","value1,value2"
"test2","value3"

Updated the SQL statement to know that the fields are enclosed by double quotes, seen "ENCLOSED BY '"'":

LOAD DATA INFILE '/var/lib/mysql-files/import.csv' 
INTO TABLE import
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES;

Result:

Query OK, 2 rows affected (0.01 sec)
Records: 2  Deleted: 0  Skipped: 0  Warnings: 0

Import was successful:

mysql> select * from import;
+-------+---------------+
| col1  | col2          |
+-------+---------------+
| test1 | value1,value2 |
| test2 | value3        |
+-------+---------------+
2 rows in set (0.00 sec)
Piet Bosch
  • 166
  • 3