Bagaimana cara menambahkan data csv ke database mysql?

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

1. Menggunakan Baris Perintah
2. Menggunakan PhpMyAdmin
3. 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 -p

Langkah 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

1. Pindahkan masukan. csv ke dalam struktur folder yang ditentukan
2. 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;

1. LOAD DATA INFILE – Ini menentukan lokasi file CSV input
2. INTO TABLE – Ini menentukan tabel tempat data akan diisi
3. FIELDS DIHENTIKAN OLEH – Ini menentukan pembatas di mana nilai-nilai individual dalam file dipisahkan
4. TERLAMPIR OLEH – Ini menentukan simbol yang menentukan nilai dalam file CSV
5. GARIS DIHENTIKAN OLEH – Ini menentukan kode untuk jeda baris
6. 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

# Secure File  Priv.
secure-file-priv=”C:/ProgramData/MySQL
/MySQL Server 5.7/Uploads”

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 mysql

Langkah 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)
);

Validasi 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)
);

Mengubah 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

Bagaimana cara memasukkan file csv ke database MySQL?

Bisakah kita mengimpor CSV di MySQL?

Apa cara tercepat untuk mengimpor file csv ke meja kerja MySQL?

Bagaimana cara membaca file CSV ke MySQL?