Guide To Import A Csv File Into Mysql Database

By Author: amsgoldsmith
Total Articles: 25
Comment this article

A CSV (comma-separated values) file is a file containing data in the form of plain text and using commas to separate different values. While importing data from one database to another, CSV acts as a standard file format. Let’s say you want to send the data in an excel worksheet to another database software like MySQL. You cannot do it directly. You must convert the data into a CSV file before importing it to another database. They are designed to make importing and exporting easier.

In this article, we will show you different ways in which you can import your CSV files into the MySQL database. To begin with, you need :
MySQL installation on your device
CSV file you want to import

Importing CSV file into MySQL using Command Line
Using the following steps, you will be able to import a CSV file into the MySQL database easily.

Get access to MySQL command line client: after installing MySQL on your desktop, use this command to get access to its terminal. Enter your password, and you are done.
mysql -u my_user_name -p

Select or create a new database: the next ...
... step is to choose a database from the default, or you can also create one according to your need. By clicking on SHOW DATABASES, you can check out MySQL's databases and select one. If you have created one, use the command.

USE [Database_Name];

Create a table in the database: Now we have to create a table in the database taking care that the number of columns should be the same as in the CSV file we want to import. Any error will fail the importing process.

Import the CSV file into the table: once you create the table, the data in the CSV file is ready to be imported. To import into a particular table, use the following command :
LOAD DATA INFILE 'C:/ProgramData/MySQL/MySQL Server 8.0/Uploads/Sample.csv'
INTO TABLE Example
FIELDS TERMINATED BY ','
ENCLOSED BY '"' LINES
TERMINATED BY '\n'
(Num, Name, Salary, Place);

By following the above steps, you can easily import your CSV file into the MySQL database using the command line.

Importing CSV file into MySQL using Workbench
Now let’s look at another way to import CSV files using Workbench. MySQL provides you with a graphical user interface, MySQL Workbench, that helps manage databases and write SQL queries. With the wizard's help, data can be imported or exported into a CSV or JSON format. To get started, you need to install MySQL Workbench on your device.

Open MySQL Workbench and go to the wizard: look for the navigator panel on your MySQL Workbench. Find the schemas section on it. It includes all the tables and databases. To open the import wizard, right-click on any of the tables and select tables data import wizard.

Select a file to import: once you open the import wizard, it will ask for the data to import. Simply mention the CSV file name containing the data to be imported.

Select a table or create a new one: To import the data of the file selected in the previous step, you have two options. You can either import it into one of the existing tables or create a new one.

Import data: once you select the database, your data is ready to be imported. Simply click on the import data tab.

Click on next, and you will get the import results.

Importing CSV using phpMyAdmin
phpMyAdmin is a free software tool with a flexible coding base. With an intuitive interface, it supports tables, indexes, columns, relations, and databases. To expand its reach, it is translated into 72 languages supporting LTR and RTL languages. It allows importing and exporting of data in various formats. Let’s see how you can import your CSV into MySQL using PHPMyAdmin.

Select or create a table: once you install PHPMyAdmin, go to the left panel of the user interface and find the list of all databases and tables. You need to select or create a table for importing your data. While creating a new one, keep the number of columns the same as in your CSV file.

Select the import menu tab: click on the import menu to allow data import into the selected table.

Select CSV file: click on ‘choose file’ and browse for the CSV file you want to import.

Go through the format-specific options tab: In this tab, you can change the format-related specifications of your CSV file.

Finally, click the ‘Go’ button, and your CSV will be imported to MySQL successfully.

Conclusion
This article presents a guide to importing a CSV file into a MySQL database using the command line, workbench, and phpMyAdmin. Follow the easy steps in the right order to get your files imported! Follow us to know more.

Source link here : https://thegetvirtualsupport.blogspot.com/2022/09/guide-to-import-csv-file-into-mysql.html