DbSchema | How to import a CSV file in MySql
1.Using SQL
Importing a csv file to your MySQL database is very simple. In this article, I’m going to show you two ways to do this.
In this example I will use a file stored in C:/Temp, named product.csv. This file contains 3 items for the __product` table and it looks like this:
product_id,name,price
1,"coconut","20"
2,"wine",100
3,"banana","15"
The first row contains the column names and the rest product properties.
First of all, you have to enter the MySQL shell and select the database. In order to do this, you have to:
Open the command line by typing __cmd` in Start;
Execute the next query:
mysql.exe -u root -p
After you insert the password you are ready to import your
- Execute __SHOW DATABASES` in order to see all your databases;
- Then, execute __USE database_name` to select the desired database;
- If you want to see what tables are in the database, execute __SHOW TABLES`.
If everything worked, you can now execute the query that will import the data from the file:
LOAD DATA LOCAL INFILE 'C:/Temp/product.csv'
INTO TABLE product
FIELDS TERMINATED BY ','
IGNORE 1 ROWS
(id, name, price);
Now, let’s break this query to understand better what’s happening:
- LOCAL tells the database that the file is expected to be found locally;
- FIELDS TERMINATED BY ‘,’’ specifies that the comma is the delimiter;
- IGNORE 1 ROWS tells the database to ignore the first row from the document, the one that specifies column names.
To check if the data was imported correctly, execute:
SELECT * FROM table_name;
2.Using DbSchema
With DbSchema, importing a CSV file is way more easier. First, you have to access the Data Importer from the top menu Data Tools > Import Data From File.
From the data importer you can select the file, the schema and the table. Next, you can modify the settings for date, columns (you can choose if what columns to load) and more.
To verify if the data was imported correctly, press on the table header while holding CTRL + SHIFT.
That’s all. If you want to try DbSchema, download it here
