Guide to Import a CSV File into MySQL Database

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.


  1. 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 


  1. 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];

 

  1. 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.

 

  1. 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. 


  1. 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.






  1. 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.




  1. 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. 


 

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

 

  1. 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.


  1. 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.


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


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



  1. 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.



Comments

Post a Comment

Popular posts from this blog

How much does an average bookkeeping firm charge on a monthly basis?

Image
If your business is growing, your bookkeeping requirements must be rising, too. As the size of the company increases, the number of monthly transactions in terms of payroll and bills also increase. If you Get Online Bookkeeping Services , you can save a significant amount of time and resources you would have otherwise spent in maintaining accounts and balance sheets. Let's take a look at the expected costs of hiring Online Bookkeeping Services for small business .   How much do bookkeeping services cost? A Bookkeeping services fee can vary depending upon the size of your company. As discussed, various variables can determine the bookkeeping costs for a business. In addition, the overall cost also depends on whether you invest in a basic bookkeeping service or a full-service accounting company. Basic Online Bookkeeping Services for entrepreneurs is ideal for small businesses that are in their initial phases. They should look for basic services, such as paying bills, get
Read more

Why outsource back-office work: data entry & bookkeeping Services

Image
In today's world of globalized business, data entry & Online bookkeeping Services in India has become the most common term. Industries such as medical, hospitality, telecommunications and manufacturing are increasingly outsourcing the data entry requirements of well-qualified BPO companies that can provide high-quality business coupled with the benefits of outsourcing.   Outsourcing can be described as subcontracting your existing office work to the external business, whether you can hire an individual or a group of professionals, depending on the scope of the project. Today the market is full of efficient and affordable communication tools. The Internet and VOIP have changed the way we communicate with the rest of the world. The Internet is easily accessible anywhere anytime, so the country in which you're outsourcing your project does not matter to your requirements.   Today, all organizations are looking for cost savings and ways to allocate their internal resour
Read more

What Online Bookkeeping Services Offer to Smaller Business

Image
In the event that you own your small business, you then you probably understand the substance of bookkeeping and its own frustrating properties. Bookkeeping is merely designed to help the management of the business enterprise and will not play any role towards the satisfaction of a person. It gets incredibly annoying once you end up buying bookkeeping software, which will not produce the type of report you need or an away site accountant who'll only submit a written report after seven days. The online bookkeeping services are much more effective and you ought to contemplate using them.  Confidentiality is among the benefits that you should consider when making choices about using on the online bookkeeping services in India . Numerous business owners will usually dread the thought of having their home elevators the web fearing that somebody might get usage of it. However, this isn't the situation with today's protection measures; you will be secured that nobody will actuall
Read more