View Cpanel MySQL data from Excel Sheet

View Cpanel MySQL data from Excel Sheet

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!