MySQL CSV import using command line client

If you need to import a large CSV file into mysql DB of a remote/live server and you have access to ssh to that server (or even in your local machine also), you can use a sqlyog or some other program for that.

An alternative is to upload the CSV file on server and using mysql command line client of the server by issuing the below mysql commands.

>>>  use DATABASENAME;

 >>> Load data infile '<PHYSICAL PATH OF THE .CSV FILE>' into table < TABLE NAME >;

e.g.

Load data infile '/tmp/customer.csv' into table customer;

If variable "secure_file_priv" is set [ show variable like 'secure_file%' ], specifying local my work:

 >>> Load data local  infile '<PHYSICAL PATH OF THE .CSV FILE>' into table < TABLE NAME >;

For windows, new line characters may need to be specified:

 >>> Load data local  infile '<PHYSICAL PATH OF THE .CSV FILE>' into table < TABLE NAME > lines terminated by '\r\n';


To load data for selected fields, field names come at the end:

 >>> Load data local  infile '<PHYSICAL PATH OF THE .CSV FILE>' into table < TABLE NAME > lines terminated by '\r\n' (column1, column2 ... ) ;