How to insert csv file data into mysql database

1
votes

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

2
mysqlcsvimportexportmysql-workbench
it looks like your csv is not properly formatted. check line 1009. - Volkan Ulukut
@VolkanUlukut comma at 1009 row, If I remove data inserted but I want to insert that comma also. - UdayKiran Pulipati
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
@ZafarMalik you are correct. - UdayKiran Pulipati

2 Answers

3
votes

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.

0
votes

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/