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.’
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.
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.
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.
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.’
You can see that the table ‘book’ has been created, and you can change its constraints.
The table is currently empty as below.
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.
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.
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.
We can view that the file path has been selected. Hit on the ‘Next’ button to proceed.
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.
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.
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.
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.
After the import has been completed, hit on the ‘Next’ button again.
Finally, the import process has been completed. Tap on the ‘Finish’ button to end it efficiently.
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.
Conclusion
Finally, we have done all the necessary steps to import data from a CSV file to a MySQL Server Workbench 8.0.
