Error 1265. Data Truncated for Column When Trying to Load Data from Txt File

error 1265. Data truncated for column when trying to load data from txt file

This error means that at least one row in your Pickup_withoutproxy2.txt file has a value in its first column that is larger than an int (your PickupId field).

An Int can only accept values between -2147483648 to 2147483647.

Review your data to see what's going on. You could try to load it into a temp table with a varchar data type if your txt file is extremely large and difficult to see. Easy enough to check for an int once loaded in the database.

Good luck.

MySQL Error 1265: Data truncated for column 'name' at row #

What is most likely happening, is the default command expects the terminating character to be '\n' when it is really '\r\n'; Try this command:

LOAD DATA INFILE 'file_location' INTO TABLE table_name 
FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n';

Error Code: 1265. Data truncated for column

The AVG() function on a NUMERIC(10,2) column like your price column produces a result with the type NUMERIC(14,6).

Demo:

mysql> create table t ( price numeric(10,2));

mysql> create table t2 as select avg(price) avg_price from t;

mysql> show create table t2\G
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `avg_price` decimal(14,6) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

(NUMERIC and DECIMAL are synonyms.)

When you try to stuff values with six digits of scale into a column with two digits of scale, MySQL must assume that it's possible that it will lose some information along the way.

Demo: Storing a number with two digits of scale is okay, and causes no warnings:

mysql> insert into t select 3.14;
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

But a number with greater scale results in a warning:

mysql> insert into t select 3.1415927;
Query OK, 1 row affected, 1 warning (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 1

mysql> show warnings;
+-------+------+--------------------------------------------+
| Level | Code | Message                                    |
+-------+------+--------------------------------------------+
| Note  | 1265 | Data truncated for column 'price' at row 1 |
+-------+------+--------------------------------------------+
1 row in set (0.00 sec)

You can explicitly cast the numeric expression to the same data type, to avoid the warning:

mysql> insert into t select cast(3.1415927 as decimal(10,2));
Query OK, 1 row affected (0.01 sec)
Records: 1  Duplicates: 0  Warnings: 0

ERROR 1265 (01000): Data truncated for column

The error is because of ENUM data type, it accept only your specific values.

Read more on : https://dev.mysql.com/doc/refman/8.0/en/enum.html

If you want to accept more values you should alter your table : How do I add more members to my ENUM-type column in MySQL?

ALTER TABLE
    `makina`
MODIFY COLUMN
    `ngjyra` enum(
        'existing_value1',
        'existing_value2',
        'existing_value3',
        'existing_value4',
        'new_value1',
        'new_value2'
    )
DEFAULT  `bardhe`;

mysql> error 1265: data truncated for column 'xxx' at row 1

During an INSERT, MySQL will tell you if you have too many columns. But apparently MySQL is not very kind: during a LOAD it does not tell you if you have too many columns.

Getting a mysql Error Code: 1265. Data truncated for column when doing a LOAD DATA LOCAL INFILE

In spite of the documentation that says empty entries are mapped to the value 0, LOAD DATA INFILE in fact doesn't handle empty entries. They must either have a value appropriate to the column's data type, or else the sequence \N to signify NULL.

See bug http://bugs.mysql.com/bug.php?id=64603

To fix this, you could substitute \N for empty entries with a sed command (or whatever equivalent text substitution tool you use on Windows).

See also MySQL load NULL values from CSV data

---

Related Topics