MySQL import csv is failing with Data too long for column exception

Question

When I'm trying to import csv into MySQL table, I'm getting an error

Data too long for column 'incident' at row 1

I'm sure the values are not higher than varchar(12). But, still I'm getting the error.

MariaDB [pagerduty]> 

    LOAD DATA INFILE '/var/lib/mysql/pagerduty/script_output.csv' 
    REPLACE INTO TABLE incidents 
    ignore 1 lines;

ERROR 1406 (22001): Data too long for column 'incident' at row 1

MariaDB [pagerduty]> 

    LOAD DATA INFILE '/var/lib/mysql/pagerduty/script_output.csv' 
    INTO TABLE incidents 
    ignore 1 lines;

ERROR 1406 (22001): Data too long for column 'incident' at row 1

While trying with REPLACE, the data is uploading only one column(which set on primary key)

MariaDB [pagerduty]> 

    LOAD DATA INFILE '/var/lib/mysql/pagerduty/script_output.csv' 
    IGNORE INTO TABLE incidents 
    ignore 1 lines;

Query OK, 246 rows affected, 1968 warnings (0.015 sec)
Records: 246  Deleted: 0  Skipped: 0  Warnings: 1968

**Columns:**
+----------------+--------------+------+-----+---------+-------+
| Field          | Type         | Null | Key | Default | Extra |
+----------------+--------------+------+-----+---------+-------+
| incident       | varchar(12)  | NO   | PRI | NULL    |       |
| description    | varchar(300) | YES  |     | NULL    |       |
| status         | varchar(12)  | YES  |     | NULL    |       |
| urgency        | varchar(7)   | YES  |     | NULL    |       |
| service        | varchar(27)  | YES  |     | NULL    |       |
| trigger        | varchar(25)  | YES  |     | NULL    |       |
| team           | varchar(20)  | YES  |     | NULL    |       |
| incident_start | datetime(6)  | YES  |     | NULL    |       |
| incident_end   | datetime(6)  | YES  |     | NULL    |       |
| resolved_by    | varchar(20)  | YES  |     | NULL    |       |
+----------------+--------------+------+-----+---------+-------+
10 rows in set (0.003 sec)

This can often be cause by a previous line being oddly formed and pushing everything else out of line. Check line terminators and column seperators, and the length of other columns too — RiggsFolly, Aug 26 '21 at 12:02
And what is the question? *I'm sure the values are not higher than varchar(12). But, still I'm getting the error.* Provide an example of CSV file (3-5 lines). — Akina, Aug 26 '21 at 12:03
You're not specifying `FIELDS TERMINATED BY` so MySQL is treating the data as tab-separated values. — Tangentially Perpendicular, Aug 26 '21 at 12:06
Here is the sample csv: "incident","description","service","trigger","team","incident_start","incident_end","resolved_by" 45497615,"Largest volume disk usage warning is Triggered on host","IACS_Support MDM-IIS Alerts","Triggered through the API","IACS_Support - Ankur","2021-08-24T04:04:50Z","2021-08-24T04:23:54Z","Integration API" — Sonu R, Aug 26 '21 at 12:35
Does this answer your question? [How do I import CSV file into a MySQL table?](https://stackoverflow.com/questions/3635166/how-do-i-import-csv-file-into-a-mysql-table) — miken32, Aug 27 '21 at 00:08

score 1 · Answer 1 · answered Aug 26 '21 at 21:21

By default, MySQL looks for a TAB character to separate values. Your file is using a comma, so MySQL reads the entire line and assumes it is the value for the first column only.

You need to tell MySQL that the column terminator is a comma, and while you're at it, tell it about the enclosing double quotes.

Try this:

LOAD DATA INFILE '/var/lib/mysql/pagerduty/script_output.csv' REPLACE INTO TABLE incidents
    columns terminated by ','
    optionally enclosed by '"'
    ignore 1 lines;

Reference

score 0 · Answer 2 · answered Aug 26 '21 at 14:50

If you THINK your data appears ok, and its still nagging about the too long data, how about creating a new temporary table structure and set your first column incident to a varchar( 100 ) just for grins... maybe even a few others if they too might be causing a problem.

Import the data to THAT table to see if same error or not.

If no error, then check the maximum trimmed length of the data in respective columns and analyze the data itself... bad format, longer than expected, etc.

Once resolved, then you can pull into production once you have figured it out. You could also always PRE-LOAD the data into this larger table structure, truncate it before each load so no dups via primary key on a fresh load.

I have had to do that in the past, also was efficient for pre-qualifying lookup table IDs for new incoming data. Additionally could apply data cleansing in the temp table before pulling into production.

MySQL import csv is failing with Data too long for column exception

2 Answers2