Hello guys, welcome back to CybercityHelp once again. So in our last article, we had explained about the installation process of MySQL. We hope that you could have been able to install MySQL successfully in your computer. So if you are ready with your installation, you can proceed with this article.
However, if you are a new user who hasn’t installed MySQL, so we request them to first install it and then continue learning without any interruption. If you don’t know how to install, you may read our installation guide article. Here is the link: MySQL Installation Guide.
So in our today’s article, we are going to discuss about how we can import CSV or sample SQL files into MySQL. We will tell you it’s simple commands, it’s methods, and little explanation with examples. So now, let’s get started.
How to Import an SQL File into MySQL?
So guys, how you can import an SQL file? Let’s understand in this way, so I have one SQL File, which is hosted on GitHub, I will provide you the link of that file. So first open that link in a new tab, once you open that, you will be redirected to our GitHub repository, so then you will get “Download Raw File” option, so click that and start downloading the Raw file. Once the file is downloaded then save the file in your desired directory. Here is the download link of that sample SQL file: SQL File Sample
After saving the SQL file in your desired directory, come back to your MySQL. In your MySQL, go to Server. In the server, you’ll find Data Import option, so click on Data Import.
Once you click on Data Import, there are two kinds of data you will be able to import. Here, go for the second option, that is, Import from Self-Contained File. In this option, choose the location of the database that you have just downloaded. So click on it, then click on open and then drag or drop or select the file manually.
Once you click on Open, then you may get Start Import option, so click on start import. At the bottom, you will find Start Import, and if it is not visible, then go to Import Progress, where you will get Start Import as well.
Once you are done, you can see the file has been uploaded over there. So now click on Refresh on the left-hand side. Once you refresh, you will get the data of the Classic Models. Open the table, and you will see you have the Customers, Employees, Offices, and all the details, Orders, Payments, Product Lines, and Products data as well. So this is how you can install any MySQL raw file.
How to Import Small CSV Files into MySQL?
Now let’s talk about how you can import a CSV file in MySQL. So guys, first we need a database to import the CSV file. So, either use your existing database or create a new database. So inorder to create a new database, go to the query window, and write this command:
CREATE DATABASE Test;Now, if you run this command and refresh, you will see that a new database has been created with “Test” name. But once you open it, it will be blank database means no table, nothing.
So from here our work starts, from here only we will be able to import a CSV file into our blank database. So right-click on Import option from the top menu bar, then you will see Table Data Import Wizard. So click on this, and then go to Browse, and in the browse section, if you have any data, you can just click on it.
For example, let’s say if you open any PZA Place Sales file, here you may get files like Orders or PZA Types. Let’s say if you want to open PASA, click on Open, then click on Next. Then click on Create a New Table called as PASA, so click on Next again.
Then you can see in your file that all your data is in text format, and one of them might be in float, also known as double in our database language. So now click on Next again, click on Next again, wait for a few seconds, and your data is being imported.
So now all your records might have imported over there. If you refresh and open it, you can see that you have PASA’s file over there. This generally does not happen in the industry. You do not import CSV files or Excel files into SQL. Usually, our data goes from SQL to CSV or XLSX files, but it does not happen the other way around.
How to Import Large CSV Files into MySQL?
Well, this method works fine when we have a small dataset, like 100-200 rows. But what if we have plenty of rows? In that case, this method will not work directly. You will have to create a table by yourself.
Let’s suppose if you try to import a table which has more data, like Order Details, which has 21,000 rows. If you browse the file, you will see that your date and time are coming in text format. You can convert it to DateTime.
But when you excute them, you will see it has been executed, but it says zero records are imported. That means the table is created, but none of the records are imported because you had a huge amount of data.
So how you can solve this problem? How you can import large CSV files which has lots of data. So in such situations, you can create your table manually. Let’s suppose you have three columns: Order ID, Order Date, and Order Time. To create the table manually, write this command in the query window:
CREATE TABLE orders (
order_id INT NOT NULL,
order_date DATE NOT NULL,
order_time TIME NOT NULL,
PRIMARY KEY (order_id)
);Then press Run, and refresh the database. Make sure you have selected the correct database i.e your “Test” database.
Now you will have the Orders table. So right-click again, click on Data Import Wizard, and this time, choose the existing table, which is Orders. Then click Next. Map the columns (Order ID, Order Date, Order Time) to the destination columns. And again click Next, then Next again.
Now, it will take a while to import the data. So whatever rows you have will be imported. Once it is done, click on Next. You can see all the records have been imported successfully.
Alright, so this is how you can import a CSV file with a large amount of data into MySQL. If you refresh or recall the query, all your data has been imported.
But note that, this method is not applicable for industry. You do not import CSV files into the database in the way we explained you. But this method is good for practice purposes, this is the correct way to do it.
So we hope that, it might have been clear for you, that how you can import SQL files or CSV files into MySQL. In our upcoming articles, we are going to discuss more about such techniques and other ways. So in case if you still haven’t understood it’s methods, you can feel free to ask your doubts in the comment section. So yeah that’s all for today’s article, thank you so much for reading till here!!
“So keep learning, keep growing!”
