Trying to import text file using below command
LOAD DATA LOCAL INFILE '/var/www/colleges.txt'
INTO TABLE selections LINES TERMINATED BY '\n'
SET unit=null, created=NOW(), type="college";
Table Structure
id INT Autoincremented
name Varchar
type Varchar
unit Varchar
crated datetime
But the above command is adding blank value for name field.
Here is my sample text file:
American River College
American University
Amherst College
You must specify the list of columns that are directly mapped from the text file surrounded in (), just before the SET clause:
LOAD DATA LOCAL INFILE '/var/www/colleges.txt'
INTO TABLE selections
LINES TERMINATED BY '\n'
-- Here, list columns from the file in ()
(name)
-- Followed by columns for which you set values
SET
unit=null,
created=NOW(),
type="college";
Given your input data, this is tested and working on my development database.
MariaDB [test]> select * from selections;
+----+------------------------+---------+------+---------------------+
| id | name | type | unit | created |
+----+------------------------+---------+------+---------------------+
| 1 | American River College | college | NULL | 2015-02-16 08:08:01 |
| 2 | American University | college | NULL | 2015-02-16 08:08:01 |
| 3 | Amherst College | college | NULL | 2015-02-16 08:08:01 |
+----+------------------------+---------+------+---------------------+
It is a little ambiguous in the documentation:
...snip...
[LINES
[STARTING BY 'string']
[TERMINATED BY 'string']
]
[IGNORE number LINES]
-- HERE....
[(col_name_or_user_var,...)]
[SET col_name = expr,...]
