Complete Guide to Importing Excel Data into MySQL Using LOAD DATA INFILE

Data Import Method Overview

There are multiple approaches to import Excel data into MySQL databases, with the LOAD DATA INFILE command being an efficient method that doesn't require additional software. This approach is particularly suitable for batch data processing, leveraging MySQL's built-in capabilities for data migration tasks.

Excel to CSV Format Conversion

Since MySQL's LOAD DATA INFILE command primarily supports text format files, the first step involves converting Excel files to CSV format. In Excel, this can be done through the "File → Save As" function, selecting CSV format. During conversion, pay attention to the choice of data delimiter, typically using commas or tabs as field separators.

MySQL Table Structure Design

Before importing data, create the corresponding database table structure. Design MySQL table fields based on Excel data column definitions. For example, for a data table containing country, amount, and quantity information, create the following structure:

CREATE TABLE sales_data (
  id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
  country VARCHAR(255),
  amount INT,
  quantity DECIMAL(10,2)
);

LOAD DATA INFILE Command Detailed Explanation

LOAD DATA INFILE is a specialized command provided by MySQL for importing data from files. The basic syntax structure is as follows:

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

Field and Line Delimiter Configuration

In the LOAD DATA INFILE command, correctly configure field and line delimiters:

• FIELDS TERMINATED BY: Specifies field separator, CSV files typically use commas
• ENCLOSED BY: Specifies field enclosure character for handling fields containing delimiters
• LINES TERMINATED BY: Specifies line terminator, "\r\n" for Windows systems, "\n" for Unix/Linux
• IGNORE n ROWS: Skips the first n rows of the file, typically used to skip header rows

Special Handling for Tab-Delimited Data

For data files using tab delimiters, adjust the field separator configuration:

LOAD DATA INFILE '/path/to/tab_delimited_data.csv'
INTO TABLE sales_data
FIELDS TERMINATED BY '\t'
ENCLOSED BY ''
ESCAPED BY '\\'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS;

Data Validation and Error Handling

During data import, various issues may arise requiring appropriate validation and handling:

• Character Encoding Issues: Ensure CSV file character encoding matches MySQL database settings
• Data Type Matching: Verify compatibility between Excel data formats and MySQL table field definitions
• Special Character Escaping: Handle field contents containing quotes, line breaks, and other special characters
• Null Value Handling: Configure null value processing to avoid import errors

Post-Import Data Validation

After data import completion, perform integrity validation:

SELECT COUNT(*) as total_records FROM sales_data;
SELECT * FROM sales_data LIMIT 10;

Verify all data has been correctly imported by querying total record count and sampling data.

Performance Optimization Recommendations

For large-scale data imports, implement the following optimization measures:

• Temporarily disable indexes before import, rebuild after completion
• Adjust MySQL's max_allowed_packet parameter to accommodate large files
• Use LOCAL keyword to read files from client file system
• Process extremely large data files in batches

Alternative Solution Comparison

Besides the LOAD DATA INFILE method, other import options are available:

• Online Conversion Tools: Such as sqlizer.io, providing graphical interfaces but dependent on web services
• Data Converters: Like Mr. Data Converter, suitable for small batch data processing
• Programming Approaches: Using programming languages like Python to write custom import scripts
• Database Management Tools: Such as MySQL Workbench's graphical import functionality

Best Practices Summary

Based on practical project experience, summarize the following best practices:

• Always perform data import validation in test environment first
• Maintain consistency between Excel data and MySQL table structures
• Establish standardized data cleaning and validation processes
• Maintain detailed import logs for issue tracking
• Regularly backup original data files