I am new to MySQL and am attempting to load Fangrpahs standard team batting data (in CSV format) into a MySQL database/table. The issue lies in my last column (playerid). My data looks as such
"Name","G","AB","PA","H","1B","2B","3B","HR","R","RBI","BB","IBB","SO","HBP","SF","SH","GDP","SB","CS","AVG","playerid"
"Nomar Garciaparra","38","156","169","50","35","7","3","5","24","21","8","2","16","4","1","0","4","2","0",".321","190"
"Trot Nixon","48","149","167","47","31","9","1","6","24","23","15","1","24","1","2","0","3","0","0",".315","204"
"Manny Ramirez","152","568","663","175","88","44","0","43","108","130","82","15","124","6","7","0","17","2","4",".308","210"
"Johnny Damon","150","621","702","189","128","35","6","20","123","94","76","1","71","2","3","0","8","19","8",".304","185"
Note that player ID is either a 3 or 4 digit integer depending on the player
I have been encountering multiple errors regarding the input of the 'playerid' data type. First I created my table:
CREATE TABLE redsoxbatting2004 (
-> Name Varchar(20),
-> G int,
-> AB int,
-> PA int,
-> H int,
-> 1B int,
-> 2B int,
-> 3B int,
-> HR int,
-> R int,
-> RBI int,
-> BB int,
-> IBB int,
-> SO int,
-> HBP int,
-> SF int,
-> SH int,
-> GDP int,
-> SB int,
-> CS int,
-> AVG DECIMAL(3,3),
-> playerid int);
Next I attempted to load in my data to redsoxbatting2004 but received Error 1265
load data infile 'Redsoxbatting2004.csv' into table redsoxbatting2004
-> fields terminated by ','
-> enclosed by '"'
-> lines terminated by '\n'
-> ignore 1 rows;
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
I tried changing the deleting the data table redsoxbatting2004 and recreating with different data types for playerid to Varchar(50) and Varchar(4) only to receive the two following errors respectively.
ERROR 1262 (01000): Row 1 was truncated; it contained more data than there were input columns
ERROR 1406 (22001): Data too long for column 'playerid' at row 1
I am not sure if this is a problem with my table generation, data input method or something else.
Thank you very much!
P.S This is my first question posed on Stack. If I have any formatting errors or if anything else can be done to make it proper please let me know