Written by Sai Sandeep Thota on March 16, 2013 in How to > SQL

How to Import CSV to MySQL

GET ALERTS:

Get our Latest updates delivered to your mailbox!

Do you have your client list in the form of Excel sheet and you would like to use it on your Website or a Project by retrieving the data from MySQL Database. You can do this by converting the Excel sheet into a Comma Separated Values (CSV ) file. So, How to import CSV to MySQL Database? How you can achieve this? You can do this by performing a couple of steps.

How to import CSV to MySQL

#0. First You will need a CSV data file.
#1. Create a Database. For example: Employees.
Syntax:

mysql > CREATE DATABASE EMPLOYEES;

create_database

#2. After creating a Database, You need to create Table to load data from the CSV file into that table.
Before loading data into the Table, You need to select the Database. So use:

USE EMPLOYEES;

So we have selected the Database Employees, next turn is to create a table.
remove_row_headings
Syntax:

mysql > CREATE TABLE tblname (FIELD1 datatype, FIELD2 datatype, FIELD3 datatype, FIELDn datatype);

create_table

#3. Now you need to change the CSV file a little bit by removing the row headings (Ex: ID, Name, Phone, etc.).
remove_row_headings

#4. Place the CSV file in the mysql/data/databasename folder.
#5. Now open your MySQL prompt and type in the following Command to import the data from *.CSV file to your Database Table:

mysql > LOAD DATA INFILE "Emp.csv" INTO TABLE People FIELDS TERMINATED BY ',' (ID, NAME, LOCATION);

import_csv

So the basic command would look like:

mysql > LOAD DATA INFILE "datafile.csv" INTO TABLE tblname FIELDS TERMINATED BY ',' (field1, field2, field3,fieldn);

This is the process involved in importing a CSV file into a MySQL Database.

{ 0 comments… add one now }

Leave a Comment

Previous post:

Next post: