Import excel ke mysql menggunakan PHP

Import excel ke mysql menggunakan PHP

5643
120
SHARE

Halo kawan jumpa lagi…

Kemarin banyak request dari pengunjung harviacode.com tentang bagaimana cara import data dari file excel ke dalam tabel mysql menggunakan PHP. Oleh karena itu pada kesempatan ini saya akan coba menulis tentang bagaimana cara import file excel ke mysql menggunakan PHP.

Import file excel ke mysql menggunakan PHP

Untuk membuat import excel ke mysql menggunakan PHP, kita memerlukan excel reader.php (@author Vadim Tkachenko) guna membaca file xls yang akan kita import. Anda dapat mendownloadnya excel_reader.php di sini.

Pada contoh berikut ini kita akan membuat import data pegawai yang terdiri dari nama, tempat lahir dan tanggal lahir. Berikut ini adalah langkah-langkahnya.

Step 1 : Download excel_reader.php di sini dan masukkan ke dalam folder project anda.

Step 2 : Buatlah tabel pegawai pada mysql menggunakan sintaks

Step 3 : Buatlah file pegawai.xls (Harus dalam format Ms Excel 2003) dengan data seperti gambar berikut

pegawai.xls

Step 4
: Buatlah file import.php dalam project anda dengan sintaks sebagai berikut.

Step 5 : Jalankan import.php melalui browser, maka akan tampil halaman sebagai berikut.

import

Step 6 : Cobalah untuk mengimport pegawai.xls yang telah kita buat tadi. Bila tidak ada masalah, akan muncul pesan data berhasil diimpor. Bukalah tabel pegawai pada mysql, maka data akan masuk sebagaimana gambar berikut ini.
impor excel ke sql
Catatan : Jika anda mencentang checkbox ‘Kosongkan tabel sql terlebih dahulu.’ saat import, maka data yang ada dalam tabel pegawai mysql akan dihapus terlebih dulu, baru kemudian data dimasukkan.

Pada artikel berikutnya (di sini), kita akan menambahkan progress bar, sehingga pengguna bisa mengetahui berapa jumlah data yang telah berhasil diimport.

progress import

Baca juga : Cara export data dari mysql ke excel menggunakan PHP

Cukup mudah bukan.. Semoga bermanfaat dan terima kasih atas kunjungan Anda.

Anda dapat mendownload contoh lengkap import excel menggunakan PHP pada link di bawah ini

DOWNLOAD

Bagikan artikel ini ya.. terima kasih

