How to load files into MariaDB

In this MariaDB tutorial, we will discuss how to load files into MariaDB and also look at several examples related to it. There are lists of the topic that comes under discussion:

• How to load files into MariaDB
• How to load CSV files in MariaDB
• How to open the .sql file in MariaDB
• How to set max_open_files in MariaDB
• How to install the file_key_management plugin in MariaDB
• How to load text files in MariaDB
• How to load Excel file in MariaDB

How to load files into MariaDB

In this section, we will learn how to load files in the MariaDB and will explain its usage with help of an example.

In MariaDB, some steps are needed to be followed to load a file:

• Open your HeidiSQL software tool.
• After this, click on the FILE tab.
• Search for LOAD SQL File in the FILE drop-down list.
• Then open the LOAD SQL File or use the shortcut CTRL + O.
• In the last, open the file by using the OPEN button.

• Then, there will one another dialogue box that will display a warning, just press the YES button to continue.

Then all the records will be imported into the HeidiSQL 10.6.7 Tool of the MariaDB Server. Here is an image of imported file records in the HeidiSQL Tool as given below:

Also, check: What is MariaDB Sharding

How to load CSV files in MariaDB

In this sub-topic, we will learn how to load CSV files into the MariaDB database and it is explained in points with diagrams.

• First, create a table as MOCK_CSV of that CSV file in the MariaDB database. Here is the sample SQL code for the table creation.

create table MOCK_DATA (
	data_id INT,
	Person_Firstname VARCHAR(50),
	Person_lastname VARCHAR(50),
	Person_email VARCHAR(50),
	Person_gender VARCHAR(50),
	Person_ipaddress VARCHAR(20)
);

• Execute the MOCK_DATA table in the query.
• Now, open the HeidiSQL tool and click on the IMPORT a CSV or tab delimiter file button.

• Import the file path of the file in the FILENAME tab. i.e; (C:\Users\aadar\Downloads\MOCK_CSV.csv).
• Rest all the portion remains the same in the dialogue box.
• Change the table name to MOCK_DATA in the dialogue box. The main reason is that without the creation of a table it will not import and errors will be returned.
• Then, click on the IMPORT button to import the file in the HeidiSQL SQL tool.

If we want to check whether the CSV files have been executed or imported in the HeidiSQL or not then use the SELECT statement to check it.

SELECT * FROM MACK_DATA;

Read: MariaDB Rename Table

How to open the .sql file in MariaDB

In this section, we will learn how to open the .SQL extension file in MariaDB and it is explained with the help of points and diagrams.

In MariaDB, with the help of the SOURCE command and with the extension file of the SQL file, it will run. But remember that it needed to run on Mysql Client as the Command prompt. The syntax to open and run the .SQL file in the MariaDB is given below:

SYNTAX:

source 'file-path';

The query to open the .SQL file in the MariaDB is given below:

EXAMPLE:

source C:\Users\aadar\Downloads\mock_data.sql;

In the above query, with the help of the source command and file path of the SQL extension file of the mock_data table. It will run and will retrieve all records of the MOCK_DATA table.

Read: MariaDB Queries

How to set max_open_files in MariaDB

In this section, we will learn how to set max_open_files in MariaDB and its explained with the help of an example.

In Linux, the user uses to set the max_open_files limit in MariaDB. There is a file called my.cnf file which can’t be edited. If the user wants to see the max_open_files in the MariaDB then it can be shown by the following query:

EXAMPLE:

SHOW GLOBAL VARIABLES LIKE 'open_files_limit';

In the above query, the value of open_files_limit is 34320 which is shown by using the SHOW GLOBAL VARIABLES statement.

The method to set open_files_limit in MariaDB by using Linux is given below:

EXAMPLE:

mysqld open_files_limit=28450;

service mysql stop && service mysql start;

service mysql stop && service mysql start --open-files-limit=28450;

In the first query, we have set the open_files_limit of MariaDB which is 28450. Once the value has been set, we have to stop the service of MySQL and restart it again which is done by service mysql stop && service mysql start statement.

Because it has an endless loop to check the mysqld return code after it stops, this may not properly terminate mysqld_safe. There is a specific return value that instructs mysqld safe to restart mysqld. This means that when mysqld safe handles a mysqld restart, it does not terminate.

The service mysql start command will then start a new mysqld safe process. If we can’t edit my.cnf file in Linux, then we need to specify it by the third query and give the limit of open_files_limit as a 28450 value in it.

Read: MariaDB JSON Function

How to install the file_key_management plugin in MariaDB

In this section, we will learn how to install the file_key_management plugin in MariaDB and which is explained with the help of an example.

MariaDB packages include the file key management. so or file key management.dll shared library, which is used by the File Key Management plugin. No additional packages are required because the shared library is included in the core server package.

Read: MariaDB Between + Examples

How to load a text file into MariaDB

In this section, we will learn how to load or import text files into MariaDB and which is explained with the help of an example.

In MariaDB, the LOAD DATE INFILE statement is the easiest way to import data from a plain text file into the database. This is what to enter in the MySQL client to load the data in the file called PERSONAL_DETAIL.txt into the table PERSONAL_DETAIL. Before we import the data from the PERSONAL_DETAIL.txt file, we need to create the empty PERSONAL_DETAIL table for import.

The query to create an empty PERSONAL_DETAIL table is given below:

CREATE TABLE PERSONAL_DETAIL(
Just_id int,
Person_Name VARCHAR(50),
Male_Female VARCHAR(10),
ip_address VARCHAR(15));

If we want to check the text file is imported in the MySQL tool, then we use the SELECT statement. The query is given below:

LOAD DATA INFILE 'Personal_Detail.txt'
INTO TABLE personal_detail
FIELDS TERMINATED BY ',';

SELECT * FROM personal_detail;

In the first query, we have imported the text file as PERSONAL_DETAIL.txt from the PERSONAL_DETAIL table and in the query, we have terminated the ‘,’ comma from the text file so that it gets imported easily.

If we want to check the data are retrieved from the PERSONAL_DETAIL table then we use the SELECT statement.

Read: MariaDB GROUP BY with Example

How to load Excel file into MariaDB

In this sub-topic, we will learn how to load the EXCEL file into MariaDB and which is explained with the help of an example.

Here are the steps to be followed to load Excel files into MariaDB:

• First, open the spreadsheet in the Excel Tool and save it to a .csv file.
• Open the MySQL tool.

• Create a new database and connect to it.

• Now, let’s create an empty table with a column with the same datatype as shown in the spreadsheet:

CREATE TABLE PERSONAL_DETAILS(
Just_id int, 
Person_Name Varchar(50),
Male_Female Varchar(15),
ip_address Varchar(20));

DESC FPERSON_DETAIL;

• Now, load the CSV file into the table by using the LOAD DATA statement:

LOAD LOCAL INFILE '/Users/aadar/Downloads/Person_Detail.csv'
INTO TABLE PERSON_DETAIL 
FIELDS TERMINATED BY ',' IGNORE 1 LINES;

You may also like to read the following MariaDB tutorials.

• MariaDB If Null + Examples
• MariaDB Logs – Helpful Guide
• MariaDB Truncate Table
• MariaDB Median – Complete Tutorial
• MariaDB Foreign Key + Examples

So, in this MariaDB tutorial, we discussed How to load files into MariaDB and also illustrated some sample examples. There are lists of the topic that comes under discussion:

• How to load files into MariaDB
• How to load CSV files in MariaDB
• How to open the .sql file in MariaDB
• How to set max_open_files in MariaDB
• How to install the file_key_management plugin in MariaDB
• How to load a text file into MariaDB
• How to load Excel file into MariaDB