CODEWITHSUNDEEP
mysqlexcelmysql-workbenchsql-insertbulkinsert
jkid
jkid

Reputation: 31

Issue with importing column with null values from Excel to MYSQL Workbench

I have a MYSQL database that I manage using MYSQL Workbench. I am trying to import data from Excel (converted to CSV, of course) into an existing table, which I do quite often and generally without problem. Technically from MYSQL's perspective, MYSQL Workbench treats this by generating a series of "Insert into" statements, not as an import statement.

I'm having trouble because the particular set of data that I'm importing has no values in the last column. In the database, the last column in the table expects a 2-digit number (or NULL). When I import the data and MYSQL Workbench turns it into "Insert into" statements, I'd expect the value inserted in the last column to be NULL. Instead, I keep getting a 1644 error for each and every row that it is trying to insert.

Here's what I've tried that hasn't worked: - Leaving the last column blank - Filling the column with the word NULL - Filling the column with the characters \N - Adding one fake row (to be deleted later) that actually has a value in the last column so that it can realize there's a column there

I'm out of ideas. Ideally I would like to stick to my process of using MYSQL Workbench's "import" (i.e. Insert into) feature so that I can add blocks of data from Excel, and I can't be manually editing each line in the database.

EXAMPLE (when I just leave the last column blank) Here's what a row of data looks like in the CSV: 4209,Reading,2015,1. Fall,10/12/15,114,212,3.4,93

Here's what the auto-generated "Insert into" looks like: INSERT INTO Tests (TestID, Subject, Year, Season, TestStartDate, Duration, RIT, StdError, Percentile, AccuratePercentile) VALUES ('4209', 'Reading', '2015', '1. Fall', '10/12/15', '114', '212', '3.4', '93', '');

Here's what the error looks like (I get one of these for each "Insert into") ERROR 1644: 1644: Error: Trying to insert an incorrect value in Percentile/Duration/RIT/AccuratePercentile; TestID: 4209 SQL Statement: INSERT INTO Tests (TestID, Subject, Year, Season, TestStartDate, Duration, RIT, StdError, Percentile, AccuratePercentile) VALUES ('4209', 'Reading', '2015', '1. Fall', '10/12/15', '114', '212', '3.4', '93', '')

Upvotes: 1

Views: 6620

Answers (1)

Mike Lischke
Mike Lischke

Reputation: 53522

The way you are doing the import (via the resultset import) is merely designed for simple cases. You cannot configure much there, but it requires only very few steps.

For a more powerful import try the relatively new Table Import (use the context menu in the schema tree on a schema or table node). This allows to import CSV + JSON data and you can configure details much more (file encoding, columns to import, data types etc.). The table import doesn't use INSERT statements but the LOAD DATA command and hence is much faster too.

enter image description here

Upvotes: 1

Related Questions