Tutorial Belajar MySQL Part 20: Cara Menambahkan data dari File (LOAD DATA INFILE)

Pada tutorial MySQL sebelumnya: Cara Menambahkan data ke dalam Tabel (INSERT), kita menambahkan data langsung pada cmd MySQL. Untuk keperluan menambahkan data dalam jumlah besar, akan lebih efisien dan mudah jika data tersebut kita siapkan dalam bentuk file terlebih dahulu, dan kemudian diload ke dalam tabel. Untuk keperluan ini MySQL menyediakan query LOAD DATA INFILE.

Mempersiapkan Tabel Contoh: daftar_dosen

Sebagai tabel contoh untuk mempelajari cara menambahkan data menggunakan query LOAD DATA INFILE, kita akan mempersiapkan tabel daftar_dosen, dengan query sebagai berikut:

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mahasiswa          |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.08 sec)

mysql> USE mahasiswa;
Database changed

mysql> CREATE TABLE daftar_dosen (NIP CHAR(10) PRIMARY KEY,
nama_dosen VARCHAR(50) NOT NULL, no_hp CHAR(13),
alamat VARCHAR(100));
Query OK, 0 rows affected (0.16 sec)

mysql> DESC daftar_dosen;
+------------+--------------+------+-----+---------+-------+
| Field      | Type         | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+-------+
| NIP        | char(10)     | NO   | PRI | NULL    |       |
| nama_dosen | varchar(50)  | NO   |     | NULL    |       |
| no_hp      | char(13)     | YES  |     | NULL    |       |
| alamat     | varchar(100) | YES  |     | NULL    |       |
+------------+--------------+------+-----+---------+-------+
4 rows in set (0.14 sec)

Cara Penulisan dasar query LOAD DATA INFILE

Query MySQL: LOAD DATA INFILE digunakan untuk menginput data kedalam tabel dimana data yang diinput berasal dari sebuah file. Kita akan mempelajari cara penggunaannya dengan contoh dalam tutorial kali ini.

Query lengkap dari LOAD DATA INFILE sesuai dari manual MySQL adalah sebagai berikut:

LOAD DATA [LOW_PRIORITY | CONCURRENT] [LOCAL] INFILE 'file_name'
    [REPLACE | IGNORE]
    INTO TABLE tbl_name
    [CHARACTER SET charset_name]
    [{FIELDS | COLUMNS}
        [TERMINATED BY 'string']
        [[OPTIONALLY] ENCLOSED BY 'char']
        [ESCAPED BY 'char']
    ]
    [LINES
        [STARTING BY 'string']
        [TERMINATED BY 'string']
    ]
    [IGNORE number {LINES | ROWS}]
    [(col_name_or_user_var,...)]
    [SET col_name = expr,...]

Dapat kita lihat bahwa MySQL menyediakan banyak pilihan opsional (dalam tanda kurung siku) yang dapat digunakan tergantung kebutuhan. Dalam tutorial MySQL ini, kita akan membahas beberapa diantaranya.

Cara Penggunaan query LOAD DATA INFILE dalam MySQL

Agar lebih gampang dipahami, kita akan langsung mencoba query LOAD DATA INFILE, dengan membuat sebuah file input_data.txt. Anda bebas untuk meletakkan file tersebut. Pada contoh ini, saya akan menempatkannya pada folder "D:\MySQL\".

Buka notepad, lalu tuliskan data berikut ke dalam file text tersebut, dan save sebagai input_data.txt:

"0576431001","M. Siddiq","0812979005","Jakarta"
"0770435006","Siswanto","0812567765","Medan"
"0869437003","Andi Mulia","0812332564","Padang"
"0260432002","Maya Ari Putri","0812345234","Palembang"
"1080432007","Arif Budiman","0812456345","Makasar"
"0275430005","Susi Indriani","0812656532","Bogor"

Berikut tampilan file input_data.txt

Pada saat membuat file, pastikan menekan Enter di akhir baris untuk membuat baris baru. Selanjutnya buka MySQL Client command prompt dan jalankan query berikut:

