Export MariaDB (MySQL) database table to a CSV or spreadsheet file

In this tutorial I’ll show you how to export MariaDB (MySQL) database table data to CSV format on an external file (The CSV format is potable and can be used across multiple applications especially spreadsheet applications like Excel, LibreOffice Calc) using phpMyAdmin.

Steps followed by this tutorial are

  1. Define the structure of the MariaDB (MySQL) database table and create it and populate it with a data sample.

  2. Export the MariaDB (MySQL) database table to an external CSV file using phpMyAdmin.

1- Define the structure of the MariaDB (MySQL) database table and create it and populate it with a data sample.

A- Define the structure of the MariaDB (MySQL) database table and create it

In this step we are going to define the structure of the Items database table and add the SQL DDL statement to create the table in MariaDB (MySQL) database.

The Items table is a simple database table constructs of 8 columns contains basic Item data item, purshase_price, sale_price, tax, barcode, item_size, notes and the primary key column item_id.

Connect to your MariaDB (MySQL) database server using a user that has a create table privilege with the tool you prefer (shell , phpMyAdmin or any other tool ) and execute the following SQL DDL statement to create the table Items.


CREATE TABLE items ( item_id INT NOT NULL AUTO_INCREMENT , item text DEFAULT NULL,
purshase_price decimal(10,0) DEFAULT NULL, sale_price decimal(10,0) DEFAULT NULL, 
tax decimal(10,0) DEFAULT NULL, barcode text DEFAULT NULL, item_size text DEFAULT NULL,
 notes text DEFAULT NULL, 
PRIMARY KEY (item_id)) ENGINE = InnoDB;

phpMyAdmin create items table MariaDB (MySQL) database
Creating Items table in MariaDB (MySQL) database using phpMyAdmin

In the following picture you can see the result of executing the create table sql statment using phpMyAdmin.

phpMyAdmin result of executing create items table MariaDB (MySQL) database
phpMyAdmin result of executing create items table MariaDB (MySQL) database

B- Populate Items table with a data sample.

In this step we are going to add data to the Items database table using INSERT SQL statement.

Run the following code to insert some rows in the newly created Items database table.


INSERT INTO items(item, purshase_price, sale_price, tax, barcode, item_size, notes)
VALUES ('Bescuits',2,3,0,622009665542,'M',NULL);

INSERT INTO items(item, purshase_price, sale_price, tax, barcode, item_size, notes)
VALUES ('Ice Cream',2,3,0,622009665543,'M',NULL);

INSERT INTO items(item, purshase_price, sale_price, tax, barcode, item_size, notes)
VALUES ('Chocolate',3,4,0,622009665544,'M',NULL);

INSERT INTO items(item, purshase_price, sale_price, tax, barcode, item_size, notes)
VALUES ('Jelly',4,5,0,622009665545,'M',NULL);

INSERT INTO items(item, purshase_price, sale_price, tax, barcode, item_size, notes)
VALUES ('Borio',2,3,0,622009665546,'M',NULL);

INSERT INTO items(item, purshase_price, sale_price, tax, barcode, item_size, notes)
VALUES ('Cockie',1,2,0,622009665547,'M',NULL);

The image shows executing sql insert statements using phpMyAdmin .

phpmyadmin insert sql dml mariadb (mysql) database
phpMyAdmin insert data into MariaDB (MySQL) database table

And here as we can see the result of executing the sql insert commands using phpMyAdmin.

result of executing the sql insert commands using phpMyAdmin
Result of executing the sql insert commands using phpMyAdmin


Let's query the Items table to view the newly inserted data.


SELECT * FROM items;

phpmyadmin sql query items database table against mariadb (mysql)
phpMyAdmin executing SQL queries against MariaDB (MySQL) database table

2- Export the MariaDB (MySQL) database table data to an external CSV file using phpMyAdmin.

Now check the "Check all" check box to select all the records (rows) or select the records you want to export, Then hit the Export button.

Export mariadb (MySQL) database table records
Export mariadb (MySQL) database table records

From the export page choose CSV from the format list, Also you can select CSV for MS Excel instead, Then hit Go button.

Export mariadb (MySQL) database table records to csv format

By hitting Go button the CSV exported file will be generated, Save the file. If you open the file with any text editor you will see the CSV format generated from mariadb (MySQL) table records as the following image.

the mariadb (mysql) exported data as csv format
Exported CSV data from Mariadb (MySQL) database table items shown in gedit text editor

 

Add new comment

Restricted HTML

  • Allowed HTML tags: <a href hreflang> <em> <strong> <cite> <blockquote cite> <code> <ul type> <ol start type> <li> <dl> <dt> <dd> <h2 id> <h3 id> <h4 id> <h5 id> <h6 id>
  • Lines and paragraphs break automatically.
  • Web page addresses and email addresses turn into links automatically.
CAPTCHA
This question is for testing whether or not you are a human visitor and to prevent automated spam submissions.
2 + 3 =
Solve this simple math problem and enter the result. E.g. for 1+3, enter 4.