I am trying to import the following CSV data into a fresh clean table with the below settings. And keep getting two entries added and then warnings. Why is it giving an error? I have changed it from Inno to MyISAM as I thought that might be the issue, but no luck.
16,"Data point 1"
15,"Data point 2"
14,"Point 3a"
13,"Data total"
After running this SQL
LOAD DATA LOCAL INFILE 'C:/Users/Me/CSV.csv' INTO TABLE points FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY ''
I get the following warning
2 row(s) affected, 2 warning(s): 1366 Incorrect integer value: ' 15' for column 'id' at row 2
The table is setup as follows.
id INT(11) Default 0 PK
value VARCHAR(45) Default NULL
Maybe your lines are not actually terminated by , but instead? That would explain the whitespace character in Incorrect integer value: ' 15' – it would just be the .
So try LINES TERMINATED BY ' ' instead.
It may be that your lines are not actually terminated by just '' but ' '.
If this is the case, you will most likely get the errors you are describing
Perhaps try something like:
load data local infile '<path2csv>' into table test.test_table fields terminated by ',' optionally enclosed by '"' lines terminated by '
'
I would also check the actual text file with an editor which has the option to show hidden characters, such as Vi (I believe you can get a graphical version of Vim for windows).