mysql> LOAD DATA INFILE 'D:\\MySQL\\input_data.txt'
INTO TABLE daftar_dosen FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES TERMINATED BY '\r\n';
Query OK, 6 rows affected (0.05 sec)
Records: 6  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM daftar_dosen;
+------------+----------------+------------+-----------+
| NIP        | nama_dosen     | no_hp      | alamat    |
+------------+----------------+------------+-----------+
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani  | 0812656532 | Bogor     |
| 0576431001 | M. Siddiq      | 0812979005 | Jakarta   |
| 0770435006 | Siswanto       | 0812567765 | Medan     |
| 0869437003 | Andi Mulia     | 0812332564 | Padang    |
| 1080432007 | Arif Budiman   | 0812456345 | Makasar   |
+------------+----------------+------------+-----------+
6 rows in set (0.00 sec)

Mari kita bahas tentang penulisan perintah LOAD DATA INFILE diatas:

• Hal pertama yang kita tulis setelah perintah LOAD DATA INFILE adalah lokasi tempat file input_data.txt berada. Dalam query diatas, file tersebut berada pada D:\MySQL\input_data.txt. Tetapi karena didalam MySQL karakter '\'merupakan karakter khusus, maka penulisannya harus di-double, sehingga menjadi D:\\MySQL\\input_data.txt. Jika lokasi file anda berbeda, tinggal menyesuaikan saja.

• Setelah lokasi file, selanjutnya kita menambahkan perintah ke tabel mana file tersebut akan diinput. Pada contoh kita, tabel tersebut adalah daftar_dosen, sehingga ditulis …INTO TABLE daftar_dosen…

• Perintah FIELDS TERMINATED BY ','  dimaksudkan sebagai instruksi pada MySQL bahwa setiap kolom pada input_data.txt dipisahkan oleh karakter koma ','. Namun tidak harus tanda koma, tanda lain seperti titik, tanda '|', maupun karakter tab bisa kita gunakan sebagai tanda pemisah, dan instruksikan MySQL untuk menggunakan tanda tersebut sebagai penanda kolom.

