Sunday, January 23, 2011

MySQL Load Data Infile

Pada sebuah kasus terdapat sebuah data pada file excel yang perlu di inputkan pada table mysql.
Apabila jumlah data tersebut hanya beberapa buah menginputkan data dengan cara manual yaitu, memasukan satu persatu data tersebut ke table database melalui aplikasi atau SQL mungkin terasa ringan, tetapi bila jumlah data tersebut berjumlah puluhan ribu, rasanya sangat melelahkan dan memerlukan waktu yang lama sekali untuk melakukannya.

Pada Database MySQL memiliki fitur LOAD DATA INFILE. fitur tersebut adalah untuk memasukan data ke table database dengan sumber data berasal dari file.

Database MySQL yang saya gunakan versi 5.1 dengan Operation System Ubuntu 11.04 - the Natty Narwhal

Berikut ini adalah langkah-langkah nya

1. Rubah data pada Excel menjadi bertipe CSV

a. Hilangkan Judul Kolom pada File Excel, menjadi seperti contoh dibawah ini


b .Lakukan konversi ke csv dan berikan delimiter koma (koma sebagai contoh) untuk membedakan antar field.



c. Bila konversi Excel ke CSV berhasil maka akan menghasilkan file berekstensi *.csv yang bila file tersebut dibuka akan tampak seperti dibawah ini



2. Lakukan perintah load file csv seperti ini contoh dibawah ini, sebelumnya Anda masuk terlebih dahulu ke prompt command line mysql melalui console

Pola
mysql -u[username] -p[password mysql] -e "LOAD DATA LOCAL INFILE '[alamat file csv]' INTO TABLE [nama table tujuan] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'" [nama database tujuan]
Contoh
mysql -uroot -ppass -e "LOAD DATA LOCAL INFILE '/home/dendie/Desktop/negara.csv' INTO TABLE tbl_negara FIELDS TERMINATED BY ','  LINES TERMINATED BY '\n'" db_teritorial

Berikut ini adalah penjelasan dari script diatas.
  • mysql mengartikan menggunakan service database mysql
  • -u adalah untuk memasukan username database mysql
  • -p adalah untuk memasukan password database mysql
  • -e adalah argumen untuk memberikan perintah eksekusi kepada mysql
  • LOAD DATA LOCAL INFILE adalah perintah untuk melakukan load/memuat data dari sebuah file
  • INTO TABLE adalah tujuan table untuk data yang di load
  • FIELDS TERMINATED BY ',' mengartikan bahwa karakter koma sebagai pemisah antar kolom dari data yang di load
  • TERMINATED BY '\n' mengartikan bawah karakter \n atau baris baru sebagai pemisah setiap baris dari data yang di load
  • db_teritorial adalah nama tujuan database untuk data yang di load

Berikut ini adalah contoh hasil import data menggunakan LOAD DATA LOCAL INFILE

Data ditampilkan menggunakan software MySQL Query Browser Versi 1.2.12 by MySQL AB

13 comments:

  1. makasih oom.
    mau nanya kalo cuman mau input ke kolom tertentu aja gimana.
    misal:
    --
    id int(10) auto_increment,
    nama,
    kelas,
    umur
    --
    ini mau input kolom nama dan kelas saja, id dan umur diabaiken. bijimanne?

    ReplyDelete
  2. bisa mas... pada saat load data cukup spesikan nama kolom yang manjadi tujuan import data, seperti contoh dibawah ini

    LOAD DATA INFILE 'data.csv' INTO TABLE tbl_siswa (nama,kelas);

    atau lengkapnya seperti ini.

    mysql -uroot -ppass -e "LOAD DATA LOCAL INFILE '/home/dendie/Desktop/data.csv' INTO TABLE tbl_siswa (nama,kelas) FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'" db_teritorial

    ReplyDelete
  3. permisi om,, kalo boleh nanya apakah script di atas bisa di pakai buat aplikasi php..?

    ReplyDelete
  4. bisa.. masukan script diatas di dalam function php exec

    ReplyDelete
  5. Maaf mas saya mau tanya, saya coba script yg ini LOAD DATA INFILE 'data.csv' INTO TABLE tbl_siswa (nama,kelas); ko ga bisa ya? ada notifikasi error seperti ini :
    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fields terminated by ','' at line 1


    saya butuh script load yang tidak memasukkan semua fields suatu table. tks atas bantuannya

    ReplyDelete
  6. This comment has been removed by the author.

    ReplyDelete
  7. Scriptnya kurang lengkap mas.. seharusnya seperti contoh dibawah ini

    mysql -uroot -ppass -e "LOAD DATA LOCAL INFILE '/home/dendie/Desktop/data.csv' INTO TABLE tbl_siswa (nama,kelas) FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'" db_teritorial

    ReplyDelete
  8. terima kasih atas responsnya.. oh maaf... saya kurang copy-nya. scriptnya sudah lengkap seperti yang Mas Dendie tulis:

    LOAD DATA LOCAL INFILE '/home/dendie/Desktop/data.csv' INTO TABLE tbl_siswa (nama,kelas) FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'" db_teritorial


    tapi tetep muncul notif ini:

    ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'fields terminated by ','' at line 1

    ReplyDelete
  9. bisa mas contohnya seperti dibawah ini

    mysql -uroot -ppass -e "LOAD DATA LOCAL INFILE '/home/dendie/Desktop/data.csv' INTO TABLE pegawai FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (nama,tgl_lahir)" db_sisfo

    nb: spesfikasi kolomnya ditulis setelah TERMINATED BY '\n'

    ReplyDelete
  10. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Klo colom di csv nya ga urut ama yang di tabel sql gmn mas dendie?

      Contoh nya
      nama,
      kelas,
      umur
      id int(10) auto_increment,

      di table sql nya
      id int(10) auto_increment,
      nama,
      kelas,
      umur


      Makasih

      Delete
    2. Harus urut mas, ntar data yang di import ke tabelnya ngak akan urut.

      Delete
  11. halo mas Dendie , nanya donk kalo mau masukkin nya multiple file gimana ya , saya punya 100 file misalnya, pake script apa ya ? soalnya saya coba pake *.csv kok ga mau ya

    ReplyDelete