What do I need to extract the historical data from my esync database? I would like to export it to an excel spreadsheet. Also is there a way to make this happen automatically?
Apologies for the delay here.
I am trying to prepare something that will help quite a bit, I will reply to this thread tomorrow with the needed information.
There are a few ways you can accomplish this with varying levels of complexity. The easiest way I have found to achieve this is actually via PowerQuery in Excel. PowerQuery is integrated directly into Excel 2016 though it can be installed easy enough into earlier versions of Excel.
Prerequisites:
- Power Query
- Native in Excel 2016
- Excel version < 2016 Add-In - Download
Steps:
- To begin we need to open Excel.
- Select the Data tab from the ribbon.
- Once you have selected the data tab, select the icon to Get Data from the far left.
- After selecting Get Data you will then select From Database -> From MySQL Database.
- You will get a popup where you need to enter the Server and Database information.
- Server - Your eSync server IP address or domain name
- Database - eSync (this does not change, you must use eSync)
- We will not use any advanced options though you can if you would like to use special SQL queries.
6. After selecting OK if everything is working as expected you should see the Navigator window. Here you can select the table you are most interested in. For most people this is going to be the esync_tagshistory table.
- Finally select the Load button from the bottom. If everything worked as expected you should see Excel populate with all of your data!
Additional Information:
Q. How can I filter the data?
A. It is easy enough to filter the data by selecting the icon from the headers in the Excel sheet. For example, if you select the arrow by _date you can select to show data from between two dates.
Q. Can I submit my own custom query to only load the data I need after importation?
A. Yes you can. The easiest way to do this is to simply update the data source inside of the Get Data menu.
- Simply select Get Data --> Data Source Setting
- In the next window highlight your source and select Change Source
- Finally under advanced options enter your new query. Ensure to use valid SQL query syntaxing. Then just refresh the data.
I hope this helps, please let me know if you run into any errors!
1 Like
This is the error I am getting now. Details: “MySQL: Unable to connect to any of the specified MySQL hosts.” After I put in the ip address and esync it asks me for a windows and database password. The esync is sitting on a cloud server and I put the cloud server login and password in windows pop up. Then I put the login and password for esync in the database field. What am I doing wrong?
You actually need to set the type as database authentication. When you are connecting you should see the following popup for authentication:
From the left instead of Windows select Database, this will use MySQL DB authentication instead of Windows authentication. Again you will use the eSyncExport/eSyncPwd credentials from above.
**A final note, you do want to ensure that you have allowed port 3306 (MySQL) through your cloud server firewall. **
I verified that we are allowing traffic on port 3306. I tried using the database authentication and still getting the same error message. Where do I find the esync export/esync password? I tried both the initial and server login.
Also this is a remote connection to a cloud server with windows server 2016 running on it.
The credentials are:
Username: eSyncExport
Password: eSyncPwd
You have tried those correct?
1 Like
Just did. It tries to connect and then the same error message comes up.
To wrap up on this thread, the above solution worked however the data source address was actually incorrect. Initially I didn’t realize Excel was running on the same server as the eSync server, if that is the case you can use LOCALHOST as the server address as opposed to the public IP of the server.
1 Like