Load data infile default value not inserting in table

2
votes

I'm importing a csv file using LOAD DATAINFILE

The csv columns are NAME,TYPE,STATUS

my table structure is 

Name  : varchar
TYPE  : Varchar
Status: Tiny int default value 1

I use this stmt :

LOAD DATA INFILE '/var/www/names.csv' 
INTO TABLE users FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES

When I use the above statement it has to insert the status value as 1 for each row, but it is not inserting the same. Please advise.

2
phpmysql
Are your "lines" really terminated with space characters? - eggyal
Post example CSV data. I find it doubtful that you have ' ' line terminators instead of '\n' - Michael Berkowski
What does the CSV actually contain in the STATUS column for records that are being incorrectly inserted? - eggyal
CSV does not contain status column ,but default i want to give status as 1 during insertion. - mark rammmy
You can do an update action to set Status column the same value in table after loading all data. - Ynjxsjmh

2 Answers

9
votes

When loading a file, MySQL expects that it has the same number of columns as the destination table, unless you specify otherwise, even if the missing column has a default value. So supply a column list to your LOAD statement, and a literal 1 for the value of STATUS:

LOAD DATA INFILE '/var/www/names.csv' 
INTO TABLE users 
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
  IGNORE 1 LINES 
  (`name`, `type`, 1)

You can also do it with a SET clause:

LOAD DATA INFILE '/var/www/names.csv' 
INTO TABLE users 
  FIELDS TERMINATED BY ',' 
  ENCLOSED BY '"' 
  LINES TERMINATED BY '\n' 
  IGNORE 1 LINES 
  (`name`, `type`)
  SET `status` = 1
0
votes

After you execute the query , check for any warnings using

show warnings

Default value is 0 for numeric data types and '' for strings.

So you should have set default value 1 while creating the table

create table tableName
(
name nvarchar(100) not null,
type nvarchar(100) not null,
status smallint default 1
);