Uulamock Uulamock - 10 days ago 5
MySQL Question

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

I am attempting to read a file and use the data in it for one of my tables. However, I get the error stating: ERROR 1265 (01000): Data truncated for column 'ERA' at row 1.

I understand what the error means - that the data is to large for the data type. However, the columns 'ERA' is the last piece of information for the first row in the .txt file. (what's in .txt file below).

So I tested it by manually inputting the same information in the first row. it accepted the data just fine. So I deleted the manually added row and then tried to load only 1 single row from the .txt file. This worked.

I only get the error when I have more than 1 row of data to load from the file.

I figure it has something to do with it reading the Enter Key value or something, but I have no idea how to fix it to be readable. Does something need to be put at the end of each row in the file? If so, what?

(Note about me: I am only learning MySQL temporarily to get past a certain point in my life. I only have a beginner's knowledge on it since its not my plan to use it in the future. So if there is something special I have to do in MySQL to fix the issue, please give a clear example. At least enough information for me to Google more examples)

Contents of .txt file:

Arizona Diamondbacks 1998 1819 914 905 1 1 8379 62131 16137 1933 .260 8422 4.26

Atlanta Braves 1876 19764 9786 9825 17 3 88243 677310 176434 12203 .260 87693 3.65

Baltimore Orioles 1901 16861 7965 8786 7 26 72633 572146 148197 11335 .259 77029 3.99

Boston Red Sox 1901 16848 8657 8108 12 7 77981 575510 153781 11671 .267 75352 3.88

Chicago Cubs 1876 19796 10103 9537 16 2 91616 678492 178828 12479 .264 88269 3.66

Chicago White Sox 1901 16855 8476 8276 6 3 73603 570404 148833 9662 .261 72730 3.73

Cincinnati Reds 1882 19382 9766 9480 10 5 87300 661241 173334 11533 .262 86119 3.71

Cleveland Indians 1901 16863 8571 8201 5 2 76441 575356 153431 11338 .267 74685 3.82

Colorado Rockies 1993 2565 1203 1362 1 0 13426 88540 24687 2944 .279 14085 5.17

Detroit Tigers 1901 16885 8497 8295 10 4 78163 575699 152746 12050 .265 77194 3.96

Florida Marlins 1993 2561 1214 1347 2 2 11353 87282 22744 2392 .261 12150 4.39

Houston Astros 1962 7526 3754 3766 1 0 31521 255339 65127 5533 .255 31387 3.75


(Note about the file: in the actual file, there is no space between each line as in it doesn't look double spaced. At the end of each row, enter is pressed in the file. The space between each data is a tab (which seems to be fine). The file is saved from Notepad if that also has something to do with it.)

The Table stats:

Field | Type | Null | Default |

<------------------------------------------------->

Team | varchar(50) | YES | NULL |

FirstYear | int(11) | YES | NULL |

G | int(11) | YES | NULL |

W | int(11) | YES | NULL |

L | int(11) | YES | NULL |

Pennants | int(11) | YES | NULL |

WS | int(11) | YES | NULL |

R | int(11) | YES | NULL |

AB | int(11) | YES | NULL |

H | int(11) | YES | NULL |

HR | int(11) | YES | NULL |

AVG | float | YES | NULL |

RA | int(11) | YES | NULL |

ERA | float | YES | NULL |

Answer

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';