CODEWITHSUNDEEP
mysqlfiletextload
Neeraj
Neeraj

Reputation: 9145

import text file into one column and assign default value to other columns of mysql table

Text file:

American River College
American University
Amherst College
......
......

Table Fields

id
name
type
created_at
updated_at

I want to import text field values in name column and want to assign some default value to other fields like "college" to type field, current date time to created_at and NULL to updated_at columns.

What changes i would need to make in the following command

LOAD DATA LOCAL INFILE '/var/www/colleges.txt'
INTO TABLE selections
ENCLOSED BY '"' 
LINES TERMINATED BY '\r\n';

I have seen various examples of stackoverflow.com but could not find matching with my specific requirement.

Upvotes: 2

Views: 1510

Answers (1)

Tomas M
Tomas M

Reputation: 7333

You need to add SET statement at the end of your LOAD DATA INFILE. For example:

LOAD DATA LOCAL INFILE '/var/www/colleges.txt'
INTO TABLE selections
FIELDS ENCLOSED BY '"' 
LINES TERMINATED BY '\n'
(name)
SET updated_at=null, created_at=NOW(), type="college"

Upvotes: 2

Related Questions