Cara menggunakan import csv to mysql

This tutorial shows you how to use the LOAD DATA INFILE statement to import CSV file into MySQL table.

The  LOAD DATA INFILE statement allows you to read data from a text file and import the file’s data into a database table very fast.

Before importing the file, you need to prepare the following:

• A database table to which the data from the file will be imported.
• A CSV file with data that matches with the number of columns of the table and the type of data in each column.
• The account, which connects to the MySQL database server, has FILE and INSERT privileges.

Suppose we have a table named discounts with the following structure:

We use CREATE TABLE statement to create the discounts table as follows:

CREATE TABLE discounts (
    id INT NOT NULL AUTO_INCREMENT,
    title VARCHAR(255) NOT NULL,
    expired_date DATE NOT NULL,
    amount DECIMAL(10 , 2 ) NULL,
    PRIMARY KEY (id)
);
Code language: SQL (Structured Query Language) (sql)

The following  discounts.csv file contains the first line as column headings and other three lines of data.

The following statement imports data from the  

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;



Code language: SQL (Structured Query Language) (sql)

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Code language: SQL (Structured Query Language) (sql)

The field of the file is terminated by a comma indicated by 

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Code language: SQL (Structured Query Language) (sql)

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Code language: SQL (Structured Query Language) (sql)

Each line of the CSV file is terminated by a newline character indicated by

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Code language: SQL (Structured Query Language) (sql)

Because the file has the first line that contains the column headings, which should not be imported into the table, therefore we ignore it by specifying 

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Code language: SQL (Structured Query Language) (sql)

Now, we can check the discounts table to see whether the data is imported.

SELECT * FROM discounts;

Transforming data while importing

Sometimes the format of the data does not match the target columns in the table. In simple cases, you can transform it by using the

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Code language: SQL (Structured Query Language) (sql)

Suppose the expired date column in the 

LOAD DATA INFILE 'c:/tmp/discounts.csv' 
INTO TABLE discounts 
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Code language: SQL (Structured Query Language) (sql)

SELECT * FROM discounts;

When importing data into the discounts table, we have to transform it into MySQL date format by using str_to_date() function as follows:

LOAD DATA INFILE 'c:/tmp/discounts_2.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title,@expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');
Code language: SQL (Structured Query Language) (sql)

Importing file from client to a remote MySQL database server

It is possible to import data from client (local computer) to a remote MySQL database server using the LOAD DATA INFILE statement.

When you use the 

SELECT * FROM discounts;

SELECT * FROM discounts;

SELECT * FROM discounts;

Let’s take a look at the following example:

LOAD DATA LOCAL INFILE  'c:/tmp/discounts.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;
Code language: SQL (Structured Query Language) (sql)

The only difference is the

SELECT * FROM discounts;

SELECT * FROM discounts;

The account that connects to MySQL server doesn’t need to have the FILE privilege to import the file when you use the

SELECT * FROM discounts;

Importing the file from client to a remote database server using 

LOAD DATA INFILE 'c:/tmp/discounts_2.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title,@expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');
Code language: SQL (Structured Query Language) (sql)

Importing CSV file using MySQL Workbench

MySQL workbench provides a tool to import data into a table. It allows you to edit data before making changes.

The following are steps that you want to import data into a table:

Open table to which the data is loaded.

Click Import button, choose a CSV file and click Open button

Review the data, click Apply button.

MySQL workbench will display a dialog  “Apply SQL Script to Database”, click Apply button to insert data into the table.

We have shown you how to import CSV into MySQL table using

LOAD DATA INFILE 'c:/tmp/discounts_2.csv'
INTO TABLE discounts
FIELDS TERMINATED BY ',' ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(title,@expired_date,amount)
SET expired_date = STR_TO_DATE(@expired_date, '%m/%d/%Y');
Code language: SQL (Structured Query Language) (sql)

Apa langkah langkah untuk mengimpor data dari file CSV?

CSV dibuka dengan apa?

Extension CSV file apa?

Bagaimana convert CSV to excel?