[FIXED] Correct Syntax for CSV import to SQL MAMP Powered Database

Issue

I am running MAMP on my local machine, and using this command for an import works successfully but it is throwing an error on my syntax.

/applications/MAMP/library/bin/mysql -u testtest -p test < /Users/myName/info.csv 

I have been importing/exporting a lot of CSV's into different databases and have noticed that the syntax is slightly different for different DBs, and giving errors sometimes with or without quotes etc. I'm wondering if someone could explain this to me, and what may be the problem in this particular case as this is the format my CSV export came in when I exported it using a Drupal UI.

ID,First,Last,e-mail,Points,kID,gID,fID,tID,rID
"45","mark","brown","test@test.com","234","","34","","532",""
"353","sam","harris","newtest@test.com","343","3432","","43","","87"

I am getting this error which is why I think this

ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ID,First,Last,e-mail,Points,kID,gID,f' at line 1

Solution

For import csv files use 'load data syntax' instead. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

The IGNORE number LINES option can be used to ignore lines at the start of the file. For example, you can use IGNORE 1 LINES to skip over an initial header line containing column names:

LOAD DATA INFILE '/Users/myName/info.csv' INTO TABLE test IGNORE 1 LINES;

Therefore, you can use the following statement:

LOAD DATA LOCAL INFILE '/Users/myName/info.csv'
INTO TABLE test
FIELDS TERMINATED BY ','
    ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES

Answered By - malyy