CODEWITHSUNDEEP
mysqlmysql-workbench
Nicholas Pappas
Nicholas Pappas

Reputation: 10624

Import CSV to MySQL With Fewer Columns Than Destination Table

Is there a way to import a CSV into MySQL where the column numbers do not match?

I have a MySQL "Employees" table which we update once a month with a CSV file. I have added two additional columns to the "Employees" table that are relevant to our needs. Importing the table data, through MySQL Workbench, with the following SQL statement:

LOAD DATA LOCAL INFILE 'EmployeeDump-March.csv' REPLACE INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'

I receive the following error, which is absolutely correct... the column numbers don't match:

Error Code: 1261. Row 1 doesn't contain data for all columns    7.426 sec

The two extra fields have default values.

Is there a way to supress this error and have MySQL ignore those last two columns, leaving them alone?

Upvotes: 2

Views: 4057

Answers (1)

Ike Walker
Ike Walker

Reputation: 65527

You can (and should!) explicitly name the columns you are inserting into using LOAD DATA INFILE, just like a regular INSERT. You can also optionally set default values for the two new columns you added as part of the LOAD DATA INFILE statement.

Something like this. Just fill in the actual column names in the right order between the parens, and set the actual defaults and column names for the new columns if you don't want NULL:

LOAD DATA LOCAL INFILE 'EmployeeDump-March.csv' REPLACE INTO TABLE employees
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(old_column1, old_column2, ...)
set new_column1 = NULL,
  new_column2 = NULL

Upvotes: 3

Related Questions