CODEWITHSUNDEEP
mysqlcsvnullheidisql
francisMi
francisMi

Reputation: 935

HeidiSQL import csv empty fields

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

Upvotes: 7

Views: 9162

Answers (2)

francisMi
francisMi

Reputation: 935

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

Upvotes: 8

SDC
SDC

Reputation: 14222

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)

Upvotes: 5

Related Questions