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.

Steps to import CSV data into 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

    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.

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.

Discuss the article:

Comment anonymously. Login not required.