MYSQL Import Data from CSV File

A CSV or comma-separated value document is a delineated text document that distinguishes values from a comma. Every line is its information record. Each data, parted by commas, comprises one or extra fields. The origin of the title for this document layout is the usage of the comma as a field divider. For sharing information between various programs, such documents are used. For instance, Database and contact administrators also endorse CSV files. The theory is that from one program to a CSV document, you may transfer complex information and afterward import the information in that CSV document to some other program. In this tutorial, we will learn how to import data from a CSV file into MySQL workbench. Let’s get started.

Step 01: Create a CSV File

To create a CSV file, you have to open Microsoft Excel in your system and add some data as we have created a file and added the below record into it. Make sure to create field names easy to make it convenient for yourself. Now, save this file with the ‘.csv’ extension. You can change its file format while saving it to any folder. Go to ‘File,’ and hit on ‘Save As.’ Select the location, name the document and below the name field, select the file format. We have named our file as ‘book.’

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image1-38.png" data-lazy- height="364" src="data:image/svg xml,” width=”601″>

Step 02: Connect Database

To import your CSV file data into MySQL, you must first connect your Workbench with the Database. For this purpose, open the newly install MySQL Workbench 8.0 and go to the ‘Database’ option. Hit on the ‘Connect to Database’ option from the listed options.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image3-35.png" data-lazy- height="557" src="data:image/svg xml,” width=”952″>

The below new window will be opened. Select your Database, e.g., ‘Stored Connection.’ After that, set the ‘Parameters’ and then hit the ‘OK’ button to link to the Database. You will be heading to the MySQL Workbench Schema’s Graphical User Interface window.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image2-37.png" data-lazy- height="485" src="data:image/svg xml,” width=”677″>

Step 03: Create Schema

In the Local instance or database window, you will see a list of schemas already created by MySQL. You have to generate a new database or use the already created Database as we have a database ‘data’ as our default database. We will utilize it to import data.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image5-31.png" data-lazy- height="299" src="data:image/svg xml,” width=”339″>

Step 04: Create Table

In the query area of schema’ data,’ create a table ‘book’ using the schema by ‘use’ command. Make sure to use the same names for the file and its fields as used in the CSV file. After that, select the create query to be executed and tap on the flash button. The new table ‘book’ will be created in the database’ data.’

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image4-33.png" data-lazy- height="264" src="data:image/svg xml,” width=”586″>

You can see that the table ‘book’ has been created, and you can change its constraints.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image7-29.png" data-lazy- height="270" src="data:image/svg xml,” width=”775″>

The table is currently empty as below.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image6-29.png" data-lazy- height="78" src="data:image/svg xml,” width=”609″>

Step 05: Import CSV File Data into Table

Let’s start importing data from the CSV file into the newly created table. Navigate the Schemas and click on the ‘data’ database. There will be a newly created list of tables under the table option of database’ data.’ Hit on the just created table ‘book’ and right-click on it. Now click on the option, i.e. ‘Table Data Import Wizard’ option.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image9-24.png" data-lazy- height="426" src="data:image/svg xml,” width=”563″>

You will see, the window will be opened as shown. Select the file ‘book’ by adding its location. Tap the ‘Browse’ button to pick the file path.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image8-26.png" data-lazy- height="192" src="data:image/svg xml,” width=”764″>

As you can have a glimpse that we have saved a file ‘book’ into some directory. Click on it, and tap on the ‘Open’ button to continue.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image12-16.png" data-lazy- height="457" src="data:image/svg xml,” width=”654″>

We can view that the file path has been selected. Hit on the ‘Next’ button to proceed.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image10-24.png" data-lazy- height="71" src="data:image/svg xml,” width=”746″>

We have the following window now. If you want to add your CSV file data into an already created table, you have to opt for, ‘Use the existing table’ radio button. On the other hand, if you want to import your CSV file data into the new table, you have to create a new table by choosing the’ Create new table’ by choosing the radio button. In this option, you have to select the database name that is already located in your server and give a new table. If you have selected the ‘Use existing table’ option, you have to checkmark the checkbox ‘Truncate table before import.’ Then, click on the ‘Next’ button to go on to the next step.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image11-19.png" data-lazy- height="224" src="data:image/svg xml,” width=”760″>

Now we are on the ‘Configure Import Settings’ window. In this window, you have to select Encoding, e.g., we have selected ‘utf-8’, which is standard. Make sure the Source and Destination columns have similar names. If not, you can select the destination column names by tapping on the column’s name and checking the list of column names. If there are any extra columns generated, you can unselect them here to avoid the creation of extra fields. If everything is set, then you are good to go. Hit on the ‘Next’ button to get closer to import data from the CSV file.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image13-14.png" data-lazy- height="487" src="data:image/svg xml,” width=”756″>

Now, it has two steps listed to import the file data. Firstly, it will prepare the import, and after that, it will import the data file. We have to just tap on the ‘Next’ button to initiate the import process.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image14-13.png" data-lazy- height="225" src="data:image/svg xml,” width=”592″>

After tapping the ‘Next’ button, it will start importing. It will hardly take 2 minutes to import all the data from the file ‘book.’ If you have too much data in the file, it may take a little longer.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image15-11.png" data-lazy- height="257" src="data:image/svg xml,” width=”624″>

After the import has been completed, hit on the ‘Next’ button again.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image16-10.png" data-lazy- height="259" src="data:image/svg xml,” width=”609″>

Finally, the import process has been completed. Tap on the ‘Finish’ button to end it efficiently.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image17-9.png" data-lazy- height="146" src="data:image/svg xml,” width=”553″>

Step 06: Check the Table

We can now refresh the table ‘book to see if it has been updated or not. See! The CSV file data has been magnificently imported into a table ‘book’ without any error or change.

<img alt="" data-lazy- data-lazy-src="https://kirelos.com/wp-content/uploads/2021/03/echo/image18-7.png" data-lazy- height="291" src="data:image/svg xml,” width=”572″>

Conclusion

Finally, we have done all the necessary steps to import data from a CSV file to a MySQL Server Workbench 8.0.