mysqllinuxmysql-loadfile

ERROR 1083 (42000) at line 1: Field separator argument is not what is expected when using mysql LOAD

I am trying to use mysql load via the shell prompt. I want to load a CSV file directly into the database.

mysql -u root -p -h mysql -e "LOAD DATA INFILE 'Subscriber.csv' INTO TABLE temp_data FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'IGNORE 1 ROWS" psi

I am getting the error ERROR 1083 (42000) at line 1: Field separator argument is not what is expected

Below is my CSV file

misdn,city,age,gender 771XXXXXX,MUTOKO,24,MALE 771XXXXXX,MUTOKO,32,MALE 771XXXXXX,MUTOKO,37,Male 771XXXXXX,MUTOKO,36,MALE 771XXXXXX,MUTOKO,25,Male 771XXXXXX,HWEDZA,26,MALE 771XXXXXX,HWEDZA,33,MALE 771XXXXXX,MUTOKO,26,MALE 771XXXXXX,HWEDZA,34,MALE 771XXXXXX,HWEDZA,34,MALE 771XXXXXX,MUTOKO,21,MALE 771XXXXXX,MUTOKO,22,MALE 771XXXXXX,MUTOKO,30,MALE 771XXXXXX,MUTOKO,28,Male 771XXXXXX,MUTOKO,33,Male 771XXXXXX,MUTOKO,23,Male 771XXXXXX,ZVISHAVANE,31,Male 771XXXXXX,ZVISHAVANE,39,MALE

Please help, what I am doing wrong?

Solution

  • You used ENCLOSED BY '"', but your fields do not contain double quotes at all. I think you should have used OPTIONALLY ENCLOSED BY '"'. Try this version:

    mysql -u root -p -h mysql -e "LOAD DATA INFILE 'Subscriber.csv'
    INTO TABLE temp_data
    FIELDS TERMINATED BY ','
    OPTIONALLY ENCLOSED BY '"'
    LINES TERMINATED BY '\n'IGNORE 1 ROWS" psi

    Or, if you are certain that no fields will ever have double quotes, you could remove the ENCLOSED BY clause entirely.