MariaDB import CSV

Recently, I got the requirement to work with a requirement to import CSV file in MariaDB. In this MariaDB tutorial, we will learn about “how to import CSV MariaDB“.

MariaDB import csv

Whenever we have a large amount of data or information, and we want that information in our database, it is in a file or a different file format like CSV.

Sometimes, users accumulate data and want to convert it into MariaDB easily. Processing a large amount of data can seem intimidating,but it isn’t a difficult task.

In MariaDB, the bulk data can be imported using the LOAD DATA INFILE statement.

Let’s create the CSV file using a text editor like Notepad. This file contains the names of countries like United Kingdom, 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 csv_table with the columns country_id and country_name. The name of this column should match the header in the CSV file that will be imported.

Now, import the country CSV file into the current database. Here, we are using the default database, MySQL, 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: This field specifies that the data in the file is separated by a specific delimiter, like CSV file data is separated with a 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: This field specifies when a line ends in the file or 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 did 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, and for that, we will need a GUI tool like HeidiSQL. Other tools for GUI MariaDB include Webyog/SQLyog, HeidiSQL, and dbForge Studio.

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, which 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

A large CSV file contains a large amount of data or records. We can also import this kind of CSV file in MariaDB. So we will use the uscities.csv file, which contains around 28000 records.

Download the uscities file that is given below.

Download

As usual, first, create the table as large_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“.