Loading Data From CSV File Into MySQL Table

This is my first ever post in MySQL, Reason is that I just started working in MySQL very recently. My requirement of the day was very simple, I had to load a set of data rows from a csv file into a table.

I was very sure that, there should be something similar to sqlldr in MySQL, and this is what I found and completed my work.

1)    There is a default data_dir from which this “LOAD DATA” command picks the data (This is found in data_dir parameter in /etc/mysql/my.cnf)

2)     Copy your CSV file in the /var/lib/mysql/ directory

Note:- Each schema in your database will have a directory of its own.

ubuntu@omegha-erp:~$ mysql -uroot -p***

Welcome to the MySQL monitor.  Commands end with ; or \g.

Your MySQL connection id is 6784

Server version: 5.5.47-0ubuntu0.14.04.1 (Ubuntu)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its

affiliates. Other names may be trademarks of their respective

owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use erp;

Reading table information for completion of table and column names

You can turn off this feature to get a quicker startup with -A

Database changed

mysql> LOAD DATA INFILE 'Stock1.csv'  INTO TABLE stockmaster;

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`erp`.`stockmaster`, CONSTRAINT `stockmaster_ibfk_1` FOREIGN KEY (`categoryid`) REFERENCES `stockcategory` (`categoryid`))

3)    Since my data load failed with foreign key constraint and I was yet to prepare the master table data for load, I decided to disable to constraints for proceeding with my data load, and this is how I disabled the constraints check.

mysql> SET foreign_key_checks = 0;

Query OK, 0 rows affected (0.01 sec)

4)    LOAD DATA again and this time it was successful. But all my CSV values in the csv file got loaded into the first column itself, I decided to delete the rows and LOAD DATA again correctly.

mysql> LOAD DATA INFILE 'Stock1.csv'  INTO TABLE stockmaster;

Query OK, 161 rows affected, 4669 warnings (0.10 sec)

Records: 161  Deleted: 0  Skipped: 0  Warnings: 4669

mysql> delete from stockmaster;

Query OK, 164 rows affected (0.08 sec)

mysql> LOAD DATA INFILE 'Stock1.csv'  INTO TABLE stockmaster COLUMNS TERMINATED BY ',' LINES TERMINATED BY '\n';

Query OK, 161 rows affected, 214 warnings (0.10 sec)

Records: 161  Deleted: 0  Skipped: 0  Warnings: 214

mysql>