CODEWITHSUNDEEP
mysqlsqlexcelstored-proceduresimport
Pavi
Pavi

Reputation: 345

Import Excel Data to Relational Tables of MySQL

My excel workbook having three worksheets. Each Worksheet contains data for 3 separate tables of my sql database.

Table1 : Client_master Columns : Id, Client_Name

Table2 : compaign_master Columns : Id, compaign_Name

Table3 : raw_data Columns: id, date, client_id,compaign_id,views,clicks.

By using below code, i inserted excel data into mysql table.

use analyticsdata;
DROP TABLE IF EXISTS excel_table;
CREATE temporary TABLE excel_table (
  id int,
  client_name VARCHAR(255)
) DEFAULT CHARSET utf8;

LOAD DATA LOCAL INFILE 'C:/Users/puttaraju.d/Documents/data.csv' 
INTO TABLE excel_table 
CHARACTER SET utf8
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' 
LINES TERMINATED BY '\n' 
IGNORE 1 LINES;

INSERT INTO client_master 
  SELECT id, client_name 
    FROM excel_table 
   ORDER BY id;

How can i read and insert data which is there in sheet 2 and sheet 3 to other tables.

Upvotes: 0

Views: 1754

Answers (1)

AnalystCave.com
AnalystCave.com

Reputation: 4984

In my opinion you have 3 options (2 require plugins):

  1. Install the Excel MySQL plugin http://www.mysql.com/why-mysql/windows/excel/

  2. Install the MySQL UDF plugin http://www.mysqludf.org/ write a shell script for transforming the Excel file to separate csv files (e.g. run an Excel macro) and run the script using the "sys_exec" SQL command

  3. Join all three tables into one CSV. Then upload the file to a temporary table (e.g. with varchar columns) and separate them on the column headers.

Upvotes: 1

Related Questions