LOAD DATA INFILE: Load File CSV Ukuran Besar ke Database MySQL

Banyak cara yang dapat kita lakukan untuk memasukkan data ke database MySQLmulai dari yang berukuran kecil (terdiri dari beberapa puluh row) hingga jutaan row. Pembahasan berikut ini akan membahas salah satu dari beberapa cara untuk melakukannya. Dalam kasus ini, kita akan mengupload data dengan format csv yang berukuran ~120Mb, dengan jumlah row sebanyak 400rb an, dan dengan jumlah kolom sebanyak 36

Cara yang paling sering kita gunakan adalah upload data menggunakan phpMyAdmin. Hal tersebut menjadi tidak bermanfaat jika file yang kita upload ukurannya besar. Cara yang paling cepat adalah menggunakan syntax MySQL “LOAD LOCAL DATA INFILE”

Yang perlu diperhatikan adalah MySQL akan memasukkan data sesuai dengan urutan data csv dan memasukkannya ke tabel sesuai urutan kolom, jadi misal kita punya tabel mahasiswa dengan field:

Nim | Nama | Alamat | jurusan | tingkat | sks

maka data file csv yang kita buat harus berurutan sesuai nilai dari field tersebut, misal:

05655458, Ahmad Mu’minun,”Jl. Patriot RT.02, RW.03, Semarang”,Informatika,2,154

Untuk membuat file csv, dapat dilakukan melalui berbagai cara diantaranya:

1. Menggunakan php
   Misal:

   $mhs = '05655458, Ahmad Mu'minun,"Jl. Patriot RT.02, RW.03, Semarang",Informatika,2,154' . "n";
   ...
   $mhs .= '07856585, Fathanah,"Jl. Merdeka No. 01, Kendal",Akuntansi,1,144';

2. Menggunakan spreadsheet
   Yang perlu diperhatikan adalah pembatas fieldnya, biasanya jika regional settingnya Indonesia maka pembatasnya titik koma (;) sedangkan jika disetting US masa pembatasnya adalah koma (,). Untuk mengeceknya, setelah file csv di simpan, buka file tersebut menggunakan notepad atau notepad++.

Selanjutnya kita jalankan query MySQL LOAD DATA INFILE. Query tersebut dapat dijalankan dengan PHP maupun command promt. Contoh dengan script php:

