3 steps to follow:
Step1: Write a file publisher.txt in D: directory with following sample data:
P001;Jex Max Publication;New York;USA;New York;15;1969-12-25 P002;BPP Publication;Mumbai;India;New Delhi;10;1985-10-01 P003;New Harrold Publication;Adelaide;Australia;Sydney;6;1975-09-05 P004;Ultra Press Inc.;London;UK;London;6;1948-07-10 P005;Mountain Publication;Houstan;USA;Sun Diego;6;1975-01-01
Step 2: Create a table
CREATE TABLE publisher( pub_id VARCHAR(50) DEFAULT NULL, pub_name VARCHAR(50) DEFAULT NULL, pub_city VARCHAR(50) DEFAULT NULL, country VARCHAR(50) DEFAULT NULL, country_office VARCHAR(50) DEFAULT NULL, no_of_branch VARCHAR(50) DEFAULT NULL, estd DATE DEFAULT NULL );
Step 3: execute following MySQL command:
LOAD DATA INFILE "D:\\publisher.txt" INTO TABLE publisher FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (pub_id, pub_name, pub_city, country, country_office, no_of_branch, estd);
If you want to ignore some columns from csv (or txt), just use @dummy for those columns:
LOAD DATA INFILE "D:\\publisher.txt" INTO TABLE publisher FIELDS TERMINATED BY ';' LINES TERMINATED BY '\r\n' (pub_id, pub_name, @dummy, country, country_office, @dummy, estd);
Relevant post:
How-to Selectively Import Data from a Data File in MySQL 5
MySQL: Row N was truncated; a solution
Hi,
how to insert BIT column data when using the “list of columns” statement?
See also:
http://forums.devshed.com/mysql-help-4/load-data-infile-problem-with-bit-datatype-636151.html
(@var1) set active = cast(@var1 as unsigned)
Btw, great blog!
Cheers,
Harald
Yes, the solution is TINYINT (instead of BIT). Thanks.