MariaDB Tutorial / Import and Export CSV with Correct Paths, Types, and Encoding

Import CSV by matching delimiters, quoting, line endings and encoding, validating raw staged values, then converting them into the destination types. LOAD DATA LOCAL INFILE reads a client-side file, whereas SELECT INTO OUTFILE writes on the server, so their path and privilege requirements differ.

Identify which machine reads or writes the file

Enable LOCAL only for a trusted server and consider the client files it can read and send. Do not broadly relax every global setting or filesystem permission just to suppress an import error. Use the client-path rules in Connect with the Command-Line Client and Run SQL Files and the column/value mapping in Insert One Row or Multiple Rows with INSERT when transferring staged rows into typed columns.

Check the MariaDB documentation for the syntax and conditions used here: load data infile, select into outfile.

Prepare a UTF-8 file and a raw-text staging table

Create a new products.csv under C:/mariadb-lab on the client machine, preserving any existing file. Save the following as UTF-8. The header names the three fields, and the first Korean product name, meaning large notebook, contains a comma and therefore needs quotes:

product_id,product_name,price
1,"노트, 대형",3500.00
2,"머그컵",8000.00
3,"볼펜",1500.00

Select shopcourse and verify csv45_stage and csv45_products do not already exist. Stage raw text first instead of trusting immediate numeric conversion:

USE shopcourse;
CREATE TABLE csv45_stage(raw_id TEXT,raw_name TEXT,raw_price TEXT)
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
CREATE TABLE csv45_products(product_id INT PRIMARY KEY,
 product_name VARCHAR(100) NOT NULL,price DECIMAL(10,2) NOT NULL CHECK(price>=0))
 ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Load the client file and inspect warnings before conversion

mariadb --protocol=tcp --host=127.0.0.1 --port=3306 --user=course_user --password --local-infile=1 --default-character-set=utf8mb4 shopcourse

LOAD DATA LOCAL INFILE 'C:/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;
SELECT * FROM csv45_stage;
SELECT * FROM csv45_stage
WHERE raw_id IS NULL OR raw_id NOT REGEXP '^[0-9]+$'
 OR raw_price IS NULL OR raw_price NOT REGEXP '^[0-9]+([.][0-9]{1,2})?$'
 OR raw_name IS NULL OR raw_name='';
SELECT CAST(raw_id AS UNSIGNED) AS normalized_id,COUNT(*)
FROM csv45_stage GROUP BY CAST(raw_id AS UNSIGNED) HAVING COUNT(*)>1;
-- After format checks pass, verify numeric ranges and name length
SELECT * FROM csv45_stage
WHERE CAST(raw_id AS DECIMAL(65,0))<1
 OR CAST(raw_id AS DECIMAL(65,0))>2147483647
 OR CAST(raw_price AS DECIMAL(65,2))>99999999.99
 OR CHAR_LENGTH(raw_name)>100;

If the server’s local_infile policy disallows the operation, review it with the administrator. LOCAL imports do not universally reject every malformed input merely because strict mode is enabled. Inspect warnings and the original strings.

The example splits lines on LF and removes a trailing CR from the final field, handling the simple common CRLF case as well. Complex embedded line breaks and escape conventions need agreement with the producing tool. Run the format checks first; only after those pass should numeric normalization, duplicate and range checks be trusted.

For an Ubuntu client, create a work directory in the terminal and save the UTF-8 products.csv there.

mkdir -p "$HOME/mariadb-lab"
printf '%s\n' "$HOME/mariadb-lab/products.csv"

Replace the LOCAL path with the actual absolute path printed above. This example assumes the client’s login name is learner.

LOAD DATA LOCAL INFILE '/home/learner/mariadb-lab/products.csv'
INTO TABLE csv45_stage CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n' IGNORE 1 LINES
(@id,@name,@price)
SET raw_id=@id,raw_name=@name,raw_price=TRIM(TRAILING '\r' FROM @price);
SHOW WARNINGS;

Run only the Windows or Ubuntu import appropriate to your client; loading both appends duplicate staging rows. An Ubuntu OUTFILE destination follows the separate server-side policy below.

Transfer only validated values to typed columns

Require empty results from the malformed-input and duplicate queries. IDs must fit positive signed INT, prices DECIMAL(10,2), and names at most 100 characters. Text IDs such as 01 and 1 can become equal after conversion, so check normalized uniqueness too. If validation fails, fix the source or policy before running the INSERT:

INSERT INTO csv45_products(product_id,product_name,price)
SELECT CAST(raw_id AS UNSIGNED),raw_name,CAST(raw_price AS DECIMAL(10,2))
FROM csv45_stage;
SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id;

There should be three products and the first name must retain its comma. The mug and pen use the other supplied Korean names. Repeating LOAD into the same staging table can append more rows; preserve source files and diagnostics before clearing a failed attempt.

Export a server-side CSV through an authorized account

Use an approved administrator or export account with FILE privileges. Check secure_file_priv, the server’s OS write access and a new destination filename. On a remote server, the C: path belongs to that server, not your client PC.

SELECT @@secure_file_priv;

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE 'C:/mariadb-lab/products-export.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

The output contains three CSV records and no header; an existing file is not overwritten. Add a header through an explicit export procedure if needed. Renaming ordinary tab-separated mariadb --batch output with a .csv extension does not turn it into CSV.

For an Ubuntu server, inspect SELECT @@secure_file_priv; and the MariaDB service account’s write permissions. If the approved directory is /var/lib/mysql-files/, a new server-side file can be written as below; it is not the client home directory.

SELECT product_id,product_name,price FROM csv45_products ORDER BY product_id
INTO OUTFILE '/var/lib/mysql-files/products-export-new.csv'
CHARACTER SET utf8mb4
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Substitute the actual approved directory and execute once on that server. Do not remove existing files or broaden FILE access simply to bypass an error.

Verify the round trip by fields rather than count alone

Compare commas, Unicode text, empty strings, NULL and line breaks as well as row counts. CSV tools do not all share a NULL convention, so define whether a blank field means empty text or missing data. This fixture deliberately uses three populated fields. Review whether the files must be retained separately, then remove only the practice tables.

DROP TABLE shopcourse.csv45_products;
DROP TABLE shopcourse.csv45_stage;