How to import CSV files to MySQL or MariaDB table

Importing data from a CSV (Comma-Separated Values) file into a database is a common task for developers, administrators, and data engineers. The CSV format allows for the easy transfer of data between different systems, making it a go-to solution for data backup, migration, or even analytics tasks.

Both MySQL and MariaDB are popular open-source relational database management systems (RDBMS) widely used in web development and other applications. These databases have a built-in utility, LOAD DATA INFILE, which provides the capability to bulk import data from a text file or a CSV directly into a database table.

While the basic steps are straightforward, certain considerations like field terminators, file paths, and permissions might require attention. Here's a concise guide to get your CSV data into a MySQL or MariaDB table.

1. Login to your database instance.

   $ mysql -u root -p
   Enter password:

2. Use the desired database.

   USE your_database_name;

3. Set the global local_infile parameter to ON.

   SET GLOBAL local_infile=1;

   Make sure you have permissions to use local_infile in MySQL or MariaDB. Contact your database administrator if you face issues.

4. Use the LOAD DATA INFILE command to import the CSV.

   LOAD DATA LOCAL INFILE 'path/to/your/file.csv'
   INTO TABLE your_table_name
   FIELDS TERMINATED BY ','
   ENCLOSED BY '"'
   LINES TERMINATED BY '\n'
   IGNORE 1 ROWS;

   Make sure the CSV columns match the table columns in both type and order. Also, if your CSV has headers, the IGNORE 1 ROWS ensures they're not imported into the table.

5. Verify the data has been imported.

   SELECT * FROM your_table_name LIMIT 5;

6. If using MariaDB, after importing, set local_infile back to 0.

   SET GLOBAL local_infile=0;

   This is a security measure to prevent any unintended file imports.

7. Exit the database client.

   EXIT;

With these steps, your CSV data should now reside within your desired table in either MySQL or MariaDB. Remember to double-check the data types, structure, and integrity to ensure everything has been imported accurately.