120 COMMENTS

  1. permisi gan, itu import file excel yang berjenis date kenapa ketika di import ke database tidak kebaca?
    jadinya terisi 0000-00-00. kolom tabel di excel saya ganti menjadi yyyy-mm-dd

    terima kasih

  2. Gan..
    Kalau worksheetnya banyak..dan yg di import worksheet ke 12 (misalnya). Bagaimana menentukan bahwa worksheet tsb yg diimport ?
    Hatur nuhun sy tunggu kabar baiknya…

    Salam

  3. Terdapat eror nih gan pas coba klik import baik ceklist Kosongkan tabel sql terlebih dahulu maupun nggak diisi dan kemudian file excel menghilang, erornya sbb :

    Warning: move_uploaded_file(pegawai.xls) [function.move-uploaded-file]: failed to open stream: Permission denied in C:\xampp\htdocs\latihan\import\import.php on line 13

    Warning: move_uploaded_file() [function.move-uploaded-file]: Unable to move ‘C:\xampp\tmp\phpEBE9.tmp’ to ‘pegawai.xls’ in C:\xampp\htdocs\latihan\import\import.php on line 13
    The filename pegawai.xls is not readable

  4. Thanks for sharing .. gue mau nanya .. kalo gue pengen import data banyak terus keluar notif eror ini gimana ya?

    Fatal error: Allowed memory size of 134217728 bytes exhausted (tried to allocate 24 bytes) in C:\xampp\htdocs\a\s\excel_reader.php on line 1367

  5. Udah saya coba gan.. Tapi kox muncul pesan eror seperti ini y.. Mohon bantuannya..

    ( ! ) SCREAM: Error suppression ignored for
    ( ! ) Deprecated: Assigning the return value of new by reference is deprecated in C:\wamp\www\latihan\excel_reader.php on line 916

    • ehmm. dari pesan errornya nampaknya ada perbedaan versi PHP. Kemungkinan ada fungsi yang deprecated pada excel readernya untuk PHP versi terbaru. Solusinya anda bisa mengganti excel reader dengan PHPExcel. Kalo ada waktu saya akan tulis juga untuk import dengan PHPExcel. Terima kasih..

  6. Hallo
    Saya coba implementasikan, tapi kok gak berhasil yah. Tidak ada pesan error, tapi data excel yang diimport tidak masuk database. Mohon kiranya bisa membantu. Terimakasih

    • pastikan agan tidak mematikan fitur notifikasi error php, kemudian pastikan kode yang agan tulis benar benar sama dengan contoh yang saya berikan. bila memang masih tidak bisa, kemungkinan karena perbedaan versi PHP.

  7. Saya sudah ikut semua step2 dia atas..tetapi selepas sudah import excel ia keluar “No database selected” atau Notice: Undefined index: drop in c:\program files (x86)\easyphp1-8\www\import.php on line 22
    No database selected…harap dapat bantu saya

  8. gan, mau tanya saya udah copas sourch code dari agan yg import.php tapi kok saya ga bisa import file excell nya yah gan ? ga ada error(notifikasi kesalahan) nya juga gan tolong bantu yah gan

  9. gan, kalo file .xls nya di edit di open office pas di importnya formatnya jadi berubah, aneh. di open office formatnya dd/mm/yy dan kebaca di phpnya jadi Contoh: 02/07/2015 -> thuthu/juljul/20152015. mohon pencerahannya.

    • mungkin dijadikan string aja gan atau sebelum dimasukkan ke database, di explode dulu dan diimplode lg sesuai format tanggal SQL baru diinsert ke database.

  10. gan, mau nanya..
    database saya kan tabelnya banyak ada sekitar 50 an table di mysqlnya.
    kalo kita mau import data excel ke beda tabel dari bnyaknya tabel itu,gimana gan. tolong bantuannya ???

    • perintah INSERT nya di ulangi aja untuk masing2 tabel. tapi masa import 1 tabel excel ke banyak tabel buat apa. kan datanya sama.

  11. gan, itu kan data excelnya dibuat dengan nama file pegawai.xls…… nah kalo pengen buat dengan berbeda nama file, misal tadi ada pegawai.xls, ada lagi honorer.xls … bisa gak gan di proses/berjalan gak kalo kita import ke sql dengan nama satu nama database di sql….

    terima kasih gan…

    • bisa aja gan, tapi menurut saya tidak ideal, karena akan membingungkan. sebaiknya tetap dipisah, misalnya untuk tabel pegawai sendiri dan tabel honorer sendiri. Kalo mw dijadikan satu tabel bisa saja, dengan penambahan satu field lagi yang menjelasakan dia pegawai atau honorer.

    • tinggal tambahin mysql_real_escape_string() saat membaca datanya mas. misalnya $nama = trim(mysql_real_escape_string($data->val($i, 1)));

    • bukan gak kehapus, tp auto increment mmg akan terus lanjut nomornya meskipun datanya dihapus. kecuali auto incrementnya di reset gan..

  12. ketika : if($_POST[‘drop’]==1){ diganti dengan :
    $drop = isset($_POST[‘drop’])?$_POST[‘drop’]:”;
    if($drop==1){

    maka muncul Notice: Use of undefined constant ‘drop’ – assumed ‘‘drop’’

  13. siang mas,
    mas mau tanya, pas saya coba dan jalanin script import.php muncul error gini ..

    Warning: mysql_connect(): Access denied for user ‘username’@’localhost’ (using password: YES) in C:\xampp\htdocs\import.php on line 3

    Notice: Undefined index: drop in C:\xampp\htdocs\import.php on line 21
    No database selected

    Perlu settingan tambahankah?
    tks

  14. mas, saya run scriptnya ada error :
    Warning: mysql_connect(): Access denied for user ‘username’@’localhost’ (using password: YES) in C:\xampp\htdocs\import.php on line 3

    Notice: Undefined index: drop in C:\xampp\htdocs\import.php on line 21
    No database selected

    saya udah coba ganti :
    if($_POST[‘drop’]==1){ diganti dengan :
    $drop = isset($_POST[‘drop’])?$_POST[‘drop’]:”;
    if($drop==1){

    tapi tetap muncul >> Notice: Undefined index: drop in C:\xampp\htdocs\import.php on line 21
    No database selected

    kurang atau ada yg salah ya?

    • O..mungkin masalah hak aksesnya mas. coba unlinknya di hapus dulu. nanti dilihat file xls yg terupload chmodnya seperti apa. mungkin harus ubah chmod dengan php. btw…nama saya Hari, mas, bukan harvian. hehe

    • Sama mas sukpri

      saya berhasl menjalankan di local, tpi ketika di upload ke online tidak berjalan norma, muncul “The filename xxxxx.xls is not readable” mas hari bisa di bantu?

      • hmmm.. saya curiga ini disebabkan chmodnya. coba tambahkan chmod 777 mas.. kodenya sudah saya update. silahkan lihat baris 15 dan 16. untested ya.. mudah2an berhasil.

  15. artikel yang sangat membantu pemula seperti saya mas, work tapi ada pesan error ketika saya import tanpa mengosongkan table terlrbih dahulu,
    Notice: Undefined index: drop in C:\xampp\htdocs\app_lap\admin\import.php on line 18
    mohon pencerahan gan 🙂

  16. mas kenapa muncul pesan seperti ini ya..??
    Deprecated: Assigning the return value of new by reference is deprecated in C:\xampp\htdocs\latihan\excel_reader.php on line 916

    tapi datanya berhasil disimpan

  17. Fatal error: Call to undefined method Spreadsheet_Excel_Reader::rowcount() in C:\xampp\htdocs\belajar\excel\import.php on line 18
    mau tanya itu yang salah apanya ya gan, berikut penulisannya

    $target = basename($_FILES[‘filepegawaiall’][‘name’]) ;
    move_uploaded_file($_FILES[‘filepegawaiall’][‘tmp_name’], $target);

    $data = new Spreadsheet_Excel_Reader($_FILES[‘filepegawaiall’][‘name’],false);

    // menghitung jumlah baris file xls
    $baris = $data->rowcount($sheet_index=0);

    • iya mas excel readernya memang deprecated. kapan2 coba saya update. untuk sementara mungkin bisa dimatikan saya error reportingnya menggunakan error_reporting(0) khusus halaman tersebut.

  18. thanks gan tutorialnya sdh bisa kucoba.
    Tapi bagaimana caranya agar data yang sama yang import bisa ditolak gan. Tahanks.

  19. tutorial nya sangat membantu gan tapi ane ada masalah nih :
    Notice: Undefined index: drop in C:\xampp\htdocs\import\import.php on line 52

    kenapa ga kebaca yah gan drop nya ? makasih

    • sudah ditambahkan ini $drop = isset( $_POST[“drop”] ) ? $_POST[“drop”] : 0 ; mas? itu notice yang muncul karena variable drop belum didefinisikan. jadi intinya harus ada $drop meskipun nilainya kosong.

    • itu PRIMARY nya di buat AUTO INCREMENT aja mas. Jadi meskipun tidak ada di tabel excel nya tidak menjadi masalah. Semoga membantu mas Alfian

  20. gan ane coba tapi ketika dijalanin baris yang kosong nya ke jumlah (baris kosong di excel)
    misal data hanya terisi 4 baris
    insert ke database banyak.
    kenepa?gimana ya cara mengatasinya?
    thanks

  21. gan tutorialnya ane coba, tapi ada masalah .
    jumlah baris yang kosong ke baca (baris di excel)
    misal 4 baris data terisi tapi input ke database jadi banyak.
    Kenapaya ? dan gimana cara mengatasinya?
    thanks

    • 1. itu mungkin karena sebelumnya ada datanya terus dihapus ya? kalo memang seperti itu, saat memang seperti itu, untuk menghapus data jangan langsung menggunakan delete, tapi hapus barisnya (delete row).
      2. cara kedua, coba copy baris data yang ada ke file excel baru dan simpan dengan file baru. coba import file baru tersebut.
      3. cara ketiga, mengubah kode dengan menambahkan if. logikanya jika data tidak sama dengan kosong, lakukan perintah insert ke database.

      demikian mas Hafizh, semoga membantu

  22. saya sudah coba script di atas namun error pada line 916
    ini isi line 916
    function Spreadsheet_Excel_Reader($file=”,$store_extended_info=true,$outputEncoding=”) {
    $this->_ole =& new OLERead();
    $this->setUTFEncoder(‘iconv’);
    if ($outputEncoding != ”) {
    $this->setOutputEncoding($outputEncoding);
    }
    for ($i=1; $i=1)?chr(($i-1)/26+64):”) . chr(($i-1)%26+65));
    $this->colnames[$name] = $i;
    $this->colindexes[$i] = $name;
    }

    mohon solusinya

  23. ok min saya sudah sukses importnya setelah sekian lama ngotak ngatik scriptnya tapi salut buat admin yang buat scriptnya mantap semoga bermanfaat buat para generasi selanjutnya

  24. pagi min, mau nanya, saya udah nyoba kodingannya, nyoba import data 21MB ga bisa, ada pengaturan buat size nya lebih dari itu ga min?thx 🙂

    • kalo itu tinggal tambahin max_upload, max_post sama max_execution time di php.ini nya mas.. saya coba smp 65ribu baris lancar

  25. Mas hari. Kalo data di excel nya ada kolom yg di marger. Misal kolom A trus kolom bawahnya B dan C itu cara buat tabel di sql nya gmana tu mas..? Mohon di bantu mas

  26. Mas hari, kalo data di excel nya ada kolom yg marger, misal nya ada kolom A trus di bawah nya kolom B dan C itu cara bikin tabel di sql nya gimana tu ? Agar biar bisa di import ke database? Mohon di bantu mas

    • waduh.. kalo ada merger cell tidak bisa mas. karena excel readernya membaca masing2 cell. solusinya untuk impor excelnya harus dicleansing secara manual dulu.

  27. gimana kalo memvalidasi header exelnya sesuai dengan keinginan misalnya
    ID NAMA ALAMAT dan kalo header exelnya tidak sama maka muncul pesan erorr

    • tinggal baca satu2 kolomnya lalu dilakukan pengecekan. contoh untuk baca cell A1 pakai $a1 = $data->val(1, 1); if($a1 <> ‘ID’) { // error and do something- maybe exit or die}
      Tapi tidak akan efektif juga menurut saya kalo yang divalidasi cm headernya. Justru yang lebih penting adalah validasi datanya. Caranya mirip tadi, cm dilakukannya didalam looping. Lebih bagus lagi yang berwenang melakukan impor hanya admin. Tentu admin tidak ingin merusak databasenya sendiri kan hehe..

  28. Trimaksih buat tutornya, sangat membantu, tapi saya ada kendala nh,.,
    kan saya sudah coba step diatas dan sudah bisa di run, data juga sudah masuk, yang saya mau tanyakan: kenapa file (excel) yang saya import hilang, apakah memang seperti itu?
    dan jika memang hilang file setelah di import benar2 hilang gitu, bagaimana cara upate/atau tambah data lg ya mas?? mohon penjelasnnya

    • tinggal di komen atau dihapus aja mas baris 50 (unlink($_FILES[‘filepegawaiall’][‘name’]);) cz pada baris itu sengaja kita hapus filenya biar tidak memenuhi memory server. hehe

  29. mkasih banget tutornya sangat membantu, tapi saya ada kendala dan pertanyaa mas,.,
    kalo file excel setelah diimport memang langsung gitu yaa,., tau bagaimana sehatusanya?
    soallnya kalau tiap diimpor hilang susah juga untu nambah data dan edit edit lagi mas.,.
    mohon bantuannya

    • tinggal di komen atau dihapus aja mas baris 50 (unlink($_FILES[‘filepegawaiall’][‘name’]);) cz pada baris itu sengaja kita hapus filenya biar tidak memenuhi memory server. hehe

  30. gan gimana jika kita mempunyai beberapa sheet misalkan sheet1, sheet2, sheet3 dan kita mempunyai database sheet1, sheet2, sheet3 nah caranya gimana supaya semua sheet itu masuk dengan table yang kita inginkan???

  31. gan mohon bantuannya gan, ini kenapa pas diimport ada row kosong yang masuk ya? padahal di excelnya ngga ada row kosong. ada keterangan duplicat entry primary key juga, padahal ngga ada yang sama datanya gan. bagaimana?

    • itu artinya ada kesalahan query dimana jumlah kolom yang diinsert tidak sama dengan jumlah data yang dimasukkan. contoh : INSERT into pegawai (nama,tempat_lahir,tanggal_lahir)values(‘$nama’,’$tempat_lahir’,’$tanggal_lahir’) memiliki 3 kolom yang akan diinsert yaitu nama,tempat_lahir,tanggal_lahir, maka harus dikirimkan data yang akan diinsert sejumlah 3 juga yaitu ‘$nama’,’$tempat_lahir’,’$tanggal_lahir’. Pastikan query mas Sofyan memiliki jumlah yang sama juga.

  32. min, makasih tutorialnya. tapia data saya ada banyak, jadi gak bisa dengan load time limite.
    bisakah jika hasil reader dijadikan array dulu, trus baru hasil array yang di insert? gimana caranya?

LEAVE A REPLY