In this MariaDB tutorial, we will learn about “MariaDB import CSV” and cover the following topics.
- MariaDB import csv
- MariaDB import csv date format
- MariaDB import csv windows
- MariaDB import csv large csv
MariaDB import csv
Whenever we have a large amount of data or information and we want that information in our database that is in some file or in a different file format like CSV.
Sometimes, users, have data accumulated and want to convert it into MariaDB easily. To process a large amount of data seems very intimidating but it isn’t a difficult task.
In MariaDB, the bulk amount of data can be imported using the LOAD DATA INFILE statement.
Let’s create the CSV file using a text editor like Notepad and this file contains the name of countries like United Kindom, Australia, Canada, and New Zealand.
Before importing the file, Create a table in the MariaDB named csv_table.
CREATE TABLE csv_table(country_id int,country_name varchar(100));
Here, we have created the table named csv_table with columns country_id, country_name. This column’s name should match with the header in the CSV file that will be imported.
Now, import the country CSV file in the current database, here we are using the default database that is MySQL database in MariaDB.
LOAD DATA INFILE 'C:/country.csv' INTO TABLE csv_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (country_id,country_name);
- LOAD DATA INFILE: It is a statement to load the file from a specified path. As we are loading the CSV file from the C:/country.csv.
- TERMINATED BY: It is the field where we specify that our data in the file are separated by a specific delimiter like CSV file data is separated with comma(,). So we have mentioned the comma with a single quotation mark in the above code.
- ENCLOSED BY: It is also the field where we specify that our data is enclosed with some symbol.
- LINES TERMINATED BY: It is the field to specify that when the line end in the file or it is was for MariaDB to identify the end of the line in the file.
Read: MariaDB Timestamp
Mariadb import csv date format
In MariaDB, we can import the CSV file containing the date in a different format using the LOAD DATA INFILE statement as we have done in the above sup-topic.
Create the CSV file using a text editor like Notepad.
Create the empty table as date_col to store the data from the CSV file.
CREATE TABLE date_table(date_col date);
Now, import the CSV file using the below query.
LOAD DATA INFILE 'C:/date.csv' INTO TABLE date_table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (date_col);
Read: MariaDB Vs SQLite
Mariadb import csv windows
The CSV file can be imported into MariaDB on windows for that we will need the GUI tool like HeidiSQL. There are other tools for GUI MariaDB such as Webyog/SQLyog, HeidiSQL, dbForge Studio for MariaDB.
Open the GUI tool for MariaDB as we are going to use the HeidiSQL tool, After opening the HeidiSQL tool a session manager dialogue appears.
In the above session manager dialogue, select the session name and click on Open. After clicking on open the main dialogue will appear that is given below.
In the above image, click on Tools then click on IMPORT CSV file.. from the options.
An Import text file dialogue will appear.
From the above dialogue,
(1) Locate the CSV file and (2) encode from the Input file section.
(3) Go to the Control characters section and fill the necessary field.
(4) Select the database name where we want to create the table from the CSV file, and we can also choose the existing where we want to import the CSV file from the Destination section.
(5) Click on the Import button to import the CSV file.
Read: MariaDB ERROR 1064
Mariadb import csv large csv
The large CSV file contains a large amount of data or records, here we can also import this kind of CSV file in MariaDB. So we will here use the uscities.csv file that contains the records around 28000.
Download the uscities file that is given below.
As usual first, create the table as larg_csv using the below code.
CREATE TABLE large_csv(city varchar(50),city_ascii varchar(50),state_id varchar(50),state_name varchar(50),county_fips int ,county_name varchar(50),lat float,lng float,population int,density int);
Run the below query to import the uscities CSV file.
LOAD DATA INFILE 'C:/uscities.csv' INTO TABLE large_csv FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS (city,city_ascii,state_id,state_name,county_fips,county_name,lat,lng,population,density);
Run the below query to view the imported data from the CSV file.
SELECT * FROM large_csv;
You may also like to read the following tutorials on MariaDB.
- How to Create Function in MariaDB
- How to Create Trigger in MariaDB
- MariaDB Vs SQL Server
- How to Drop Column from MariaDB Table
- How to Add Column in MariaDB
- MariaDB Update Statement
So in this tutorial, we have learned about “MariaDB import CSV” and covered the following topics.
- mariadb import csv
- mariadb import csv date format
- mariadb import csv windows
- mariadb import csv large csv
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.