Tutorial SQL : Penggunaan Select Into Outfile untuk Kebutuhan Export File Excel dalam Jumlah Data yang Besar
 |
| Tutorial SQL : Penggunaan Select Into Outfile untuk Kebutuhan Export File Excel dalam Jumlah Data yang Besar |
Penggunaan SELECT INTO OUTFILE dapat menulis baris yang menghasilkan ke sebuah file, dan memungkinkan penggunaan kolom dan baris untuk menentukan format output tertentu. Standarnya adalah untuk mengakhiri bidang dengan tab (\t) dan baris dengan baris baru (\n).
File yang akan di export tidak boleh kosong. Dan apabila sudah terdapat file yang sudah di export itu tidak dapat ditimpa. Seorang pengguna membutuhkan hak istimewa FILE untuk menjalankan pernyataan ini. Selain itu, MariaDB memerlukan izin untuk menulis file di lokasi yang ditentukan. Jika variabel sistem secure_file_priv disetel ke nama direktori yang tidak kosong, file hanya dapat ditulis ke direktori tersebut.
Pernyataan LOAD DATA INFILE melengkapi SELECT INTO OUTFILE.
Kumpulan Karakter
Klausa CHARACTER SET menentukan set karakter di mana hasilnya akan ditulis. Tanpa klausa, tidak ada konversi yang terjadi (rangkaian karakter biner). Dalam hal ini, jika ada beberapa set karakter, output akan berisi ini juga, dan mungkin tidak dapat dimuat ulang dengan mudah.
Dalam kasus di mana Anda memiliki dua server menggunakan kumpulan karakter yang berbeda, menggunakan SELECT INTO OUTFILE untuk mentransfer data dari satu ke yang lain dapat memberikan hasil yang tidak terduga. Untuk memastikan bahwa MariaDB menginterpretasikan escape sequence dengan benar, gunakan klausa CHARACTER SET pada pernyataan SELECT INTO OUTFILE dan pernyataan LOAD DATA INFILE berikutnya.
Contoh
Sebelum menggunakan perintah Select Into Outfile, kita membutuhkan database, tabel dan juga data dummy. Silahkan ikuti instruksi beriut di bawah.
Membuat database dengan perintah :
CREATE DATABASE bumn;
Membuat tabel dengan perintah :
create table pegawai (
id INT NOT NULL,
nik VARCHAR (20) NOT NULL,
nama VARCHAR (25) NOT NULL,
created_at DATETIME,
updated_at TIMESTAMP,
PRIMARY KEY (id)
);
Dan input data dummy dengan perintah :
INSERT INTO pegawai (id,nik,nama) VALUES
(1,90142518,'andreas'),
(2,90140803,'imam'),
(3,90139893,'windu'),
(4,90142525,'hary'),
(5,90142534,'anton'),
(6,90142518,'andreas'),
(7,90140803,'imam'),
(8,90139893,'windu'),
(9,90142525,'hary')
;
Dari tutorial lain yang saya baca, saya menemukan bahwa untuk men-export data dari database ke format .csv atau excel atau .txt atau lainnya. saya menemukan perintah berikut :
SELECT * from pegawai
INTO OUTFILE '/exportdata/pegawai.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Ketika dijalankan akan muncul pesan kesalahan seperti pada gambar berikut
Untuk mengatasi error 1290 seperti di atas, dapat dengan mudah kita atasi dengan mengecek variabel secure file. Sekarang, tuliskan perintah sql berikut :
SHOW VARIABLES LIKE "secure_file_priv";
Maka kita akan melihat lokasi dimana file akan tersimpan dengan aman.
Oke, rubah querynya menjadi seperti berikut :
SELECT * from pegawai
INTO OUTFILE 'C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\pegawai.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';
Lalu, apabila berhasil maka file excel akan tersimpan pada folder C:\ProgramData\MySQL\MySQL Server 8.0\Uploads\
Sintaks ANSI berikut juga didukung untuk SELECT sederhana tanpa UNION
SELECT customer_id, firstname, surname INTO OUTFILE ' C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\pegawai.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM customers;
Jika Anda ingin menggunakan sintaks ANSI dengan UNION atau konstruksi serupa, Anda harus menggunakan sintaks:
SELECT * INTO OUTFILE C:\\ProgramData\\MySQL\\MySQL Server 8.0\\Uploads\\pegawai.csv" FROM (SELECT * FROM t1 UNION SELECT * FROM t1);
Untuk lebih detail mengenai Select Into Outfile, anda dapat membaca melalui laman dokumentasinya di https://mariadb.com/kb/en/select-into-outfile/