mysql_query ("
		LOAD DATA LOCAL INFILE '$file_csv'
		INTO TABLE $nama_tabel
		FIELDS TERMINATED BY ','
		ESCAPED BY '\'
		OPTIONALLY ENCLOSED BY "
		LINES TERMINATED BY '\n'
		IGNORE 1 LINES
	      ')

Penjelasan:

• • • $file_csv merupakan lokasi file relatif pada root yaitu htdocs misal file berada di htdocs/app/files/csv/data.csv, maka:

      $file_csv = 'app/files/csv/data.csv'

      jika menggunakan command promt, lokasi relatif terhadap direktori lokasi file mysql.exe namun bisa juga ditulis absolut, misal:

      LOAD DATA LOCAL INFILE 'D:\data.csv'

    • FIELDS TERMINATED BY diisikan karakter pemisah antar kolom biasanya koma (,) atau titik koma (;)
    • ESCAPED BY diisi dengan karakter escape dalam contoh diatas adalah backslash karena ada karakter single quote (‘) yang di escape dengan \ , yaitu Mu’minun
    • OPTIONALLY ENCLOSED BY diisi karakter penutup suatu field, misal dari contoh diatas, karena field alamat mengandung koma, maka field dibatasi dengan dua tanda kutip (“)
    • LINES TERMINATED BY diisi karakter pemisah antar baris, biasanya ‘\n’ atau ‘\r\n’
    • IGNORE … LINES diisi dengan jumlah awal baris yang di skip, misal kita membuat filecsv dari spreadsheet dengan layout sbb:
      

      +----------+----------------+------------------------+-------------+---------+-----+
      |    nim   |   nama         | alamat                 | jurusan     | tingkat | sks |
      +----------+----------------+------------------------+-------------+---------+-----+
      | 05655458 | Ahmad Mu'minun | Jl. Patriot , Semarang | Informatika | 4       |  64 |
      +----------+----------------+------------------------+-------------+---------+-----+

      maka ada 1 baris yang perlu di-skip, yaitu baris header: nim, nama, dst… sehingga IGNORE … LINES diisi 1

Pertama: kita memasukkan data apa adanya, sehingga syntax yang kita jalankan

mysql_query ('
		LOAD DATA LOCAL INFILE 'app/files/data.csv'
		INTO TABLE mahasiswa
		FIELDS TERMINATED BY ','
		ESCAPED BY ''
		OPTIONALLY ENCLOSED BY "
		LINES TERMINATED BY 'n'
	     ')

maka, hasil yang kita peroleh:

Kedua: jika kita menginginkan field mana saja yang ingin kita masukkan datanya, maka kita dapat mendefinisikan field tersebut dengan cara menyebutkan field tersebut. Misal dengan tabel mahasiswa tadi, kita akan memasukkan data csv tanpa nama:

'05655458,"Jl. Patriot RT.02, RW.03, Semarang",Informatika,2,154'

maka syntax nya kita ubah menjadi:

mysql_query ('
		LOAD DATA LOCAL INFILE 'app/files/data.csv'
		INTO TABLE mahasiswa
		FIELDS TERMINATED BY ','
		ESCAPED BY ''
		OPTIONALLY ENCLOSED BY "
		LINES TERMINATED BY 'n'
		(nim, alamat, jurusan, tingkat, sks)
	     ')

hasil yang kita peroleh:

Ketiga: jika kita memiliki data scv: 05655458, Ahmad Mu’minun,”Jl. Patriot RT.02, RW.03, Semarang”,Informatika,2,154 namun ingin tidak ingin memasukkan nilai nama, maka syntax kita ubah menjadi:

mysql_query ('
		LOAD DATA LOCAL INFILE 'app/files/data.csv'
		INTO TABLE mahasiswa
		FIELDS TERMINATED BY ','
		ESCAPED BY ''
		OPTIONALLY ENCLOSED BY "
		LINES TERMINATED BY 'n'
		(nim, @skip, alamat, jurusan, tingkat, sks)
	     ')

kita memasukkan variabel @skip yang tidak ada nilainya, untuk menggantikan column nama. Hasilnya adalah:

Keempat: kita ingin memanipulasi data yang diinput. Misal kita ingin field sks dengan nilai yang diinput setengah dari sks dan untuk field semester, jika nilai semester = 2 maka field diisi duakali nilai semester. Untuk melakukan hal tersebut, kita menggunakan perintah set, sehingga scriptnya menjadi:

mysql_query ('
		LOAD DATA LOCAL INFILE 'app/files/data.csv'
		INTO TABLE mahasiswa
		FIELDS TERMINATED BY ','
		ESCAPED BY ''
		OPTIONALLY ENCLOSED BY "
		LINES TERMINATED BY 'n'
		set tingkat = IF(tingkat = 2, tingkat*2,tingkat),
	            sks     = sks/2
	     ')

Hasil yang kita peroleh adalah:

Menggunakan command promt:

Untuk menggunakan command promp, terlebih dahulu kita perlu login ke database mysql dan memilih database yang akan kita gunakan. Rekan dapat membaca tutorial ini.

jika menggunakan command promt, maka lokasi file adalah relatif pada folder dinama file mysql.exe berada, namun juga dapat ditulis absolute misal: D\:data.csv. selain itu untuk penulisan backslash (\) harus di escape sehingga penulisannya menjadi (\\). Contoh jika syntax dijalankan menggunakan command promt:

Selanjutnya setelah penjelasan diatas, mari kita coba load file dengan ukuran yang lebih besar. file yang akan kita coba terdiri dari 380.000 rows dengan ukuran ~61Mb. Hasilnya sebagai berikut:

dari gambar tersebut terlihat bahwa waktu yang diperlukan adalah 8.11 detik

Dengan script PHP

Untuk menggunakan perintah LOAD DATA LOCAL INFILE pada script PHP, caranya tinggal masukkan SQL diatas ke script PHP. Contoh sebagai berikut:

<?php
$conn = mysqli_connect('localhost', 'root', '', 'nama_database');
$sql = "LOAD DATA LOCAL INFILE '$file_csv'
	INTO TABLE $nama_tabel
	FIELDS TERMINATED BY ';'
	OPTIONALLY ENCLOSED BY '" . '"' . "'
	LINES TERMINATED BY '\n'
	IGNORE 1 LINES";
		
mysqli_query($conn, $sql);

Demikian sekilas tentang syntax MySQL LOAD DATA INFILE, selamat mencoba…..