Tutorial SQL : Penggunaan Load Data Infile untuk Kebutuhan Import File Excel dalam Jumlah Data yang Besar

Tutorial SQL : Penggunaan Load Data Infile untuk Kebutuhan Import File Excel dalam Jumlah Data yang Besar

Load Data Infile digunakan untuk mengimport data dari file berjenis excel ke dalam suatu database dengan ukuran/kapasitas data yang cukup besar. Metode ini membaca baris dari file teks  ke dalam table yang di tunjuk pada database dengan kecepatan sangat tinggi. Nama file harus diberikan sebagai string.
Sebelumnya kita telah mempelajari mengenai pernyataan Select Into Outfile, dengan pernyataan ini File di keluarkan dari database dengan menghasilkan ekstensi tertentu dan menempatkannya pada folder yang aman.

Tiap baris data di database di tulis ke disk menggunakan pernyataan Select Into Outfile. Anda kemudian dapat membaca file kembali ke table menggunakan pernyataan Load Data Infile. Klausa Fields dan Lines sama di kedua pernyataan. Klausa ini bersifat optional, tetapi jika keduanya ditetapkan maka klausa Fields harus mendahului Lines.

Saat Anda menjalankan pernyataan LOAD DATA INFILE, Server MariaDB mencoba membaca file masukan dari sistem filenya sendiri. Sebaliknya, saat Anda menjalankan pernyataan LOAD DATA LOCAL INFILE, client mencoba membaca file input dari sistem filenya, dan mengirimkan konten file input ke Server MariaDB. Ini memungkinkan Anda untuk memuat file dari sistem file lokal client ke dalam database.

Jika Anda tidak ingin mengizinkan operasi ini (mungkin karena alasan keamanan), Anda dapat menonaktifkan pernyataan LOAD DATA LOCAL INFILE di server atau client.
1. Pernyataan LOAD DATA LOCAL INFILE dapat dinonaktifkan di server dengan menyetel variabel sistem local_infile ke 0.
2. Pernyataan LOAD DATA LOCAL INFILE dapat dinonaktifkan pada client. Jika Anda menggunakan MariaDB Connector/C, ini dapat dilakukan dengan menghapus flag kapabilitas CLIENT_LOCAL_FILES dengan fungsi mysql_real_connect atau dengan menghapus opsi MYSQL_OPT_LOCAL_INFILE dengan fungsi mysql_optionsv. 

Jika Anda menggunakan pustaka client atau client yang berbeda, lihat dokumentasi untuk pustaka client atau client khusus Anda untuk menentukan cara menangani pernyataan LOAD DATA LOCAL INFILE di https://mariadb.com/.

Jika pernyataan LOAD DATA LOCAL INFILE dinonaktifkan oleh server atau client dan jika pengguna mencoba menjalankannya, maka server akan menyebabkan pernyataan gagal dengan pesan kesalahan berikut:

The used command is not allowed with this MariaDB version
( Perintah yang digunakan tidak diperbolehkan dengan versi MariaDB ini )

Perhatikan bahwa tidak sepenuhnya akurat untuk mengatakan bahwa versi MariaDB tidak mendukung perintah tersebut. Akan lebih akurat untuk mengatakan bahwa konfigurasi MariaDB tidak mendukung perintah tersebut.

Dari MariaDB 10.5.2, pesan kesalahan lebih akurat:
The used command is not allowed because the MariaDB server or client has disabled the local infile capability
( Perintah yang digunakan tidak diperbolehkan karena server atau client MariaDB telah menonaktifkan kemampuan infile lokal ).

Sekarang saatnya kita coba bagaimana penggunaan pernyataan Load Data Local Infile. Untuk pernyataan lainnya yang telah dijelaskan di atas, Anda dapat mengunjungi situs dokumentasinya di https://dev.mysql.com/doc/refman/8.0/en/load-data.html atau https://mariadb.com/kb/en/load-data-infile/

Persiapan Database dan Tabel

Tabel dan Database masih kita manfaatkan dari tutorial sebelumnya yaitu di https://www.alvapixel.com/2023/05/penggunaan-select-into-outfile-untuk-kebutuhan-export-file-excel-dalam-jumlah-data-yang-besar.html

Menyiapkan file excel

File excel yang kita butuhkan berjenis .csv . Silahkan buat datanya dengan contoh isi data sebagai berikut

Untuk format tanggal/waktu di mysql tersusun menjadi yyyy-mm-dd hh:mm:ss
Simpan di dalam directory Local Disk C

Perintah Query

Kita aktifkan pernyataan Load Data Local dengan perintah :

SET GLOBAL local_infile=1;

Apabila telah sukses, maka dapat dilanjut dengan menuliskan perintah sebagai berikut :

LOAD DATA LOCAL INFILE 'C:\\pegawai.csv'
INTO TABLE pegawai
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\r\n';

Bagi Anda yang sering bekerja menggunakan Command Line, dapat ikuti langkah-langkanya seperti pada gambar di bawah ini

Pilih database bumn, lalu ketikkan perintah sql

Upps, ada yang error, mari kita coba hapus key "local" di query

Whaatt, error lagi, coba cek variabel globalnya

Coba lagi, tempatkan file yang telah di atur oleh MariaDB, kemudian rubah target lokasi filenya

Jalankan kembali perintahnya dengan merubah target lokasi/letak filenya

Ok, dan perintahnya berhasil di jalankan. mari kita cek datanya di dalam

Untuk keluar dari frame layout sql, jalankan perintah quit

Trik Tambahan

Bila Anda menjumpai error seperti : MySQL Server is running with –secure-file-priv . . . hal ini dikarenakan kita wajib menempatkan file di dalam folder yang aman yang telah di atur oleh MariaDB. Apabila Anda ingin merubahnya, anda dapat mengikuti langka-langkahnya sebagai berikut :
1. Di bagian config my.ini (di dalam folder C:\ProgramData\MySQL\MySQL Server 8.0) cari bagian # Secure File Priv. dan ganti secure-file-priv="path lama" menjadi secure-file-priv="path baru".

2. Usahakan nama pada path jangan ada spasi misal "D:\\basis data\\example.csv ,maka pengisiannya harus "BasisData" atau gunakan underscore "basis_data"
3. Pathnya harus dipisah dengan "\\"
4. Apabila masih belum bisa, silahkan kunjungi grup tanya jawab di http://stackoverflow.com/questions/32737478/how-should-i-tackle-secure-file-priv-in-mysql