How to Import JSON, XML, CSV, and Text Files into MySQL Efficiently

Importing external data into a database is a common ETL task; this guide focuses on the Load (L) phase for MySQL.

The article covers how to import data in several formats (JSON, Text, XML, CSV) into MySQL.

1. Import Text/CSV Files

Assume the text or CSV file is properly formatted, e.g., fields separated by a tab (\t). First design a matching table schema.

CREATE TABLE sometable(id INT, name VARCHAR(255), balance DECIMAL(8,4));

Then load the file:

LOAD DATA LOCAL INFILE 'your/file/path.csv' INTO TABLE sometable FIELDS TERMINATED BY '\t' [ENCLOSED BY '"'] LINES TERMINATED BY '\n' (id, name, balance);

Enable the local-infile option in MySQL, either by setting local-infile=1 in my.cnf or starting the client with --local-infile.

local-infile=1

mysql --local-infile -uroot -pyourpwd yourdbname

Alternatively, the official mysqlimport utility can be used.

2. Import XML Files

The approach depends on the XML structure. For a well‑formed XML like:

<?xml version="1.0"?> <row> <field name="id">1</field> <field name="name">Free</field> <field name="balance">2333.3333</field> </row> ...

MySQL’s LOAD XML can import directly, but mapping XML fields to different column names requires a stored procedure (MySQL 5.0.7+ cannot run LOAD XML INFILE inside a procedure). Use Load_File() and ExtractValue() functions.

Example procedure:

DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `import_some_xml`(path VARCHAR(255), node VARCHAR(255)) BEGIN DECLARE xml_content TEXT; DECLARE v_row_index INT UNSIGNED DEFAULT 0; DECLARE v_row_count INT UNSIGNED; DECLARE v_xpath_row VARCHAR(255); SET xml_content = LOAD_FILE(path); SET v_row_count = EXTRACTVALUE(xml_content, CONCAT('count(', node, ')')); WHILE v_row_index < v_row_count DO SET v_row_index = v_row_index + 1; SET v_xpath_row = CONCAT(node, '[', v_row_index, ']/@*'); INSERT INTO applicants VALUES ( EXTRACTVALUE(xml_content, CONCAT(v_xpath_row, '[1]')), EXTRACTVALUE(xml_content, CONCAT(v_xpath_row, '[2]')), EXTRACTVALUE(xml_content, CONCAT(v_xpath_row, '[3]')) ); END WHILE; END$$ DELIMITER ;

Call the procedure with:

CALL import_some_xml('your/xml/file.xml', '/some_list/someone');

3. Import JSON Files

JSON is increasingly common. When each line contains a single JSON object, it can be processed line‑by‑line.

Method 1: Use the common‑schema library’s extract_json_value() function.

CREATE FUNCTION extract_json_value(json_text TEXT CHARSET utf8, xpath TEXT CHARSET utf8) RETURNS TEXT CHARSET utf8 ...

Example query:

SELECT common_schema.extract_json_value(f.event_data,'/name') AS name, common_schema.extract_json_value(f.event_data,'/gender') AS gender, SUM(f.event_count) AS event_count FROM json_event_fact f GROUP BY name, gender;

Method 2: Use the third‑party mysqljsonimport tool (written in C, depends on Jansson and MySQL client libraries). Installation steps:

$ wget http://sourceforge.net/projects/mysqljson/files/myjsonimport_1.6/mysqljsonimport-1.6.tar.gz $ tar xvfz mysqljsonimport-1.6.tar.gz $ cd mysqljsonimport-1.6 $ ./configure --with-mysql=/path/to/mysql $ make $ make check $ sudo make install

Import command:

$ ./mysqljsonimport --database test --table tablename jsonfilename

4. Using MySQL Workbench

MySQL Workbench provides a graphical wizard for importing CSV and JSON files, useful for users unfamiliar with the command line.

Summary

The guide presented concrete techniques for loading JSON, Text, XML, and CSV files into MySQL, discussed necessary configuration (e.g., enabling local‑infile) and security notes, and pointed out that large‑scale import scenarios may require additional considerations such as sharding.