Importing CSV Files into MySQL Tables

Importing data from CSV files into MySQL tables is a common task in database management. In this tutorial, we will cover the basics of importing CSV files into MySQL tables using various methods.

Introduction to Importing CSV Files

CSV (Comma Separated Values) files are plain text files that contain tabular data, with each row representing a single record and each column separated by a comma. MySQL provides several ways to import CSV files into its tables, including the LOAD DATA INFILE statement and the mysqlimport command.

Using the LOAD DATA INFILE Statement

The LOAD DATA INFILE statement is used to load data from a file into a MySQL table. The basic syntax of this statement is as follows:

LOAD DATA INFILE 'file_name'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n';

Here, file_name is the path to the CSV file, and table_name is the name of the MySQL table into which you want to load the data. The FIELDS TERMINATED BY clause specifies the character that separates each field in the CSV file (in this case, a comma). The ENCLOSED BY clause specifies the character that encloses each field (in this case, a double quote). Finally, the LINES TERMINATED BY clause specifies the character that terminates each line in the CSV file (in this case, a newline character).

Specifying Column Names

When importing data from a CSV file into a MySQL table, you can specify the column names using the following syntax:

LOAD DATA INFILE 'file_name'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(column1, column2, ...);

Here, column1, column2, etc. are the names of the columns in your MySQL table.

Ignoring Header Rows

If your CSV file has a header row that you want to ignore during import, you can use the IGNORE 1 ROWS clause:

LOAD DATA INFILE 'file_name'
INTO TABLE table_name
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

This will skip the first row in your CSV file and import only the data rows.

Using the mysqlimport Command

The mysqlimport command is a utility that allows you to load data from a file into a MySQL table. The basic syntax of this command is as follows:

mysqlimport --ignore-lines=1 \
            --fields-terminated-by=, \
            --local -u root \
            -p Database \
             TableName.csv

Here, --ignore-lines=1 specifies that the first row in the CSV file should be ignored. The --fields-terminated-by=, option specifies that each field is separated by a comma. The --local option tells mysqlimport to read the file from the local machine. Finally, -u root -p Database TableName.csv specifies the MySQL username, password, database name, and CSV file name.

Importing CSV Files using phpMyAdmin

phpMyAdmin is a web-based interface for managing MySQL databases. You can also use it to import CSV files into your MySQL tables. To do this, follow these steps:

1. Prepare your CSV file by ensuring that the fields are in the same order as the columns in your MySQL table.
2. Remove any header row from your CSV file.
3. Log in to phpMyAdmin and select the database you want to import data into.
4. Click on the "Import" button at the top of the page.
5. Browse to your CSV file and select it.
6. Choose the "CSV using LOAD DATA" option.
7. Enter a comma (,) in the "Fields terminated by" field.
8. Enter the column names in the same order as they appear in your MySQL table.
9. Click the "Go" button to import the data.

Conclusion

Importing CSV files into MySQL tables is a straightforward process that can be accomplished using various methods, including the LOAD DATA INFILE statement and the mysqlimport command. By following the steps outlined in this tutorial, you should be able to successfully import your CSV files into your MySQL tables.