MySQL: Import CSV - Revision history

Onnowpurbo: /* Referensi */

2020-05-27T23:09:40Z

Referensi

← Older revision	Revision as of 23:09, 27 May 2020
Line 112:	Line 112:




		==Youtube==

		* [https://youtu.be/f_Mw35YZqWQ YOUTUBE: MySQL excel csv ke table di database]

Onnowpurbo at 22:54, 27 May 2020

2020-05-27T22:54:01Z

← Older revision		Revision as of 22:54, 27 May 2020
Line 2:		Line 2:


	~~This tutorial shows you how to use the~~ LOAD DATA INFILE statement to import CSV file ~~into~~ MySQL table.		Disini kita akan menggunakan LOAD DATA INFILE statement untuk import CSV file ke 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.~~		Sebelum import, siapkan

	~~Before importing the~~ file, ~~you need to prepare the following:~~		* Database yang akan menerima data.
			* CSV file yang kolom-nya persis dengan kolom tabel di database.
			* Account di MySQL database server, yang punya ijin FILE & INSERT.

	~~A database table to which the data from the file will be imported.~~		Misalkan, kita mempunyai table discount dengan struktur
	~~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:~~		CREATE TABLE discounts (
	~~discounts table~~		id INT NOT NULL AUTO_INCREMENT,
			title VARCHAR(255) NOT NULL,
			expired_date DATE NOT NULL,
			amount DECIMAL(10 , 2 ) NULL,
			PRIMARY KEY (id)
			);

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

	~~CREATE TABLE discounts (~~		Cek lokasi tempat secure_file_priv
	~~id INT NOT NULL AUTO_INCREMENT,~~
	~~title VARCHAR(255) NOT NULL,~~
	~~expired_date DATE NOT NULL,~~
	~~amount DECIMAL(10 , 2 ) NULL,~~
	~~PRIMARY KEY (id)~~
	);

	~~The following~~ ~~discounts.csv file contains the first line as column headings and other three lines of data.~~		SHOW VARIABLES LIKE "secure_file_priv";
	~~discount csv file~~

	~~The following statement imports data from the~~ ~~c:\tmp\discounts.csv file into the discounts table.~~		+------------------+-----------------------+
			\| Variable_name \| Value \|
			+------------------+-----------------------+
			\| secure_file_priv \| /var/lib/mysql-files/ \|
			+------------------+-----------------------+

	~~LOAD DATA INFILE 'c:~~/~~tmp~~/~~discounts.csv'~~		Copykan file .csv yang akan di upload ke /var/lib/mysql-files/
	~~INTO TABLE discounts~~
	~~FIELDS TERMINATED BY ','~~
	~~ENCLOSED BY '"'~~
	~~LINES TERMINATED BY '\n'~~
	~~IGNORE 1 ROWS;~~

	~~The field of the~~ file ~~is terminated by a comma indicated by~~ ~~FIELD~~ TERMINATED BY ',' ~~and enclosed by double quotation marks specified by~~ ENCLOSED BY '" ‘.		cp *csv /var/lib/mysql-files/


			Load /var/lib/mysql-files/discounts.csv file ke discounts table.

			LOAD DATA INFILE '/var/lib/mysql-files/discounts.csv'
			INTO TABLE discounts
			FIELDS TERMINATED BY ','
			ENCLOSED BY '"'
			LINES TERMINATED BY '\n'
			IGNORE 1 ROWS;

			Cek menggunakan

			SELECT * FROM discounts;

	~~Each line of the CSV file is terminated by a newline character indicated by LINES TERMINATED BY '\n' .~~

	~~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 IGNORE 1 ROWS option.~~

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

	~~SELECT * FROM discounts;~~

	discounts table data		discounts table data

Onnowpurbo: Created page with "Sumber: https://www.mysqltutorial.org/import-csv-file-mysql-table/ This tutorial shows you how to use the LOAD DATA INFILE statement to import CSV file into MySQL table. Th..."

2020-05-27T22:02:33Z

Created page with "Sumber: https://www.mysqltutorial.org/import-csv-file-mysql-table/ This tutorial shows you how to use the LOAD DATA INFILE statement to import CSV file into MySQL table. Th..."

New page

Sumber: https://www.mysqltutorial.org/import-csv-file-mysql-table/

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:
discounts table

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)
);

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

The following statement imports data from the c:\tmp\discounts.csv file into the discounts table.

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

The field of the file is terminated by a comma indicated by FIELD TERMINATED BY ',' and enclosed by double quotation marks specified by ENCLOSED BY '" ‘.

Each line of the CSV file is terminated by a newline character indicated by LINES TERMINATED BY '\n' .

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 IGNORE 1 ROWS option.

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

SELECT * FROM discounts;

discounts table data
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 SET clause in the LOAD DATA INFILE statement.

Suppose the expired date column in the discount_2.csv file is in mm/dd/yyyy format.
discount_2.csv file

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');

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 LOCAL option in the LOAD DATA INFILE , the client program reads the file on the client and sends it to the MySQL server. The file will be uploaded into the database server operating system’s temporary folder e.g., C:\windows\temp on Windows or /tmp on Linux. This folder is not configurable or determined by MySQL.

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;

The only difference is the LOCAL option in the statement. If you load a big CSV file, you will see that with the LOCAL option, it will be a little bit slower to load the file because it takes time to transfer the file to the database server.

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

Importing the file from client to a remote database server using LOAD DATA LOCAL has some security issues that you should be aware of to avoid potential security risks.
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.
mysql workbench import csv

Click Import button, choose a CSV file and click Open button
import csv into mysql

Review the data, click Apply button.
edit table content
Review Data

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 LOCAL and using MySQL Workbench. With these techniques, you can load data from other text file formats such as tab-delimited.

==Referensi==

* https://www.mysqltutorial.org/import-csv-file-mysql-table/

==Pranala Menarik==

* [[MySQL]]