How to load/migrate data from .CSV file to mySQL table?
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.
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.