Most people now store their raw data into their excel worksheet and use for their everyday business transaction. As a developer generate SQL insert statement in excel worksheet to run in MySql is what they have to do for data integration. This method help you to save your time and efforts of technical difficulties or re-entry your old data into the new system.
This article is produce to solve problem and support for database administrator and programmer that has problem with data importing that user input in Ms Excel into there database such as MySql Database.
The most frequency asked question by database administrator and programmer about importing data from excel to MySql Database is:
How can import data from Ms Excel to MySql Database?
How to generate script to import data from Ms Excel to PhpMyAdmin MySql database?
Generate SQL Statement from Ms excel data for import to MySql Database by PhpMyAdmin?
There was simple steps to import your data from Ms Excel to your MySql Database.
Step 1: Prepare your Ms Excel column to meet your table column
The first thing you have to prepare is the column name on your database must same as column name and number in your excel file. It could help you to match the column and it’s data to be import.
Supposed that you have table name tbl_user that contains field such as user_id, user_firstname, user_lastname, user_gender, user_dob, user_email and user_phone.
You may have other table named tbl_product that has field name as pro_id, pro_name, pro_qty and pro_unitPrice
Step 2: Login to your MySql Database from PhpMyAdmin
Login to you MySql Database via PhpMyAdmin interface with your username and password
Step 3: Prepare your MySql Tables Columns to be import
This step is the process of creating database or creating table in your existing database for import data from excel in the step 1 into your database.
In you table would exists one first field that is the primary key for your record named id
To set your first field as primary key, you may checked on A.I it mean Auto Incremental and set Index to PRIMARY to make sure that field is automatically insert incremental number as primary key.
Create another table named tbl_product and set field as required for your project same as your excel columns and the first field to be PRIMARY KEY and Auto Incremental.
Now two table was created as tbl_user, and tbl_product
Step 4: Understand Ms Excel Concatenate String Formulas
In this step you need to understand about string concatenate in excel formulas that could help you to generate SQL statement string.
The function is to concatenate string as bellow picture.
Step 5: Generate Sql Statement in Ms Excel
In this step you can see we gonna run SQL statement to insert data into MySQL Database so we need to concatenate string as bellow picture.
You may need to generate the amount of columns to match the real field in your table tbl_user but make sure your first column that is primary key is set to null and it will automatically generate primary key and incremental number.
Now concatenate our second field by bellow method “&C5&”
The bellow formulas will produce string as this picture.
Now concatenate all of your require field that match to the table field.
Press Enter key, to see the string result bellow as we been generate single SQL Statement for insert data into table tbl_user.
For multiple SQL Statement you need to add semi column (;) at the end of your statement so it will run insert data line by line.
You may do the same method, to produce SQL Statement on other sheets that you wish to import that data into MySQL database.
Step 6: Execute your Sql Statement in MySql Database using PhpMyAdminn
To execute your SQL Statement that generated from Ms Excel in the bellow step, you may need to copy your all statement in Ms Excel and paste into your MySQL Database or using PhpMyAdmin as bellow practice.
You may click on Go button to execute your SQL Statement.
You can do same practice on your other excel data by copy and paste into PhpMyAdmin and click on button Go .
Step 7: Verify result after data imported
Now your import process from Ms Excel data is complete and you can verify your data in MySQL table tbl_user to see the inside data is correct way.
Finally your data is imported successfully into your database and your problem is solved here.
