How to insert csv file data into mysql database

Question

I want to insert data into mysql table from csv file. Import data from region_codes.csv file. In region_codes.csv file having 3 columns in 3rd columns it had , separated data, include those commas how to insert in mysql.

DROP TABLE IF EXISTS `region_codes`;
CREATE TABLE `region_codes` (
    `country_code` CHAR(2) NULL,
    `region_no` varchar(5) NOT NULL,
    `region` VARCHAR(45) NULL,
    INDEX `idx_country_code` (`country_code`)
) COLLATE='utf8_bin' ENGINE = MyISAM;

Using LOAD DATA LOCAL INFILE I import the data but only 1000 rows are imported outof 4066 rows.

LOAD DATA LOCAL INFILE 'C:/region_codes.csv' INTO TABLE `region_codes` FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

How to insert huge amount of data into mysql region_codes table from csv file.

Screenshot: Action output of MySql

it looks like your csv is not properly formatted. check line 1009. — Volkan Ulukut, Mar 25 '14 at 09:47
@VolkanUlukut comma at 1009 row, If I remove data inserted but I want to insert that comma also. — UdayKiran Pulipati, Mar 25 '14 at 09:55
your csv data is manually prepared or exported from some where else....problem seems in csv data as if any field contains comma then whole field data should be enclosed in double quotes (") but in your data it will not. — Zafar Malik, Mar 25 '14 at 10:02

Zafar Malik · Accepted Answer · 2014-03-25T13:44:16.033

3

You can try below syntax if it works for you otherwise provide csv data:

LOAD DATA LOCAL INFILE 'C:/region_codes.csv' INTO TABLE `region_codes` FIELDS ESCAPED BY '\\' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n';

If above syntax does not work then export data by below command again and import by below given command.

select * into outfile 'C:/region_codes.csv' fields terminated by ',' optionally enclosed by '"' lines terminated by '\n' from `region_codes`;

Now use below command (to ignore column heading line)

LOAD DATA LOCAL INFILE 'C:/region_codes.csv' INTO TABLE `region_codes` FIELDS ESCAPED BY '\\' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES;

Note: If data is prepared manually then need to correct it manually.

If still not work then attach your csv data to check exact problem.

edited Mar 25 '14 at 13:44

answered Mar 25 '14 at 13:39

Zafar Malik

6,734
2
19
30

data is inserted into mysql database when csv file has having commas in columns. Thank you Malik. – UdayKiran Pulipati Mar 26 '14 at 05:15
`\n is for unix \r is for mac \r\n is for windows format`. These are line separators for supported by OS. – UdayKiran Pulipati Mar 26 '14 at 05:19
@udaykiran you are right, I missed to change it as per linux/unix. – Zafar Malik Mar 26 '14 at 06:59

score 0 · Answer 2 · answered Mar 25 '14 at 10:57

You can use mysqlimport tool this way:

mysqlimport  --ignore-lines=1 --fields-terminated-by=,
--columns='ID,Name,Phone,Address' --local -u root -p
Database /path/to/csvfile/TableName.csv

Here is a full explanation: http://chriseiffel.com/everything-linux/how-to-import-a-large-csv-file-to-mysql/

How to insert csv file data into mysql database

2 Answers2

Linked