Import data from a CSV file into MySQL

Step 1: Copy the CSV file to the host running the MySQL Server

 $ scp file.csv user@host:

Step 2: Create a table to receive the information from the CSV file.

mysql> create table import_data 
    -> (
    ->   id int,
    ->   field_1 varchar(50),
    ->   field_2 varchar(50)
    -> );

The field names should match the names in the csv header row. After creating the table, remove the header row from the csv file (first line of the file).

Step 3: Load the data from the CSV file into the new table

mysql> load data local infile '/home/user/file.csv' into table import_data 
    -> fields terminated by ','
    -> enclosed by '"'
    -> lines terminated by '\n'
    -> (id, field_1, field_2);

Step 4: Create a stored procedure to insert/update the existing tables with the data that has been populated in the new table.

DELIMITER $$  

DROP PROCEDURE IF EXISTS  c_import_data $$

CREATE PROCEDURE `c_import_data` ()  
BEGIN  
 DECLARE DONE BOOLEAN DEFAULT FALSE; 
 DECLARE RESULT INT(11) DEFAULT 0; # Count the rows changed 

 DECLARE import_id INT(11); 
 DECLARE import_field_1 VARCHAR(50);
     DECLARE import_field_2 VARCHAR(50);

 DECLARE INFO CURSOR FOR  

 	select id, field_1, field_2
 	from import_data;

 DECLARE CONTINUE HANDLER FOR NOT FOUND SET DONE = TRUE;  

 OPEN INFO;

 read_loop: LOOP

 	FETCH INFO INTO import_id, import_field_1, import_field_2; 

 	IF DONE THEN
 		LEAVE read_loop;
 	END IF;

 	update existing_table_1 set field_1=import_field_1 WHERE existing_id = import_id;
 	insert into existing_table_2 (field_1, field_2) VALUES(import_field_1, import_field_2);

 	SET RESULT = RESULT+1;

 END LOOP; 

 CLOSE INFO;

 select RESULT;
END $$ 

DELIMITER ;

Step 5: Execute/Call the stored procedure

mysql> call c_import_data();

Step 6: Verify that the imported data is present in the existing tables;

Run select queries against the affected tables to ensure that you successfully imported the data.

Step 7: Drop the import_data table from the database.

mysql> drop table import_data;