How to import .txt or .csv file data into MySQL?

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

Advertisements