Search⌘ K
AI Features
Home
Courses
SQL Interview Preparation – Advanced Level

Answer: Import CSV Data

Understand how to perform bulk CSV data imports in MySQL using the LOAD DATA INFILE statement. Learn about handling field delimiters, quoted text, line termination, and skipping headers to safely and efficiently load data. This lesson also covers verifying imported data and explores alternate import methods for validation and transformation.

We'll cover the following...

Solution

The solution is given below:

MySQL 8.0
LOAD DATA INFILE '/var/lib/mysql-files/supplier_metrics.csv'
INTO TABLE Suppliers
FIELDS TERMINATED BY ','
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 ROWS
(SupplierName, Email, Phone, Address, OnTimeDeliveryRate, AvgLeadTime);
/* Verification */
SELECT   SupplierID, SupplierName, OnTimeDeliveryRate, AvgLeadTime
FROM     Suppliers
WHERE    SupplierName IN ('FastShip Ltd', 'PrimeLogix', 'EcoTransit')
ORDER BY SupplierID;

Explanation

The explanation of the solution code is given below:

  • Line 1: The LOAD DATA INFILE statement starts the bulk import process by specifying the absolute path of the CSV file that MySQL is allowed to read from.

  • Line 2: The INTO TABLE clause identifies Suppliers as the target table for the imported data.

  • Line 3: FIELDS TERMINATED BY ',' specifies that columns in the CSV file are separated by commas.

  • Line 4: OPTIONALLY ENCLOSED BY '"' allows text fields to be wrapped in double quotes, which is common in CSV files that contain commas in values.

  • Line 5: LINES TERMINATED BY '\n' indicates that each row in the file ends with a newline character.

  • ...