Unable to load from csv to mysql table?

Question

 LOAD DATA LOCAL INFILE 'hr.csv' INTO TABLE hr_analytics  FIELDS TERMINATED BY '`' IGNORE 1 LINES(NO,SATISFACTION_LEVEL,LAST_EVALUATION,NUMBER_PROJECT,AVERAGE_MONTLY_HOURS,TIME_SPEND_COMPANY,WORK_ACCIDENT,LEFT,PROMOTION_LAST_5YEARS,SALES,SALARY)

when i run above query i get:

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 'LEFT,PROMOTION_LAST_5YEARS,SALES,SALARY)' at line 1

how to solve this?

user3151902 · Accepted Answer · 2017-08-18 08:08:30Z

1

The problem seems to be that LEFT is a MySQL key word. Try this:

LOAD DATA LOCAL INFILE 'hr.csv' INTO TABLE hr_analytics  FIELDS TERMINATED BY '`' IGNORE 1 LINES(`NO`,`SATISFACTION_LEVEL`,`LAST_EVALUATION`,`NUMBER_PROJECT`,`AVERAGE_MONTLY_HOURS`,`TIME_SPEND_COMPANY`,`WORK_ACCIDENT`,`LEFT`,`PROMOTION_LAST_5YEARS`,`SALES`,`SALARY`)

edited Aug 18, 2017 at 8:08

user3151902

3,1881 gold badge22 silver badges36 bronze badges

answered Aug 18, 2017 at 8:06

Idriss Benbassou

1,6462 gold badges18 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

smv Over a year ago

i have already ran the query and i got com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Unknown column 'NO' in 'field list'

user3151902 Over a year ago

Please post the table definition and the first 2 or 3 lines (anonymized!) of the CSV!

Idriss Benbassou Over a year ago

Try using different quotes in lines as the identifier quote character is the backtick. Otherwise MySQL "thinks" that you point to a columns. Try this : LOAD DATA LOCAL INFILE 'hr.csv' INTO TABLE hr_analytics FIELDS TERMINATED BY '`' IGNORE 1 LINES('NO','SATISFACTION_LEVEL','LAST_EVALUATION','NUMBER_PROJECT','AVERAGE_MONTLY_HOURS','TIME_SPEND_COMPANY','WORK_ACCIDENT','LEFT','PROMOTION_LAST_5YEARS','SALES','SALARY')

Szymon Roziewski · Accepted Answer · 2017-08-18 08:07:55Z

0

You have a problem because you use a reserved SQL word 'LEFT'.

If an identifier contains special characters or is a reserved word, you must quote it whenever you refer to it.

The identifier quote character is the backtick "`".

answered Aug 18, 2017 at 8:07

Szymon Roziewski

1,0312 gold badges29 silver badges40 bronze badges

1 Comment

smv Over a year ago

1LOAD DATA LOCAL INFILE 'hr.csv' INTO TABLE hr_analytics FIELDS TERMINATED BY '' IGNORE 1 LINES(NO,SATISFACTION_LEVEL,LAST_EVALUATION,NUMBER_PROJECT,AVERAGE_MONTLY_HOURS,TIME_SPEND_COMPANY,WORK_ACCIDENT,LEFT,PROMOTION_LAST_5YEARS,SALES,SALARY) when i run the above query i get Unknown column 'NO' in 'field list'

Collectives™ on Stack Overflow

Unable to load from csv to mysql table?

2 Answers 2

3 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related