~ istana kesabaran jiwa

Jumat, 15 Agustus 2014

19.36

Unknown

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:

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:

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

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 filemysql.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 ‘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, 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.

jiks menggunakan command promt, maka lokasi file adalah relatif pada folder dinama filemysql.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: