Step by step guide
1. Install MySQL for Excel → https://downloads.mysql.com/archives/excel/
2. Select Data > Get Data > From Database > From MySQL Database.
Note: If receive the message “This connector requires one or more additional components to be installed before it can be used.”, you need to download the appropriate ODBC Driver for MySQL to your Windows device from here: https://dev.mysql.com/downloads/connector/odbc/
Tip: Make sure to install the proper version of ODBC matching your machine. Excel might have a different version compared with your system. For example, you can have a 64-bit operating system and excel on 32-bits. To check this, go to excel → File → Account → About Excel. In this case install the 32-bit version of ODBC driver.
3. Next, navigate to Data → MySQL for Excel. On the right side, select New Connection as shown on the left:
4. Write the credentials in the new window as shown in the figure below and press OK. The example uses the eosio_activity database.
user: eosio_token_accounting_ro
password: eosio_token_accounting_ro
database: eosio_token_accounting
host: eu01.pub.bdata.one
port: 3303
The new connection will appear under the Remote Connections section:
5. By double clicking, all the tables from the database will be shown.
6. Now open ODBC Data Source Administrator and:
- Press Add.. to add a new data source
- Select MySQL ODBC 8.0 Unicode Driver
- The next window should appear
The example uses the eosio_token_accounting database.
user: eosio_token_accounting_ro
password: eosio_token_accounting_ro
database eosio_token_accounting
host: eu01.pub.bdata.one
port: 3303
7. Now navigate to Excel and go to Data → Get Data → From Other Sources → From ODBC
In the new window that appears, select Data Source Name that we just created (eosio_token_accounting_ro) and press advance options.
From here, you can customize your SQL queries according to your needs.
For example:
Examples of queries:
SELECT block_time, trx_id, delta/1e4 as _delta, balance/1e4 as _balance, other_party, CONVERT(memo using utf8)
FROM eos_TRANSFERS
WHERE account_name = ‘cc32dninexxx’ and contract=’eosio.token’ ORDER BY seq
SELECT account_name, block_time
FROM eos_TRANSFERS
WHERE account_name = ‘xx’ and block_time >=’2019–01–01' ORDER BY seq
In the next window, select Default or Custom for the login credentials and press Connect.
A summary of the data will be shown.
8. Press Load to visualize the complete data in excel.
Written by Alexandru Gherghelas for Zaisan.
About us
Blockchain System Integrator zaisan.io
Founded by EOSAmsterdam, EOSDublin, Cryptolions, EOSBarcelona
You can find us here:
Website|Twitter|LinkedIn
Tailor-made GDPR supporting enterprise blockchain platform: Europechain. Managed and governed by experienced validators registered in the European Union.