• Instruksi ENCLOSED BY '"' memberitahu MySQL agar menghapus penanda kutip dua (") dari tiap kolom. Namun jika pada daftar_dosen.txt kita tidak menambahkan tanda kutip dua diawal data, MySQL tetap akan menerima data tersebut.

• Pada akhir query, perintah …LINES TERMINATED BY '\r\n' menginstruksikan MySQL agar tiap baris diakhiri dengan karakter new line dan carriage return. Karakter khusus ini terbentuk ketika kita menekan Enter pada keyboard untuk pindah baris pada notepad. '\r' merupakan cara penulisan untuk karakter carriage return, sedangkan '\n' adalah karakter new line.

Berbagai pilihan instruksi dari query LOAD DATA INFILE

Jika kita melihat format penulisan query LOAD DATA INFILE, terdapat beberapa pilihan opsi lainnya, kita akan membahas beberapa diantaranya:

Penjelasan opsi LOCAL

Opsi LOCAL disini digunakan untuk menentukan lokasi dari text yang diinput. Jika kita menuliskan LOCAL, maka file text akan dicari dalam komputer Client MySQL. Namun jika tidak ditulis, maka lokasi file akan dicari pada komputer MySQL Server. Karena kita mengistall MySQL pada satu komputer, opsi LOCAL tidak akan berpengaruh, karena MySQL Server dan MySQL Client berada pada komputer yang sama, dan juga folder yang sama (file mysqld.exe adalah MySQL Server, dan file mysql.exe adalah MySQL Client). Contoh penggunaan query-nya: LOAD DATA LOCAL INFILE 'path_to_file'

Penjelasan opsi [REPLACE | IGNORE]

Opsi [REPLACE | IGNORE] berkaitan dengan cara MySQL menangani duplikasi data pada kolom PRIMARY KEY. Jika opsi REPLACE digunakan, maka saat ditemukan data yang sama, maka data yang baru akan menimpa data lama, namun jika menggunakan opsi IGNORE, data yang baru akan diabaikan dan MySQL akan menjalankan baris berikutnya.

Penjelasan opsi LINES STARTING BY 'string' dan TERMINATED BY 'string'

Opsi ini digunakan untuk menentukan awal dari data dan akhir dari data. String disini dapat berupa karakter seperti "," atau "*", maupun karakter new line dan carriage return.

Penggunaan karakter-karakter khusus dapat dilihat dari tabel dibawah ini:

Penjelasan opsi IGNORE number

Opsi IGNORE dapat digunakan untuk mengistruksikan MySQL agar melompati beberapa baris dan memulai dari baris ke sekian. Hal ini berguna jika pada awal text merupakan penjelasan nama kolom.

Contoh Cara Penggunaan query LOAD DATA INFILE dengan opsi LOCAL, REPLACE dan LINES STARTING BY

Agar memahami penggunaan LOCAL, REPLACE dan LINES STARTING BY, kita akan mencoba menggunakan query LOAD DATA INFILE untuk file kedua berikut.

Kali ini saya akan membuat file data_lagi.txt, dengan isian sebagai berikut:

NIP                  Nama          No Hp          Kota
**'0876439004'   'Mulyono'       '0812912312'  'Semarang'|
**'0770435006'   'Rubin Hadi'    '0812567678'  'Papua'|
**'0869437003'   'Mustalifah'    '0812338877'  'Aceh'|
**'0160436012'   'Sabrina Sari'  '0812349900'  'Pekanbaru'|
**'0480432066'   'Tia Santrini'  '0812451177'  'Padang'|

Berikut tampilan file input_data.txt

Perhatikan bahwa setiap baris diawali dengan tanda bintang dua kali (**), diakhiri dengan karakter pipa (|) dan setiap kolom dibatasi dengan tab. Juga pada baris ke 2 dan 3 akan terdapat duplikasi untuk kolom NIP dengan data sebelumnya. Baris pertama dari text adalah judul kolom.

Agar file diatas dapat diproses oleh MySQL, kita perlu menggunakan perintah tambahan. Penulisan query untuk menginput file ini adalah sebagai berikut:

mysql> SELECT * FROM daftar_dosen;
+------------+----------------+------------+-----------+
| NIP        | nama_dosen     | no_hp      | alamat    |
+------------+----------------+------------+-----------+
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani  | 0812656532 | Bogor     |
| 0576431001 | M. Siddiq      | 0812979005 | Jakarta   |
| 0770435006 | Siswanto       | 0812567765 | Medan     |
| 0869437003 | Andi Mulia     | 0812332564 | Padang    |
| 1080432007 | Arif Budiman   | 0812456345 | Makasar   |
+------------+----------------+------------+-----------+
6 rows in set (0.00 sec)

mysql> LOAD DATA LOCAL INFILE 'D:\\MySQL\\data_lagi.txt' REPLACE
INTO TABLE daftar_dosen FIELDS TERMINATED BY '\t'
ENCLOSED BY '\'' LINES STARTING BY '**' TERMINATED BY '|'
IGNORE 1 LINES;
Query OK, 6 rows affected (0.02 sec)
Records: 4  Deleted: 2  Skipped: 0  Warnings: 0

mysql> SELECT * FROM daftar_dosen;
+------------+----------------+------------+-----------+
| NIP        | nama_dosen     | no_hp      | alamat    |
+------------+----------------+------------+-----------+
| 0160436012 | Sabrina Sari   | 0812349900 | Pekanbaru |
| 0260432002 | Maya Ari Putri | 0812345234 | Palembang |
| 0275430005 | Susi Indriani  | 0812656532 | Bogor     |
| 0480432066 | Tia Santrini   | 0812451177 | Padang    |
| 0576431001 | M. Siddiq      | 0812979005 | Jakarta   |
| 0770435006 | Rubin Hadi     | 0812567678 | Papua     |
| 0869437003 | Mustalifah     | 0812338877 | Aceh      |
| 1080432007 | Arif Budiman   | 0812456345 | Makasar   |
+------------+----------------+------------+-----------+
8 rows in set (0.00 sec)

Query diatas menambahkan 4 baris dan menghapus 2 baris, hal ini dapat dilihat setelah query dijalankan Records: 4  Deleted: 2  Skipped: 0  Warnings: 0. Hal ini dikarenakan opsi REPLACE yang menggantikan 2 baris yang memiliki NIP yang sama.

Dalam tutorial belajar MySQL kali ini kita telah membahas cara menambahkan data ke tabel MySQL dari sebuah file text external menggunakan query LOAD DATA INFILE. Melanjutkan pembahasan mengenai query dasar MySQL, berikutnya kita akan membahas tentang Cara Menampilkan Data MySQL dengan query SELECT.