Excel is great tools when it comes to working with data easily and efficiently. Today I’m going to show you a very simple tweak to upload data from Excel to Mysql.
When we want to insert data in MySQL then there’s a simple and reliable process is export your data from Excel to CSV and format that CSV according to your table and import that CSV into your MySQL DB. Although, In that process, sometimes I felt in a situation where my uploading interrupts due to inconsistency in data in a particular column. Also, you have to arrange and format your columns in CSV before importing them. I feel these are all tricky and lengthy for uploading a small amount of data means thousand, two thousand data in one go. And using CSV you can only able to insert data not updating data.
So here’s an another tweak of Excel by using that you can easily update or insert data in MySQL.
This tweak was I learnt from my senior Joshi Ji.
Basically, we are going to use CONCATENATE formula of Excel.
First, I’m going to show you how to update data in MySQL using Excel.
Suppose you have an excel sheet containing employee details in below format.
Now, you have to update their role in the table of MySQL with the given role in excel sheet.
To do that, We first make the update query. So in the employee table, the update query will be:
update employees set role = 'admin' where id = 1;
This is the update query for the first employee. Now we will put that query in CONCATENATE formula in the next column of the role column.
Now, we have to replace the value of role and id with the cell address of role and id so that when we drag the formula in the below rows then the values changed according to their rows.
So, In the above scenario, we have the below part of the query that we will join using CONCATENATE formula.
- “update employees set role = ‘”
- “‘ where id = “
when we join them in CONCATENATE formula that will look like
=CONCATENATE("update employees set role = '",E2,"' where id = ",A2,";")
Note: we have to keep the single inverted quotations of the value of role as it’s a varchar/string datatype and we keep that at the end of 1st part and the beginning of 3rd part
To make that concatenation easy, Follow the below steps:
- Put your entire query as it is in CONCATENATE formula
- Now select only the value part of the query without quotation
- Replace that part with “,<cell address>,” In the current scenario it will be “,E2,” (with double inverted quotation)
- Repeat above step for both values of SET and WHERE part of query
- Make sure that query should generate with semicolon at the end
That’s all, now drag the formula in below cells that you want to update.
Now copy all the cells containing the formula and paste it in MySQL editor. So in MySQL query editor, your query will be pasted not the CONCATENATE formula as query window is a simple text editor.
Similarly, do the same steps for insert query. Use insert query in formula and replace data part with desired cell address.
Point to be noted:
- When update/insert data of varchar/string type make sure that your data in excel should not contain any special character or single inverted quotation if it’s, then put (\) backslash before single inverted quotation or before the special character
- In that process, each query treats as individual query due to the (;) semicolon at last of each query so when executing these queries in MySQL editor the uploading process doesn’t interrupt if any query has any error.
- If you have any issue by following above steps just contact me by the comment on this post or by other communications. You can find my contact details on my website.