ffCCBot/2.0 (http://commoncrawl.org/faq/)
NEW!! Buku Query MySQL Lihat Detail » x

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 ~61Mb.

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 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 pembatas field 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 ‘rn’
      • 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, 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:

result

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:

result2

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:

result3

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:

result4

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.

jiks 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:

result5

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:

result6
dari gambar tersebut terlihat bahwa waktu yang diperlukan adalah 8.11 detik

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

Recomended Post

8 Feedback dari pembaca

Silakan tinggalkan komentar

Like Us

Dapatkan update artikel terbaru via E-Mail
  1. Eksklusif Cheat Sheet PHP Bahasa Indonesia – Pendamping Belajar PHP

  2. PHP FORM III: Menampilkan Data MySQL Dengan PHP dan Form HTML

  3. Tutorial PHP Form II: Menampilkan Hasil Input Form HTML dengan PHP

  4. Tutorial PHP Form I: Menghubungkan Form HTML dengan PHP

  5. Menampilkan Data Dari Beberapa Tabel MySQL – JOIN Pada MYSQL

  6. Memahami dan Menampilkan Tanggal dan Waktu Pada PHP

  7. Menghitung Selisih Waktu Dengan PHP – Cara Termudah

  8. Memahami Fungsi Date Pada PHP

  9. Memahami Fungsi Time, Strtotime, dan Mktime Pada PHP – Memanipulasi Waktu

  10. Memahami Zona Waktu (Timezone) dan Selisih Waktu Pada PHP