To import a CSV file into a MySQL table, follow these steps:
1. Prepare Your CSV File
Ensure the CSV file is formatted correctly and matches the structure of the MySQL table (same column count and data types).
2. Create the Table in MySQL
Create a table in MySQL that corresponds to the CSV file’s structure:
sql
Copy code
CREATE TABLE your_table_name (
column1_name column1_data_type,
column2_name column2_data_type,
…
);
3. Use the LOAD DATA INFILE Command
You can use MySQL’s LOAD DATA INFILE to import the CSV file. Here’s the syntax:
sql
Copy code
LOAD DATA INFILE ‘/path/to/your_file.csv’
INTO TABLE your_table_name
FIELDS TERMINATED BY ‘,’
ENCLOSED BY ‘”‘
LINES TERMINATED BY ‘\n’
IGNORE 1 ROWS;
FIELDS TERMINATED BY ‘,’: Specifies the delimiter (e.g., comma for CSV files).
ENCLOSED BY ‘”‘: Specifies how text fields are enclosed.
LINES TERMINATED BY ‘\n’: Defines the line break.
IGNORE 1 ROWS: Skips the header row.
4. Ensure Permissions
If you encounter errors, ensure MySQL has access to the file by checking:
The file is located in a directory MySQL can access.
The MySQL user has the FILE privilege.
Alternatively, use the LOCAL keyword:
sql
Copy code
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;
5. Using MySQL Workbench (Optional)
Open MySQL Workbench.
Navigate to your database.
Use the “Table Data Import Wizard” in the context menu for the table to load the CSV interactively.
6. Alternative: Python Script
Use Python and libraries like pandas or mysql-connector-python for flexibility:
python
Copy code
import pandas as pd
import mysql.connector
# Read CSV
df = pd.read_csv(‘your_file.csv’)
# Connect to MySQL
conn = mysql.connector.connect(
host=’your_host’,
user=’your_user’,
password=’your_password’,
database=’your_database’
)
cursor = conn.cursor()
# Insert rows into the table
for _, row in df.iterrows():
cursor.execute(‘INSERT INTO your_table_name VALUES (%s, %s, …)’, tuple(row))
conn.commit()
conn.close()