Ringkasan. dalam tutorial ini, Anda akan mempelajari berbagai teknik cara mengekspor tabel MySQL ke file CSV
CSV adalah singkatan dari nilai yang dipisahkan koma. Anda sering menggunakan format file CSV untuk bertukar data antar aplikasi seperti Microsoft Excel, Open Office, Google Docs, dll
Akan sangat berguna untuk memiliki data dari database MySQL dalam format file CSV karena Anda dapat menganalisis dan memformat data sesuai keinginan Anda
MySQL menyediakan cara mudah untuk mengekspor hasil kueri ke file CSV yang berada di server basis data
Sebelum mengekspor data, Anda harus memastikannya
- Proses server MySQL memiliki akses tulis ke folder target yang berisi file CSV target
- File CSV target tidak boleh ada
Kueri berikut memilih pesanan yang dibatalkan dari tabel orders
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled';
Code language: SQL (Structured Query Language) (sql)Untuk mengekspor kumpulan hasil ini ke file CSV, Anda menambahkan beberapa klausa ke kueri di atas sebagai berikut
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)_Pernyataan tersebut membuat file CSV bernama cancelled_orders.csv di folder C:\tmp yang berisi kumpulan hasil
File CSV berisi baris baris di set hasil. Setiap baris diakhiri dengan urutan carriage return dan karakter umpan baris yang ditentukan oleh klausa LINES TERMINATED BY '\r\n'. Setiap baris berisi nilai dari setiap kolom baris di set hasil
Setiap nilai diapit oleh tanda kutip ganda yang ditunjukkan oleh klausa FIELDS ENCLOSED BY '”'. Ini mencegah nilai yang mungkin mengandung koma (,) akan ditafsirkan sebagai pemisah bidang. Saat melampirkan nilai dengan tanda kutip ganda, koma di dalam nilai tidak dikenali sebagai pemisah bidang
Mengekspor data ke file CSV yang nama filenya berisi stempel waktu
Anda sering perlu mengekspor data ke file CSV yang namanya berisi stempel waktu saat file dibuat. Untuk melakukannya, Anda perlu menggunakan pernyataan yang disiapkan MySQL
Perintah berikut mengekspor seluruh tabel pesanan menjadi file CSV dengan stempel waktu sebagai bagian dari nama file
SET @TS = DATE_FORMAT(NOW(),'_%Y_%m_%d_%H_%i_%s'); SET @FOLDER = 'c:/tmp/'; SET @PREFIX = 'orders'; SET @EXT = '.csv'; SET @CMD = CONCAT("SELECT * FROM orders INTO OUTFILE '",@FOLDER,@PREFIX,@TS,@EXT, "' FIELDS ENCLOSED BY '\"' TERMINATED BY ';' ESCAPED BY '\"'", " LINES TERMINATED BY '\r\n';"); PREPARE statement FROM @CMD; EXECUTE statement;
Code language: SQL (Structured Query Language) (sql)Mari kita periksa perintah di atas lebih terinci
- Pertama, kami membuat kueri dengan stempel waktu saat ini sebagai bagian dari nama file
- Kedua, kami menyiapkan pernyataan untuk dieksekusi dengan menggunakan perintah
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)0 pernyataanSELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)1 - Ketiga, kami mengeksekusi pernyataan tersebut dengan menggunakan perintah
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)2
Anda dapat menggabungkan perintah dengan acara dan menjadwalkan acara berjalan secara berkala jika diperlukan
Mengekspor data dengan judul kolom
Akan lebih mudah jika file CSV berisi baris pertama sebagai judul kolom sehingga file tersebut lebih mudah dipahami
Untuk menambahkan judul kolom, Anda perlu menggunakan pernyataan UNION sebagai berikut
(SELECT 'Order Number','Order Date','Status') UNION (SELECT orderNumber,orderDate, status FROM orders INTO OUTFILE 'C:/tmp/orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n');
Code language: SQL (Structured Query Language) (sql)_Seperti yang ditunjukkan kueri, Anda harus menyertakan tajuk kolom dari setiap kolom
Menangani nilai NULL
Jika nilai dalam kumpulan hasil berisi nilai NULL, file target akan berisi
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)3 bukanSELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)4. Untuk memperbaiki masalah ini, Anda perlu mengganti nilaiSELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)_4 dengan nilai lain e. g. , tidak berlaku (SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)_6 ) dengan menggunakan fungsi IFNULL sebagai kueri berikutSELECT orderNumber, orderDate, IFNULL(shippedDate, 'N/A') FROM orders INTO OUTFILE 'C:/tmp/orders2.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Kami mengganti nilai
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)_4 di kolomSELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)8 dengan stringSELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)6. File CSV menampilkanSELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)_6, bukanSELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)4 nilaiMengekspor data ke file CSV menggunakan MySQL Workbench
Jika Anda tidak memiliki akses ke server database untuk mendapatkan file CSV yang diekspor, Anda dapat menggunakan MySQL Workbench untuk mengekspor kumpulan hasil kueri ke file CSV di komputer lokal Anda sebagai berikut
- Pertama, jalankan kueri, dapatkan set hasilnya
- Kedua, dari panel hasil, klik "export recordset to an external file". Set hasil juga dikenal sebagai recordset
- Ketiga, dialog baru ditampilkan. Ini meminta Anda untuk nama file dan format file. Masukkan nama file, pilih CSV sebagai format file dan klik tombol Simpan
File CSV yang diekspor oleh MySQL Workbench mendukung judul kolom,
SELECT orderNumber, status, orderDate, requiredDate, comments FROM orders WHERE status = 'Cancelled' INTO OUTFILE 'C:/tmp/cancelled_orders.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n';
Code language: SQL (Structured Query Language) (sql)4 nilai, dan fitur hebat lainnya