How to Import and Export with MySQL for Excel Utility

MySQL for Excel is a very nice tool to export or import between MySQL and Excel.

Many people will be facing problems in converting the Excel data to MySQL Table data. This week I came across this tool and found helpful for every one. It is simply superb.

The steps to export your Excel data to MySQL table is below,

For export as a New table data,

1. Open the excel sheet from where you want to export data


2. Go to Data tab, then click MySQL for Excel


3. Make sure database server is running. Click on new connection down the page and fill all the details


4. Double click on the connection under Local/Remote connections to open the connection and enter password.This lists all the schema under the connection


5. Double click on the schema to open


6. Now select the value to export


7. Now the export option will be enabled on the right side


8. Click on export Excel data to new table


9. A pop opens where it asks for the table name and primary keys


10. Just follow those steps and click on export.


11. Table is created under the schema, with the table name you entered.

You may feel this is very easy. Now the interesting thing comes. This is what i required and it took me one day to figure it out.
What happens if you want one column of the excel data to be exported to one Column of the table??. Here the most important thing is you have to follow the synchronization. Even if you are exporting one column the row should be in sync. For example Salary of Siva should match Salary of Siva in table. You should mesh up the data.

The steps are below.

1. Follow till the 5th step above.


2. Click the table(example test) to which you want to export the data from excel sheet.


3. Click on Edit MySQL data


4. This opens the table in a separate sheet with the name of the table(test). Suppose this table has 2 columns named 'A' and 'B' .


5. There is excel sheet named newtestdata.xls, and it has 2 columns named 'A' and 'B' .


6. Now we wanted to move the  newtestdata.xls 's 'B' column to test table.


7. First make sure, newtestdata.xls's 'B' column and test sheet's 'B' columns are following same order.


8. then check whether the sheet is editable. If not,


9. press ALT + f11


10. double click the sheet where the table to be edited is shown. (Test sheet)


11. copy the below code on the white space
    

    Sub PasswordBreaker()
'Breaks worksheet password protection.
Dim i As Integer, j As Integer, k As Integer
Dim l As Integer, m As Integer, n As Integer
Dim i1 As Integer, i2 As Integer, i3 As Integer
Dim i4 As Integer, i5 As Integer, i6 As Integer
On Error Resume Next
For i = 65 To 66: For j = 65 To 66: For k = 65 To 66
For l = 65 To 66: For m = 65 To 66: For i1 = 65 To 66
For i2 = 65 To 66: For i3 = 65 To 66: For i4 = 65 To 66
For i5 = 65 To 66: For i6 = 65 To 66: For n = 32 To 126
ActiveSheet.Unprotect Chr(i) & Chr(j) & Chr(k) & _
Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & Chr(i3) & _
Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
If ActiveSheet.ProtectContents = False Then
MsgBox "One usable password is " & Chr(i) & Chr(j) & _
Chr(k) & Chr(l) & Chr(m) & Chr(i1) & Chr(i2) & _
Chr(i3) & Chr(i4) & Chr(i5) & Chr(i6) & Chr(n)
Exit Sub
End If
Next: Next: Next: Next: Next: Next
Next: Next: Next: Next: Next: Next
End Sub

    
    


12. press f5


13. now the column will be editable on sheet test.


14. copy the data inside column 'B'.


15. press commit changes.

Thats all. The changes are now committed to database.