Search code examples
mysqlimportload-data-infile

MySQL "load data local infile" just loads 1 row

I need to upload a csv file to my MySQL table. For some reason, when I run the command below in Mac Terminal, just one row is inserted. How can I upload all the rows?

load data local infile 'Research/cube_sample_data.csv' 
into table ad_spend 
fields terminated by ',' 
lines terminated by '\n';

I tried '\r\n' and ',\r\n' but no success. I keep getting this message:

Query OK, 1 row affected, 0 warnings (0.00 sec)
Records: 1  Deleted: 0  Skipped: 0  Warnings: 0

my file is like this: 

entertainment, games, 14

entertainment, movies, 12

and I have an empty table with 3 columns: 

category (varchar), subcat (varchar), adspend (int)
Solution

  • It seems that there's not only a comma between fields, but also a whitespace. So try to use:

    fields terminated by ', '