How to import CSV files into MySQL or MariaDB

Importing a CSV file into a database is a common task in database management. The CSV format is widely used for transferring data between systems. It is essential for tasks such as data migration or integration. MySQL and MariaDB provide a built-in command to simplify the process of importing CSV files into a table.

Both MySQL and MariaDB offer the `LOAD DATA INFILE` command to efficiently load data from a CSV into a database table. This method allows you to quickly and accurately import large datasets. It eliminates the need for manual data entry or repeated `INSERT` commands. By using this command, you can directly import data in bulk into an existing table.

Before importing, ensure the structure of the CSV file matches the table schema. This includes aligning column order and data types. Managing the correct permissions, such as the `local_infile` parameter, is important to enable the file import while ensuring database security. These settings must be verified before and after the import process.

1. Log in to the database instance.

   $ mysql -u root -p
   Enter password:

2. Select the target database.

   USE your_database_name;

3. Set the local_infile parameter to ON.

   SET GLOBAL local_infile=1;

   Make sure you have the necessary permissions to use local_infile. Contact your database administrator if needed.

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

   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;

   Ensure the columns in the CSV file match the table structure, both in type and order. If the CSV has headers, the IGNORE 1 ROWS option prevents them from being imported.

5. Verify the imported data.

   SELECT * FROM your_table_name LIMIT 5;

6. Set local_infile back to OFF if using MariaDB.

   SET GLOBAL local_infile=0;

   This is a security measure to prevent unintended file imports in MariaDB.

7. Exit the database.

   EXIT;