Monday 31 October 2016

I have recently gone through a task where it was required to load a CSV file into a MySQL table so I opted to do it in a different way rather than using traditional load data local infile method. I have used MySQL 5.7.15 and steps may be the same in almost all the versions

First of all the table structure is required to be created for the table for which data is required to be loaded, as we would be using CSV engine so it requires that all columns should be defined as not null. The example below defines a sample table with two sample columns both of integer data type.
mysql> create table table1

à(col1 int notnull,

àcol2 int notnull)

àengine=CSV;


it would result in two files at OS level which would be table1.CSM & table1.CSV, now the file with name table1.CSV can have comma separated values. Either copy paste the conetents from your souce CSV file or move the source file with this name and then change permissions to mysql:mysql for whole folder. Once there is data in CSV file you can use mysql to show the data in table
mysql> select * from table1;
if your data is not being shown, please use
mysql> flush tables;
or 
mysql> flush tables table1 ;

and then
mysql> select * from table1;

Hope this helps for users wants to do similar tasks.