CODEWITHSUNDEEP
mysql
drzbir
drzbir

Reputation: 439

How to insert into my table from a file in mysql?

I have a file.dat that is as follow:

"Evan","E","Wallis","222222200","1958-01-16","134 Pelham, Milwaukee, WI","M","92000.00",null,"7"
"Jared","D","James","111111100","1966-10-10","123 Peachtree, Atlanta, GA","M","85000.00",null,"6"

I would like to insert these values into my table.

I tried the following but I did not succeed. 

LOAD DATA LOCAL INFILE "C:/file.dat" 
INTO TABLE table
;

There is no error but there is a warning and the table is not filled with the data. 

LOAD DATA LOCAL INFILE "C:/file.dat" INTO TABLE table
0 row(s) affected, 64 warning(s):
1265 Data truncated for column 'fname' at row 1
1261 Row 1 doesn't contain data for all columns...

Here is how the table is created.

CREATE TABLE table(
  fname    varchar(15) not null, 
  minit    varchar(1),
  lname    varchar(15) not null,
  ssn      char(9),
  bdate    date,
  address  varchar(50),
  sex      char,
  salary   decimal(10,2),
  superssn char(9),
  dno      integer(4),
  primary key (ssn),
  foreign key (superssn) references employee(ssn)
);

Upvotes: 0

Views: 67

Answers (1)

Curt
Curt

Reputation: 5722

By default, MySQL is looking for a tab-separated file. Try the following:

LOAD DATA LOCAL INFILE "C:/file.dat" 
INTO TABLE table
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
;

Upvotes: 1

Related Questions