Create Table From CSV in MySQL

CSV (Comma Separated Value)  files are a type of file containing data frames that are separated by a comma (generally). These files are textual in format and aren’t confined to a specific program or standard, due to which they are widely used. It is quite common for data frames to be stored in form of CSV files, which could later be used to retrieve data easily. 

In this article, we will learn how to create a table from a CSV file in MySQL. 

A distribution of MySQL is required to be installed in the Operating System. For the purpose of Demonstration we would be using the following CSV file to create a table:

This CSV file contains 4 columns:

1. DATE
2. TOTAL_TIME
3. HOURS
4. MINUTES

Creation of Skeleton Table:

STEP 1: We would be creating a table in MySQL, which contains the info about each column of the CSV file. Then we would copy the data from the CSV file to each of the columns.  

Query:

CREATE TABLE table_name ( csv_column_1 
DATATYPE, csv_column_2 DATATYPE,
 csv_column_3 DATATYPE ....);

Where the last 2 entries could be extended to the number of columns in the CSV file. i.e. csv_column_2/3/4… goes on to the number of columns in the CSV file.  In our case, there are 4 columns each of which has the following data types:

DATE            => Date
TOTAL_TIME      => INT
HOURS           => INT
MINUTES         => INT

So to create a table for the above file, the query would be:

Query:

CREATE TABLE TRANSFER ( DATE DATE, 
TOTAL_TIME INT, HOURS INT, MINUTES INT);

This would create a table. To view the table enter the query:

Query:

SELECT * FROM TRANSFER;

Which displays an empty table. Since we haven’t added any data to it. 

STEP 2: 

Now to add the data from the CSV file to it, run the following:

Query:

LOAD DATA INFILE 'file.csv' 
INTO TABLE TRANSFER
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

The  LOAD DATA INFILE statement allows you to read data from a text file and import the file’s data into a database. Upon execution of the above statement we display the table again using:

Query:

SELECT * FROM TRANSFER;

To which the resulting table is:

Therefore in this way, you could import data from a CSV file into a MySQL Database. 

Explanation:

The first line tells the compiler to load data from a file named ‘file.csv‘ into the table named TRANSFER. The second line states that the fields (different cells) are terminated by a comma, cell data could be enclosed between double quotes (to allow the usage of commas within the cell), and lines are terminated by a new line character. The third line tells the compiler to ignore the first line, as it contains the names of the columns of the table, which we already mentioned while creating the table.

Therefore the blueprint for loading the data to a table would be:

Query:

LOAD DATA INFILE '_path_to_csv_' 
INTO TABLE TABLE_NAME
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

where,

_path_to_csv_  => path to the csv file
TABLE_NAME     => Name of the table (skel table) 
in which the data is to be copied

If the excel file has different delimiters, that could also be mentioned in the second line.