Many business scenarios use more than one database and usually they are disconnected. Sharing data between these databases is a common demand to create valuable business insights or make critical decisions. To achieve this, one possible way is to get them connected to each other on your data management platform directly. However, this action might pose many challenges, such as security risks, data integrity issues, and conflicts in updating data. In such cases, sharing data by files using the commonly adopted formats such as CSV and Excel is the best solution.

This tutorial explains how to import or export CSV/Excel files in MySQL, one of the most popular databases. Before diving into it, make sure that MySQL is installed on your computer.

Export files from MySQL

You can use syntax into outfile to export CSV or Excel files from MySQL.

select * from users into outfile '/opt/homebrew/var/mysql/new_db/users.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"';

• '/mysql/output/users.csv' contains the path and the name of the exported file.

• FIELDS TERMINATED BY is an optional parameter. It specifies the format of how the values are separated.

• ENCLOSED BY is an optional parameter. It specifies the format that each value is enclosed.

Note that the directory of MySQL might differ by operation systems and the tool that you used to install it. By default, MySQL stores data in the directory where it is intalled and creates a folder for each database.

You can go to the corresponding directory to check whether the file has been exported successfully.

The into outfile command might cause ERROR 1290. It happens because, by default, MySQL has specified a directory for the exported files. You can update the value of secure_file_priv to be an empty string'' to allow MySQL exporting data to any user-defined path.

ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement

Import files into MySQL

You can use syntax load data infile to import CSV or Excel files into MySQL. Before running this command, you need to first have a table in a database to save your data.

Step 1: Create a database

CREATE DATABASE new_db; USE new_db;

Step 2: Create a table

CREATE TABLE `users` ( `id` VARCHAR(255) NULL, `gender` VARCHAR(255) NULL, `employed` VARCHAR(255) NULL, `country` VARCHAR(255) NULL, `zipcode` VARCHAR(255) NULL );

You are recommended to not set a primary key and set all fields to accept NULL at this step to avoid possible issues that might be caused by the quality of the CSV file to be imported. Using VARCHAR for numeric values also helps to avoid unpredictable data format issues. After importing data, you can then validate and update the table schema.

Step 3: Import files to the newly created table

load data infile '/opt/homebrew/var/mysql/new_db/users.csv' into table users FIELDS TERMINATED BY ',' ENCLOSED BY '"';

• load data infile specifies the path and name of the imported file.

• FIELDS TERMINATED BY is the format of how the values are separated in the imported file.

• ENCLOSED BY is the format of how each value is enclosed.

After this operation, you can use the command select * from users; to check whether the data has been successfully imported into MySQL.

Data import/export within clicks using Openblocks

In Openblocks, you can import data via the File Upload component and present it in Tables directly. Openblocks also supports editing in tables so that you can update information in a more intuitive way and don't need to write any SQL query. If you'd like to download the data from a table, all you need is click the download icon at the left-bottom corner.

Openblocks is an open-source low-code platform, offering 50+ built-in UI components. It allows you to connect to most databases and any REST API within seconds, design custom components, leverage external JS libraries, and more. With full control over your resources, you can grant permissions to individual colleagues or teams, track activities, and deploy self-hosted servers.

Give it a try and feel free to reach out to our community on Discord if you encounter any issue or would like to share an idea.