How to import CSV files to MySQL or MariaDB table

CSV (Comma Separated Values) stores tabular data in a simple file format. It is often used for data transfer between different systems and can be easily opened in spreadsheet programs such as Microsoft Excel or Google Sheets. However, SQL databases such as MySQL and MariaDB are more efficient and reliable for data storage and management.

SQL databases provide better organization and management of data compared to CSV files. With SQL, data can be easily sorted, filtered, and queried, and it supports relationships between tables, making it easier to work with complex data sets. Additionally, SQL databases have robust security features and can handle concurrent access by multiple users, making them ideal for enterprise-level data storage.

Importing a CSV file into MySQL or MariaDB requires loading the data into a database table. First, you must create a table with the same structure as the CSV file. Then, the LOAD DATA INFILE command can be used to import the data, which requires the path to the CSV file and mapping the file columns to the table columns.

1. Make sure that you have a appropriate access to the table that you want to import the data to.
2. Prepare the CSV file that you want to import, making sure that it has a header row that specifies the column names.
3. Log in to the MySQL or MariaDB server using the command-line interface or a graphical tool such as phpMyAdmin.
4. Create a new table to hold the data by running a CREATE TABLE statement that specifies the column names and data types that match the CSV file.
5. Make sure that the columns in the CREATE TABLE statement match the column names in the CSV file header row.
6. Use the LOAD DATA INFILE statement to import the data from the CSV file into the new table. Make sure to specify the path to the CSV file, the name of the table, and the column delimiter (usually a comma). Here is an example of a LOAD DATA INFILE statement.

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

7. The FIELDS TERMINATED BY clause specifies the column delimiter, which is a comma in this example.
8. The ENCLOSED BY clause specifies the character used to enclose the values, which is a double-quote in this example.
9. The LINES TERMINATED BY clause specifies the line delimiter, which is a newline character in this example.
10. The IGNORE 1 ROWS clause skips the first row of the CSV file, which is assumed to be the header row.
11. Once the LOAD DATA INFILE statement has finished executing, you should see a message indicating how many rows were affected.
12. Verify that the data has been successfully imported by querying the table using a SELECT statement.