File CSV (comma-separated values) adalah file teks dengan informasi pemisah koma. Mereka paling sering ditemukan di spreadsheet dan database dan sekarang terutama digunakan untuk kumpulan data dalam ilmu data dan pembelajaran mesin. Mereka membantu bisnis dalam mengekspor data dalam jumlah besar. Namun, kami tidak dapat menjalankan kueri SQL pada data CSV tersebut, jadi kami harus mengonversinya menjadi tabel terstruktur
Ada banyak metode untuk mengonversi data CSV menjadi tabel terstruktur. Kami akan membahas tiga metode paling populer untuk mengonversi. file csv ke format SQL. Tugas ini menjadi lebih kompleks ketika data yang dipisahkan koma yang kita gunakan untuk ini berisi stempel waktu
Pada artikel ini, kita akan melihat tiga metode berbeda untuk mengimpor. csv ke dalam tabel MySQL bersama dengan atribut timestamp.
Impor. File CSV Ke Tabel MYSQL
Ini dapat dicapai dengan berbagai cara. Ketiga metode yang akan kita bahas ini menggunakan metode import yang berbeda. csv ke dalam tabel MySQL. Kami akan melihat metode berikut
- Menggunakan Baris Perintah
- Menggunakan PhpMyAdmin
- Menggunakan Meja Kerja MySQL
Menggunakan Baris Perintah
Langkah 1. Buka jendela terminal dan masuk ke Klien MySQL menggunakan kata sandi. Lihat perintah berikut
mysql -u root -pLangkah 2. Buat database lalu buat tabel di dalam database itu. Itu. Data file CSV akan menjadi data input untuk tabel ini.
#To create a database CREATE DATABASE database_name; #To use that database use database_name; #To create a table CREATE TABLE table_name( id INTEGER, col_1 VARCHAR(100), col_2 INTEGER, col_3 DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );_Kami membuat database dan tabel bernama table_name di dalamnya. Tabel berisi berbagai atribut seperti id, col_1, col_2, dan col_3. Atribut col_3 bertipe DATETIME, dan berisi data dan waktu dalam format YYYY-MM-DD hh. mm. ss. Selain atribut integer dan varchar, kita akan melihat cara mengimpor atribut tipe DateTime dari file CSV ke tabel MySQL
Langkah 3. Sekarang kita harus memeriksa variabel MySQL "secure_file_priv". Secara default, server MySQL dimulai dengan opsi -secure-file-priv. Saat menggunakan LOAD DATA INFILE, ini menentukan direktori dari mana file data dapat dimuat ke dalam instance database tertentu. Untuk melihat direktori yang dikonfigurasi, gunakan perintah berikut
SHOW VARIABLES LIKE 'secure_file_priv';Keluaran
Output Untuk Langkah 1, 2 dan 3
Variabel "secure_file_priv" akan diatur ke direktori file yang dikonfigurasi oleh server MySQL. Kami sekarang memiliki dua opsi untuk membuat perintah LOAD DATA INFILE berfungsi dengan baik
- Pindahkan masukan. csv ke dalam struktur folder yang ditentukan
- Untuk mengubah struktur folder yang dikonfigurasi sesuai kebutuhan kita
Kami akan membahas kedua pendekatan ini
Pendekatan 1.
Dalam pendekatan ini, kita perlu memasukkan file CSV input ke dalam struktur folder yang ditentukan, dan hanya kita yang dapat mengakses file tersebut untuk dimuat ke dalam database. Setelah kami melakukan ini, sekarang kami siap menjalankan perintah untuk mengimpor file CSV ke database. Lihat perintah berikut
LOAD DATA INFILE '{folder_structure}/{csv_file_name}' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;- LOAD DATA INFILE – Ini menentukan lokasi file CSV input
- INTO TABLE – Ini menentukan tabel tempat data akan diisi
- FIELDS DIHENTIKAN OLEH – Ini menentukan pembatas di mana nilai-nilai individual dalam file dipisahkan
- TERLAMPIR OLEH – Ini menentukan simbol yang menentukan nilai dalam file CSV
- GARIS DIHENTIKAN OLEH – Ini menentukan kode untuk jeda baris
- IGNORE 1 ROWS – Ini menentukan jumlah baris yang akan diabaikan dari file CSV input yang mungkin berisi label kolom, dll.
Keluaran
Keluaran Untuk Perintah LOAD DATA INFILE
Pendekatan 2.
Untuk Windows
Langkah 1. Struktur folder yang ada di my. ini harus diubah. Buka PC Ini -> Drive C -> ProgramData -> MySQL -> Server MySQL * -> my. ini [Folder ProgramData disembunyikan. Untuk melihat folder, buka Lihat di bilah menu atas dan aktifkan item Tersembunyi. ]
Langkah 2. Buka file dan cari "secure-file-priv". Ini akan terlihat seperti ini
Langkah 3. Ubah struktur folder sesuai pilihan Anda dan simpan file di lokasi yang berbeda, karena file tidak dapat disimpan di lokasi yang sama. Sekarang, salin dan tempel file ke lokasi sebenarnya. Untuk melakukannya, kita memerlukan akses administrator
Langkah 4. Sekarang, tekan "Ctrl + R" -> Ketik "layanan. msc” -> Tekan “Enter”. Ini akan membuka tab Layanan
Langkah 5. Cari MySQL57 [Angka 57 mungkin berbeda di PC Anda]. Klik kanan padanya dan kemudian klik tombol restart
Langkah 6. Masuk ke klien Baris Perintah MySQL dan jalankan perintah berikut
LOAD DATA LOCAL INFILE '{file_location}' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;Mengikuti langkah-langkah yang diuraikan di atas, kita dapat mengimpor file CSV ke tabel MySQL di OS Windows tanpa menemui kesalahan
Untuk Linux
Langkah 1. Kita perlu menambahkan struktur folder baru pilihan kita di my. file cnf. File tersebut terletak di folder /etc/mysql/. Buka saya. cnf menggunakan editor pilihan Anda dan tambahkan baris berikut ke file
[mysqld] secure-file-priv={new_folder_structure}_Langkah 2. Simpan file setelah melakukan perubahan dan restart layanan MySQL. Lihat perintah berikut untuk itu
sudo systemctl restart mysqlLangkah 3. Masuk ke klien MySQL lagi menggunakan perintah berikut
mysql -u root -p --local-infile_Langkah 4. Setelah Anda masuk, jalankan perintah berikut
set global local_infile = 1; SHOW VARIABLES LIKE 'local_infile';_Keluaran
Langkah 5. Sekarang, kita akan mengimpor data file CSV ke tabel MySQL. Lihat perintah berikut
LOAD DATA LOCAL INFILE '{file_location}' INTO TABLE table_name FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;Keluaran
Mengikuti langkah-langkah yang diuraikan di atas, kita dapat mengimpor file CSV ke tabel MySQL di OS Linux tanpa mengalami error
Perintah berikut dapat digunakan untuk menentukan apakah atribut dob dengan tipe timestamp telah diimpor dengan benar atau tidak. Dengan cara ini, kita dapat memastikan bahwa atribut dalam tabel sesuai dengan tipe data DateTime sebenarnya dari MySQL
#To create a database CREATE DATABASE database_name; #To use that database use database_name; #To create a table CREATE TABLE table_name( id INTEGER, col_1 VARCHAR(100), col_2 INTEGER, col_3 DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );_1Validasi Atribut DATETIME
Langkah Opsional. Mengubah Data saat Mengimpor
Saat Anda ingin memuat data dari file ke tabel MySQL, Anda mungkin memperhatikan bahwa format waktu data yang masuk tidak sama persis dengan tabel target. Sebagai contoh, perhatikan format cap waktu col_3 dalam item. Jika format tabel target adalah “yyyy/mm/dd %H. %saya. %s. %f” dan format file csv adalah “dd/mm/yyyy %H. %saya. %s. %f". Klausa SET dalam pernyataan LOAD DATA INFILE dapat digunakan untuk mengonversi format datetime file CSV ke format yang digunakan oleh tabel tujuan di MySQL. Saat melakukan operasi untuk memuat data dari file ke tabel di MySQL, Anda dapat menggunakan klausa SET di bagian akhir bersama dengan fungsi str to date(). Lihat perintah berikut
#To create a database CREATE DATABASE database_name; #To use that database use database_name; #To create a table CREATE TABLE table_name( id INTEGER, col_1 VARCHAR(100), col_2 INTEGER, col_3 DATETIME DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (id) );_2Mengubah Data saat Mengimpor
Menggunakan PhpMyAdmin
phpMyAdmin adalah alat perangkat lunak berbasis PHP gratis yang dirancang untuk menangani administrasi MySQL melalui Internet. phpMyAdmin dapat melakukan berbagai macam operasi MySQL. Antarmuka pengguna dapat digunakan untuk melakukan operasi yang sering digunakan (seperti mengelola basis data, tabel, kolom, relasi, indeks, pengguna, dan izin), sementara Anda masih dapat menjalankan pernyataan SQL apa pun secara langsung
Mari kita lihat bagaimana kita dapat menggunakan PhpMyAdmin untuk mengimpor file CSV ke database MySQL
Langkah 1. Saat Anda meluncurkan PhpMyAdmin, Anda akan melihat berbagai database dan tabel di sisi kiri antarmuka. Buat tabel tempat Anda ingin mengimpor data file CSV. Kolom dan tipe data harus cocok dengan data terkait dalam file CSV
Langkah 2. Pilih tabel dan kemudian klik tombol impor dari bilah menu sisi atas
Tombol Impor Untuk Memasukkan File CSV
Langkah 3. Klik tombol "pilih file" dan arahkan ke penyimpanan lokal Anda untuk menemukan file input CSV. Di halaman yang sama, Anda juga dapat menentukan jumlah baris yang akan dilewati dalam file
Pilih Masukkan File CSV
Langkah 4. Gulir ke bawah halaman untuk memilih format file, yaitu csv dalam kasus kami. Mirip dengan pendekatan baris perintah, kami memiliki beberapa opsi khusus Format di sini. Setelah selesai, klik tombol "Go".
Format Dan Pilihan Format Khusus Untuk Memasukkan File CSV
Langkah 5. File CSV diimpor ke tabel MySQL. Saat impor berhasil, kami melihat pesan berikut
Berhasil Mengimpor File CSV Ke Tabel MySQL
Langkah 6. Kita sekarang dapat kembali ke tabel dan memeriksa data yang diimpor dari file CSV.
Tabel MySQL Diimpor Dengan Input Data CSV
Menggunakan Meja Kerja MySQL
MySQL Workbench adalah alat desain basis data visual yang menggabungkan pengembangan SQL, administrasi, desain basis data, pembuatan, dan pemeliharaan untuk sistem basis data MySQL ke dalam satu lingkungan pengembangan terintegrasi.
Mari kita lihat bagaimana kita bisa menggunakan MySQL Workbench untuk mengimpor file CSV ke database MySQL
Langkah 1. Saat Anda meluncurkan MySQL Workbench, Anda akan melihat berbagai database dan tabel di sisi kiri antarmuka. Buat tabel tempat Anda ingin mengimpor data file CSV. Kolom dan tipe data harus cocok dengan data terkait dalam file CSV
Langkah 2. Klik kanan pada tabel dan pilih opsi "Table Data Import Wizard". Jelajahi penyimpanan lokal Anda untuk memilih file CSV masukan
Langkah 3. Pilih penyandian file dan cocokkan kolom sumber dan tujuan untuk mengonfigurasi pengaturan impor. Setelah selesai, klik tombol berikutnya
Langkah 4. Data file CSV input kemudian akan diimpor ke tabel MySQL. Kami kemudian dapat memeriksa data yang diimpor pada antarmuka meja kerja MySQL
Kesimpulan
Artikel ini mendemonstrasikan tiga metode untuk mengimpor file CSV ke MySQL. Anda sekarang tahu cara mengimpor file CSV ke MySQL menggunakan baris perintah, phpMyAdmin, dan MySQL Workbench. Jangan ragu untuk menambahkan alat tambahan apa pun yang dapat membantu mengimpor file CSV ke MySQL