How to load/migrate data from .CSV file to mySQL table? Samples and examples

How to load/migrate data from .CSV file to mySQL table?

Posted Date: 20-Aug-2012	Category: SQL
Author: Prachi Kulkarni	Member Level: Gold	Points: 15

Data migration means migrating data from one place to another place. Generally, mySQL queries are used to retrive ,add , update , delete data. We have used mySQL query for migration purpose. I hope this will be useful to all.

How to load/migrate data from .CSV file to mySQL table?
Introduction:
This will be useful if you want to migrate large volume of data from .CSV file to mySQL table. In such case, it is not possible to use insert query and migrate data one by one.
This will be the best practice to migrate data in a very short amount of time.
Steps:
You have to follow these steps -
1) In mySQL create a schema as firsttable.
2) Create table by this query -


DROP TABLE IF EXISTS `firsttable`.`MultipleOrdersDetails`;
CREATE TABLE  `firsttable`.`MultipleOrdersDetails` (
 `MultipleOrdersDetailsid` varchar(45) NOT NULL,
  `side` varchar(45) NOT NULL,
  `symbol` varchar(45) NOT NULL,
  `totalQuantity` varchar(45) NOT NULL,
  `totalPrice` varchar(45) NOT NULL,
  `createdDateForUsage` varchar(45) NOT NULL
)

It will create table MultipleOrdersDetails.
3) It will load data from .CSV file to table MultipleOrdersDetails. Make sure that .CSV has first line which is nothing but header that means it should be same as that of column names present in the table MultipleOrdersDetails.


LOAD DATA LOCAL INFILE 'D:\\records_for_loading.csv' INTO TABLE firsttable.MultipleOrdersDetails FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' (MultipleOrdersDetailsid, side, symbol, totalQuantity, totalPrice,	createdDateForUsage);

4) You can use following select query to see the data has been properly loaded in table or not.


SELECT * FROM firsttable.`MultipleOrdersDetails` ;

Make sure that datatype of columns and data present in .CSV file should be compatible for migration else sometimes data gets truncated while migration process.

Post Comment:

Notify me by email when others post comments to this article.

Do not include your name, "with regards" etc in the comment. Write detailed comment, relevant to the topic.

No HTML formatting and links to other web sites are allowed.

This is a strictly moderated site. Absolutely no spam allowed.

Name:	Sign In to fill automatically.
Email:	(Will not be published, but required to validate comment)
	Type the numbers and letters shown on the left.