Search code examples
mysqlexcelcsvimport-csv

MYSQL import from excel row import error 1364

Hello everyone please bear with me, im a student and im still learning,

so im trying to import data from an excel CSV file into my database, this method worked for me on every table except for my orders table which has a lot of foreign keys, when i tired to import data like i did before i keep getting an error related to default value for column, i managed to get some data by doing column by column but when i try the rest i get errors. any ideas? also some of the screenshots got cut off but rest assured my table is complete.

My table so far,error when trying to import only quantity,error when i try to import IDs,code for my orders table

Solution

  • Your source does not contain data for column OrderNumber. So import wizard does not mention this column in LOAD DATA / INSERT statement. The column have no default value. Server does not know what value must be assigned into this column. An error is generated.

    You may:

    Option 1. Alter table structure and add default value for this column. For example,

    ALTER TABLE tablename
    CHANGE COLUMN OrderNumber OrderNumber INT NULL DEFAULT NULL;

    Replace datatype/length with one you need. Set another default value if needed (for example, zero). Remove NULL (or replace with NOT NULL) if needed.

    Option 2. Edit your source (Excel file) and add a column with the name OrderNumber and some value which you need. Then export this changed file to CSV and import into the table.