HeidiSQL import csv empty fields

Question

I want to import a csv file to my MySQL database with HeidiSQL.

But some of my fields are empty.

What could I do to let HeidiSQL know these empty values have to be seen as NULL-values?

Sample of csv-file (last 2 fields not yet known):

    NULL;Students Corner;437452182;;

Create commands:

    CREATE  TABLE `db`.`customers` (
         `company_id` INT NOT NULL AUTO_INCREMENT ,
         `company_name` VARCHAR(40) NULL ,
         `company_number` INT NULL ,
         `company_vat` INT NULL ,
         `company_zip` INT NULL,
    PRIMARY KEY (`company_id`) );

I get these error:

    Incorrect integer value: '' for column 'company_id' at row 1 */
    Incorrect integer value: '' for column 'company_vat' at row 1 */
    Incorrect integer value: '' for column 'company_zip' at row 1 */
    etc

You could do it using LOAD DATA INFILE command with SET clause - replace empty strings with NULL values. — Devart, Sep 07 '12 at 13:36

score 8 · Accepted Answer · answered Sep 07 '12 at 14:29

8

If solved it by writing \N in each empty field instead of writing NULL !

answered Sep 07 '12 at 14:29

francisMi

925
3
15
31

1

that's good. don't forget to accept your answer if its the one you used. I'd still say the `LOAD DATA INFILE` query solution is better, because one doesn't alwawys have the option to pre-process a CSV file before importing it. Writing the query allows you to put all of that logic into a single script, which means next time you can just run it as a single command, and not worry about checking the contents of the file. – SDC Sep 07 '12 at 14:37
1

Wow, this answer is a total lifesaver! I've been trying to import a huge CSV file (200k rows and 34 columns), and it has been driving me crazy for throwing me errors and warnings about NULL values. It seems that using `\N` in place of `NULL` or just a blank field worked magic! :) – Terry Sep 27 '13 at 12:48
the `Fields Escaped By` character must be \ in Heidi to recognize that syntax. – Doug0 Aug 31 '15 at 20:26

score 5 · Answer 2 · answered Sep 07 '12 at 14:19

You can import CSV files into MySQL using a LOAD DATA INFILE query.

In your case, you would write something like this:

LOAD DATA INFILE filename.txt
INTO TABLE customers
FIELDS TERMINATED BY ';'
LINES TERMINATED BY '\n'
(@id, @name, @number, @vat, @zip)
SET
  company_id     = (CASE WHEN @id='' THEN NULL ELSE @id END),
  company_name   = (CASE WHEN @name='' THEN NULL ELSE @name END),
  company_number = (CASE WHEN @number='' THEN NULL ELSE @number END),
  company_vat    = (CASE WHEN @vat='' THEN NULL ELSE @vat END),
  company_zip    = (CASE WHEN @zip='' THEN NULL ELSE @zip END)

(you may need to adjust this, depending on your end of line markers, etc, but this should be pretty close to what you need)

HeidiSQL import csv empty fields

2 Answers2