How to import CSV files in MariaDB | MariaDB import CSV

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.

Download

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