How do I load a CSV that has a column that is entirely empty?
I'm trying to load a CSV file into a database. There's a whole column that's completely empty, and I'm not sure what to do about it. I could go through and delete the column manually, but I suspect that would be cheating.
But I'm getting this error message:
10:36:41 LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csvData.csv" INTO TABLE theStates FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES (stateId, stateName, statePopulation, Growth, Pop2018, Pop2010, growthSince2010, Percent, Density) Error Code: 1366. Incorrect integer value: '' for column 'Pop2018' at row 1 0.000 sec
Here are the queries I use to create and populate a table:
USE states;
CREATE TABLE IF NOT EXISTS theStates (
stateId TINYINT UNSIGNED,
stateName TINYTEXT,
statePopulation INT UNSIGNED,
Growth FLOAT,
Pop2018 INT UNSIGNED,
Pop2010 INT UNSIGNED,
growthSince2010 FLOAT,
percent FLOAT,
density FLOAT,
PRIMARY KEY (stateId)
);
LOAD DATA INFILE "C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/csvData.csv"
INTO TABLE theStates FIELDS TERMINATED BY ","
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES
(stateId,
stateName,
statePopulation,
Growth,
Pop2018,
Pop2010,
growthSince2010,
Percent,
Density)
;
Here's the CSV file:
"rank","State","Pop","Growth","Pop2018","Pop2010","growthSince2010","Percent","density"
"1","California","39613493","0.0013","","37319502","0.0615","0.1184","254.2929"
"2","Texas","29730311","0.0125","","25241971","0.1778","0.0889","113.8081"
"3","Florida","21944577","0.0108","","18845537","0.1644","0.0656","409.2229"
"4","New York","19299981","-0.0040","","19399878","-0.0051","0.0577","409.5400"
"5","Pennsylvania","12804123","0.0001","","12711160","0.0073","0.0383","286.1704"
"6","Illinois","12569321","-0.0041","","12840503","-0.0211","0.0376","226.3967"
"7","Ohio","11714618","0.0011","","11539336","0.0152","0.0350","286.6944"
"8","Georgia","10830007","0.0099","","9711881","0.1151","0.0324","188.3054"
"9","North Carolina","10701022","0.0100","","9574323","0.1177","0.0320","220.1041"
"10","Michigan","9992427","0.0003","","9877510","0.0116","0.0299","176.7351"
"11","New Jersey","8874520","-0.0004","","8799446","0.0085","0.0265","1206.7609"
"12","Virginia","8603985","0.0040","","8023699","0.0723","0.0257","217.8776"
"13","Washington","7796941","0.0118","","6742830","0.1563","0.0233","117.3249"
"14","Arizona","7520103","0.0163","","6407172","0.1737","0.0225","66.2016"
"15","Tennessee","6944260","0.0084","","6355311","0.0927","0.0208","168.4069"
"16","Massachusetts","6912239","0.0014","","6566307","0.0527","0.0207","886.1845"
"17","Indiana","6805663","0.0054","","6490432","0.0486","0.0203","189.9644"
"18","Missouri","6169038","0.0026","","5995974","0.0289","0.0184","89.7419"
"19","Maryland","6065436","0.0016","","5788645","0.0478","0.0181","624.8518"
"20","Colorado","5893634","0.0116","","5047349","0.1677","0.0176","56.8653"
"21","Wisconsin","5852490","0.0026","","5690475","0.0285","0.0175","108.0633"
"22","Minnesota","5706398","0.0059","","5310828","0.0745","0.0171","71.6641"
"23","South Carolina","5277830","0.0124","","4635649","0.1385","0.0158","175.5707"
"24","Alabama","4934193","0.0032","","4785437","0.0311","0.0147","97.4271"
"25","Louisiana","4627002","-0.0023","","4544532","0.0181","0.0138","107.0966"
"26","Kentucky","4480713","0.0015","","4348181","0.0305","0.0134","113.4760"
"27","Oregon","4289439","0.0084","","3837491","0.1178","0.0128","44.6872"
"28","Oklahoma","3990443","0.0042","","3759944","0.0613","0.0119","58.1740"
"29","Connecticut","3552821","-0.0018","","3579114","-0.0073","0.0106","733.7507"
"30","Utah","3310774","0.0161","","2775332","0.1929","0.0099","40.2918"
"31","Puerto Rico","3194374","0.0001","","3721525","-0.1416","0.0095","923.4964"
"32","Nevada","3185786","0.0169","","2702405","0.1789","0.0095","29.0195"
"33","Iowa","3167974","0.0020","","3050745","0.0384","0.0095","56.7158"
"34","Arkansas","3033946","0.0027","","2921964","0.0383","0.0091","58.3059"
"35","Mississippi","2966407","-0.0016","","2970548","-0.0014","0.0089","63.2186"
"36","Kansas","2917224","0.0007","","2858190","0.0207","0.0087","35.6808"
"37","New Mexico","2105005","0.0019","","2064552","0.0196","0.0063","17.3540"
"38","Nebraska","1951996","0.0045","","1829542","0.0669","0.0058","25.4087"
"39","Idaho","1860123","0.0200","","1570746","0.1842","0.0056","22.5079"
"40","West Virginia","1767859","-0.0068","","1854239","-0.0466","0.0053","73.5443"
"41","Hawaii","1406430","-0.0033","","1363963","0.0311","0.0042","218.9678"
"42","New Hampshire","1372203","0.0046","","1316762","0.0421","0.0041","153.2674"
"43","Maine","1354522","0.0038","","1327629","0.0203","0.0040","43.9167"
"44","Montana","1085004","0.0075","","990697","0.0952","0.0032","7.4547"
"45","Rhode Island","1061509","0.0010","","1053959","0.0072","0.0032","1026.6044"
"46","Delaware","990334","0.0084","","899593","0.1009","0.0030","508.1242"
"47","South Dakota","896581","0.0067","","816166","0.0985","0.0027","11.8265"
"48","North Dakota","770026","0.0052","","674715","0.1413","0.0023","11.1596"
"49","Alaska","724357","-0.0049","","713910","0.0146","0.0022","1.2694"
"50","District of Columbia","714153","0.0059","","605226","0.1800","0.0021","11707.4262"
"51","Vermont","623251","-0.0006","","625879","-0.0042","0.0019","67.6197"
"52","Wyoming","581075","0.0020","","564487","0.0294","0.0017","5.9847"
What do I do?