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

contoh data LOAD DATA INFILE MySQLE MySQL

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:

PenulisanPenjelasan Karakter
\0Karakter ASCII untuk NULL (0x00)
\'Karakter tanda kutip satu (')
\"Karakter tanda kutip dua(")
\bKarakter backspace
\nKarakter newline (linefeed)
\rKarakter carriage return
\tKarakter untuk tab
\ZASCII 26 (Control+Z)
\\Karakter untuk backslash (\)
\%Karakter untuk tanda persen (%)
\_Karakter untuk tanda garis bawah (_)

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

contoh data LOAD DATA INFILE MySQL

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.


Tutorial Terkait:

56 Comments

  1. kickymaulana
    10 Feb 15
  2. izzunabdussalam
    19 May 15
    • Andre
      21 May 15
  3. ria
    21 May 15
    • Andre
      24 May 15
  4. ariadi
    21 May 15
    • Andre
      24 May 15
  5. Doni Nurramdan
    16 Oct 15
    • Andre
      17 Oct 15
  6. EDG
    12 Nov 15
    • Zuhair
      26 Jan 17
  7. tgkrisna
    20 Dec 15
  8. one
    22 Dec 15
    • Andre
      23 Dec 15
  9. one
    24 Dec 15
    • Andre
      26 Dec 15
      • JANG
        11 Apr 16
        • ary
          11 Aug 16
  10. corokun
    05 Jan 16
  11. reza
    16 Feb 16
    • Andre
      18 Feb 16
  12. iqbal
    09 Apr 16
  13. adi
    18 Apr 16
    • Andre
      18 Apr 16
    • Anonymous
      10 Oct 16
  14. Alfian W
    19 Apr 16
  15. Anonymous
    08 May 16
    • Andre
      08 May 16
  16. munir
    19 May 16
  17. Vahienly
    29 May 16
  18. Vahienly
    29 May 16
    • Andre
      22 Sep 16
  19. Jakfar
    11 Jun 16
    • Andre
      12 Jun 16
  20. ary
    11 Aug 16
    • Andre
      11 Aug 16
  21. Kholid
    16 Aug 16
  22. SA
    21 Sep 16
    • Andre
      22 Sep 16
      • SA
        23 Sep 16
        • SA
          23 Sep 16
        • Andre
          24 Sep 16
  23. SA
    25 Sep 16
    • Andre
      25 Sep 16
  24. fau
    06 Oct 16
    • Andre
      06 Oct 16
  25. astaanza
    25 Oct 16
    • Andre
      26 Oct 16
  26. Akbar Rizky
    24 Feb 17
    • Andre
      24 Feb 17
  27. joni
    27 Feb 17
    • Andre
      28 Feb 17
  28. Ryan
    18 Mar 17
  29. Ryan
    18 Mar 17
  30. iqbal
    22 Sep 17
  31. iqbal
    22 Sep 17

Add Comment