Scenario : You have online website with MYSQL Database and you want to see the live data in excel sheet.
Solution :
- Create a MY SQL User account
- Give permission for Select query to the specific database
- Allow remote sql access permission to your ip address
- Download the .net connector for MYsql for Windows
- Configure EXCEL to consume data from Remote MySQL
Step 1: Login to CPANEL
Open MYSQL Databases
Step 2: Create new user
Step 3: Assign User to Database and give permission
Step 4 : Find Your Public IP Address
Step5 : Open Remote SQL and add your public ip address
Step 6 : Download and install MY SQL Connector for Windows
https://dev.mysql.com/downloads/connector/net/
or
https://dev.mysql.com/downloads/file/?id=492453
Step7 : Open MS Excel and add new query
Data -> New Query ->From database -> From MySQL Database
Step 8 :
- Enter Server address (Your server IP address – or Hostname )
- Enter Databse Name
- Enter the query to get the data
Step 9 : Enter the username and password
Step 10 : Click Save Close and Load
Step 11 : Now the data is available is MS Excel
Step 12 : To get the latest data from live please press the refresh button
Enjoy!