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.
$ mysql -u root -p Enter password:
USE your_database_name;
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.
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.
SELECT * FROM your_table_name LIMIT 5;
SET GLOBAL local_infile=0;
This is a security measure to prevent any unintended file imports.
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.
Comment anonymously. Login not required.