Oke, situasinya adalah saya mencoba mengkonversikan database MS Access (mdb), ke MySQL/MariaDB. Tantangannya adalah tools yang tersedia (mdbtools) cuma menyediakan fasilitas ekspor skema dan data dalam format csv. Kalau tabel nya sedikit sih ga masalah, tapi kalau tabelnya banyak, meeeh, malesin kalau harus dilakukan pertable. Apa cara yang bisa dilakukan? Manfaatkan bash shell scripting dan kita bisa melakukannya langsung dari command line Linux. Oke begini caranya:
1. Install mdbtools. Di Ubuntu Linux, cukup dengan perintah: sudo apt-get install mdbtools.
2. Buat database dan user nya untuk menyimpan data hasil konversi:
————————————————
hendrowicaksono@hendrowicaksono:~$ mysql -u root -p Enter password: Welcome to the MariaDB monitor. Commands end with ; or \g. Your MariaDB connection id is 175 Server version: 5.5.44-MariaDB-1ubuntu0.14.04.1 (Ubuntu)
Copyright (c) 2000, 2015, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]> CREATE DATABASE konversi_mdb; Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> GRANT ALL PRIVILEGES ON konversi_mdb.* TO konversi@localhost IDENTIFIED BY 'rahasia'; Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> quit Bye hendrowicaksono@hendrowicaksono:~$
——————————————————-
2. Masuk ke folder dimana file mdb disimpan. Ekspor skema database, kemudian import ke MySQL/MariaDB.
——————————————————-
hendrowicaksono@hendrowicaksono:~$ cd Projekto/konversi-mdb/ hendrowicaksono@hendrowicaksono:~/Projekto/konversi-mdb$ mdb-schema Bm310.mdb mysql > konversi_mdb.sql hendrowicaksono@hendrowicaksono:~/Projekto/konversi-mdb$ mysql -u konversi -p konversi_mdb < konversi_mdb.sql Enter password: hendrowicaksono@hendrowicaksono:~/Projekto/konversi-mdb$
——————————————————-
3. Lakukan perintah looping di basj shell via command line:
——————————————————-
hendrowicaksono@hendrowicaksono:~/Projekto/konversi-mdb$ for TAB in `mdb-tables -1 Bm310.mdb` > do mdb-export Bm310.mdb $TAB > $TAB.csv; mysqlimport --ignore-lines=1 --fields-terminated-by=, --verbose --local -u konversi --password="rahasia" konversi_mdb $TAB.csv > done
——————————————————-
Nanti akan muncul tampilan proses import nya:
——————————————————-
Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Alamat.csv into Alamat konversi_mdb.Alamat: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Anggota.csv into Anggota konversi_mdb.Anggota: Records: 142 Deleted: 0 Skipped: 0 Warnings: 298 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Artikel_Majalah.csv into Artikel_Majalah konversi_mdb.Artikel_Majalah: Records: 11029 Deleted: 0 Skipped: 0 Warnings: 61600 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Asal.csv into Asal konversi_mdb.Asal: Records: 42 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Bahasa.csv into Bahasa konversi_mdb.Bahasa: Records: 10 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Denda_Buku.csv into Denda_Buku konversi_mdb.Denda_Buku: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Denda_Maj.csv into Denda_Maj konversi_mdb.Denda_Maj: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Denda_NBM.csv into Denda_NBM konversi_mdb.Denda_NBM: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Detail_Pengadaan.csv into Detail_Pengadaan konversi_mdb.Detail_Pengadaan: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Edisi_Majalah.csv into Edisi_Majalah konversi_mdb.Edisi_Majalah: Records: 789 Deleted: 0 Skipped: 0 Warnings: 792 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Eksemplar_Majalah.csv into Eksemplar_Majalah konversi_mdb.Eksemplar_Majalah: Records: 779 Deleted: 0 Skipped: 0 Warnings: 3116 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Eksemplar_NBM.csv into Eksemplar_NBM konversi_mdb.Eksemplar_NBM: Records: 17 Deleted: 0 Skipped: 0 Warnings: 51 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Groups.csv into Groups konversi_mdb.Groups: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Jabatan.csv into Jabatan konversi_mdb.Jabatan: Records: 8 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Jenis_Buku.csv into Jenis_Buku konversi_mdb.Jenis_Buku: Records: 17 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Jenis_Majalah.csv into Jenis_Majalah konversi_mdb.Jenis_Majalah: Records: 34 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Kala_Terbit.csv into Kala_Terbit konversi_mdb.Kala_Terbit: Records: 9 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Klasifikasi.csv into Klasifikasi konversi_mdb.Klasifikasi: Records: 1774 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Koleksi.csv into Koleksi konversi_mdb.Koleksi: Records: 3 Deleted: 0 Skipped: 0 Warnings: 3 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Kota.csv into Kota konversi_mdb.Kota: Records: 318 Deleted: 0 Skipped: 0 Warnings: 14 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Majalah.csv into Majalah konversi_mdb.Majalah: Records: 79 Deleted: 0 Skipped: 0 Warnings: 2 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/NBM.csv into NBM konversi_mdb.NBM: Records: 16 Deleted: 0 Skipped: 0 Warnings: 43 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Peminjam.csv into Peminjam konversi_mdb.Peminjam: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Penerbit.csv into Penerbit konversi_mdb.Penerbit: Records: 1364 Deleted: 0 Skipped: 0 Warnings: 35 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Pengadaan.csv into Pengadaan konversi_mdb.Pengadaan: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Pengarang.csv into Pengarang konversi_mdb.Pengarang: Records: 15583 Deleted: 0 Skipped: 0 Warnings: 14930 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Pengarang_Artikel.csv into Pengarang_Artikel konversi_mdb.Pengarang_Artikel: Records: 6978 Deleted: 0 Skipped: 0 Warnings: 13208 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Pengarang_Buku.csv into Pengarang_Buku konversi_mdb.Pengarang_Buku: Records: 19750 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Pengarang_NBM.csv into Pengarang_NBM konversi_mdb.Pengarang_NBM: Records: 12 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Perolehan.csv into Perolehan konversi_mdb.Perolehan: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Sirk_Buku.csv into Sirk_Buku konversi_mdb.Sirk_Buku: Records: 3 Deleted: 0 Skipped: 0 Warnings: 12 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Sirk_Majalah.csv into Sirk_Majalah konversi_mdb.Sirk_Majalah: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Sirk_NBM.csv into Sirk_NBM konversi_mdb.Sirk_NBM: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Status.csv into Status konversi_mdb.Status: Records: 4 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Subjek.csv into Subjek konversi_mdb.Subjek: Records: 4349 Deleted: 0 Skipped: 0 Warnings: 9 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Subjek_Artikel.csv into Subjek_Artikel konversi_mdb.Subjek_Artikel: Records: 6498 Deleted: 0 Skipped: 0 Warnings: 9642 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Subjek_NBM.csv into Subjek_NBM konversi_mdb.Subjek_NBM: Records: 16 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Supplier.csv into Supplier konversi_mdb.Supplier: Records: 0 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Users.csv into Users konversi_mdb.Users: Records: 6 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Buku.csv into Buku konversi_mdb.Buku: Records: 22172 Deleted: 0 Skipped: 0 Warnings: 106077 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Eksemplar_Buku.csv into Eksemplar_Buku konversi_mdb.Eksemplar_Buku: Records: 20994 Deleted: 0 Skipped: 0 Warnings: 62984 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Jenis_NBM.csv into Jenis_NBM konversi_mdb.Jenis_NBM: Records: 1 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Mata_Uang.csv into Mata_Uang konversi_mdb.Mata_Uang: Records: 3 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/Subjek_Buku.csv into Subjek_Buku konversi_mdb.Subjek_Buku: Records: 19232 Deleted: 0 Skipped: 0 Warnings: 0 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/OPAC_bbaru.csv into OPAC_bbaru konversi_mdb.OPAC_bbaru: Records: 4 Deleted: 0 Skipped: 0 Warnings: 10 Disconnecting from localhost Connecting to localhost Selecting database konversi_mdb Loading data from LOCAL file: /home/hendrowicaksono/Projekto/konversi-mdb/OPAC_Kabar.csv into OPAC_Kabar konversi_mdb.OPAC_Kabar: Records: 2 Deleted: 0 Skipped: 0 Warnings: 2 Disconnecting from localhost
——————————————————-
Jika tidak ingin ada tampilan progres import seperti diatas, cukup buang output bukan ke standard output tapi ke /dev/null pada sintaks mysqlimport:
——————————————————-
hendrowicaksono@hendrowicaksono:~/Projekto/konversi-mdb$ for TAB in `mdb-tables -1 Bm310.mdb` > do mdb-export Bm310.mdb $TAB > $TAB.csv; mysqlimport --ignore-lines=1 --fields-terminated-by=, --verbose --local -u konversi --password="rahasia"konversi_mdb $TAB.csv > /dev/null > done
——————————————————-
Selamat mencoba!
Hendro Wicaksono's Blog 