MySQL Forums
Forum List  »  Newbie

How do I load a CSV that has a column that is entirely empty?
Posted by: Montana Burr
Date: January 06, 2022 11:44AM

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?

Options: ReplyQuote


Subject
Written By
Posted
How do I load a CSV that has a column that is entirely empty?
January 06, 2022 11:44AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.