Step 1:
Create a table to which you want to import data.
Example: I have created a sample table for importing data.
CREATE TABLE `sample` (
`s_id` INT NOT NULL AUTO_INCREMENT,
`s_name` VARCHAR(255) NULL,
`s_address` VARCHAR(255) NULL,
`created_at` DATETIME NULL,
PRIMARY KEY (`s_id`)
) ENGINE=InnoDB; |
Step 2:
Create a CSV file matching the table fields for importing data.
Example: Please refer to this Google Sheet: Sample.csv
Step 3:
Navigate to the following directory: /var/lib/mysql-files |
You cannot navigate directly to this path. Instead, go to /var/lib and find mysql-file. Double-click and authenticate using the root user password.
Step 4:
After navigating to /var/lib/mysql-files, copy your sample.csv file to this path. |
Step 5:
Login to MySQL using the terminal.
Command: sudo mysql -u {database-username} -p |
For example:
Command: sudo mysql -u root -p // Here, 'root' is my MySQL username |
Step 6:
After logging into MySQL in the terminal, select the database.
Command: USE {database-name} |
For example:
USE test; // Here, 'test' is my database name |
Step 7:
Execute the query:
LOAD DATA INFILE '/var/lib/mysql-files/{your-csv-name}'
INTO TABLE {your-table-name}
FIELDS TERMINATED BY ','
ENCLOSED BY '"'
LINES TERMINATED BY '\n'
IGNORE 1 LINES; |
For example:
LOAD DATA INFILE '/var/lib/mysql-files/Sample.csv' INTO TABLE sample FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 LINES; |
In this example, Sample.csv is the CSV file, and sample is the table name.
