User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Retrieving MySQL data from Windows Excel

Thu May 30, 2019 4:41 am

I have a Raspberry Pi running Stretch and MySQL/MariaDB. It is monitoring some weather sensors and storing the data to a MariaDB database.

From my Windows 10 laptop on the same home network (Intranet) I'd like to pull some of the data directly using Excel and its Data Access functions. Basically I can not figure out how to setup the " Connection" string. My Pi is at 192.168.1.150. Its network name is PiV3Monitor. I can SSH to it using either of those.

For access to MariaDB do I need to be using a special port?

Any help would be appreciated.....RDK

User avatar
rpdom
Posts: 14473
Joined: Sun May 06, 2012 5:17 am
Location: Chelmsford, Essex, UK

Re: Retrieving MySQL data from Windows Excel

Thu May 30, 2019 6:06 am

MariaDB/MySQL uses port 3306 by default.

Note though that the default settings for the server do not allow access from the network, just local connections on the machine the server is running on.

To do that on the server I run, I create a file called /etc/mysql/mariadb.conf.d/99-local.cnf (or edit it and add/change the bind-address line to the [mysqld] section, if it is already there)

Code: Select all

# Local mysql config file

[mysqld]
# Allow listening on all IPV4 addresses
bind-address = 0.0.0.0

User avatar
DougieLawson
Posts: 35381
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Retrieving MySQL data from Windows Excel

Thu May 30, 2019 7:10 am

You'll need to install unixodbc. Then in Excel create a remote ODBC data source and you'll be good to go.
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Retrieving MySQL data from Windows Excel

Thu May 30, 2019 6:23 pm

OK, I changed the /etc/mysql/mariadb.conf.d/99-local.cnf file.

Now, not real sure what you (Dougie) are suggesting. When I go to Excel / Data tab which menu items do I select? I see "From Other Surces", "Existing Connections' and "New Query".

Thanks....Rob

Andyroo
Posts: 3376
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Retrieving MySQL data from Windows Excel

Thu May 30, 2019 7:10 pm

I would load MySQL connector from this site onto your PC via https://dev.mysql.com/downloads/connector/net/

Then follow M/S notes for the relevant version of Office from https://support.office.com/en-gb/articl ... a8f84a269b

Note even though MySQL is forked to MariaDB this worked early last year (not touched it on a Pi though) :lol:
Need Pi spray - these things are breeding in my house...

User avatar
DougieLawson
Posts: 35381
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Retrieving MySQL data from Windows Excel

Thu May 30, 2019 8:07 pm

Doesn't that .Net connector have to run on the machine with the database (and there isn't an ARM version). Hence my suggestion to use UnixODBC (which will run on your RPi) and the Excel ODBC connector.
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

markkuk
Posts: 96
Joined: Thu Mar 22, 2018 1:02 pm
Location: Finland

Re: Retrieving MySQL data from Windows Excel

Thu May 30, 2019 8:09 pm

DougieLawson wrote:
Thu May 30, 2019 8:07 pm
Doesn't that .Net connector have to run on the machine with the database (and there isn't an ARM version).
No, the "connector" is the client-side library that communicates with the server over network.

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Retrieving MySQL data from Windows Excel

Fri May 31, 2019 6:03 am

OK, I followed your links and have installed the following:
  • MySQL Workbench 8.0.16
    MySQL Shell 8.0.16
    MySQL Router 8.0.16
    Connector/ODBC 8.0.16
    Connector/C++ *.0.16
    Connector/J 8.0.16
    Connector/NET 8.0.15
I did not install the Server option. There was also, as I recall, an application option that I did not install. I really do not want to set up a MySQL database on my laptop.

However, when I try to create a connection (Excel/DATA/NewQuery) to my Pi it gives me an error
Details: "An error happened while reading data from the provider: 'Could not load file or assembly 'System.EnterpriseServices, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b03f5f7f11d50a3a' or one of its dependencies. Either a required impersonation level was not provided, or the provided impersonation level is invalid. (Exception from HRESULT: 0x80070542)'"
What am I doing wrong?....RDK

ps...I assume I really do not need the connectors for C++, NET and Java??

Andyroo
Posts: 3376
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Retrieving MySQL data from Windows Excel

Fri May 31, 2019 8:40 am

'Either a required impersonation level was not provided, or the provided impersonation level is invalid' normally means a security issue on the PC.

In the old Windows 7 days it was UAC messing about and this Windows 2000 KB was the fix KB 821546 No idea on Windwoes 10 I am afraid as I left that to the corporate guys a few years ago.
Need Pi spray - these things are breeding in my house...

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Retrieving MySQL data from Windows Excel

Fri May 31, 2019 12:36 pm

I returned to the setup and installed the "MySQL for Excel" application. After a reboot, I now see the "MySQL for Excel" option on the Excal/Data menu bar.

When I click on it it looks like I'm headed in the right direction and the resulting menu makes sense!!! However, after I supply all of the information and click "connect", I again get rejected, BUT this time the error message gives some useful information:
A possible fail reason could be that your user requires a SSL connection that is not currently supported.
Which sounds like I need to do some configuration modification on the Pi? Any ideas?.....RDK

Andyroo
Posts: 3376
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Retrieving MySQL data from Windows Excel

Fri May 31, 2019 12:48 pm

It may be worth posting all the error.

Looking at https://dev.mysql.com/doc/connector-net ... tions.html it says SSL is preferred but connect anyway - you may get it to work with the SSL security set to none.

Without a PC though I'm going to be a bit stuck. I do not even have a VM that I can fire up as the builder has left a hole rather than a wall and not back till Wednesday :cry:
Need Pi spray - these things are breeding in my house...

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Retrieving MySQL data from Windows Excel

Fri May 31, 2019 5:09 pm

OK, thanks for your efforts. This is not a critical issue. My workaround is to run an app on the Pi to retrieve the data to a CSV file and then import it into Excell. That works but adds an extra step. Not a big deal. However, if I can get the direct access approach to work, ever better….RDK

Andyroo
Posts: 3376
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Retrieving MySQL data from Windows Excel

Fri May 31, 2019 5:22 pm

You could try Dougies ODBC link above. It’s not one I can try here (or tried) as I now have two holes after a ‘quick pop in to do a bit’ where the wall should be :roll:
Need Pi spray - these things are breeding in my house...

User avatar
DougieLawson
Posts: 35381
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Retrieving MySQL data from Windows Excel

Fri May 31, 2019 10:03 pm

I'm busy building the MariaDB ODBC Connector from source.
https://mariadb.com/kb/en/library/build ... om-source/

That needed a little script from
https://ubuntuforums.org/showthread.php?t=499123

To get the cmake configuration stuff to run.

Then I had to create MariaDB_odbc_driver_template.ini

Code: Select all

[MariaDB]
Description = MariaDB Connector/ODBC v.3.1
Driver = /usr/local/lib/libmaodbc.so
SERVER=192.168.3.14
PORT=3306
USER=dougie
PASSWORD=redacted
DATABASE=TestDB
And installed that with sudo odbcinst -i -d -f MariaDB_odbc_driver_template.ini

That works with an isql command.
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

User avatar
DougieLawson
Posts: 35381
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Retrieving MySQL data from Windows Excel

Fri May 31, 2019 10:48 pm

To get Windows connected to MariaDB with ODBC I've started with the MSI installer.
https://downloads.mariadb.com/Connector ... -win64.msi

[Win]+[R] run odbcad32
Add your MariaDB data source with the server name, port, userid and password.

Note: you may need to install https://downloads.mariadb.com/Connector ... -win32.msi (for Excel).

Then follow Microsoft's instructions at: https://support.office.com/en-us/articl ... 091c067953
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Retrieving MySQL data from Windows Excel

Sat Jun 01, 2019 4:40 am

Dougie…..WOW!!! Looks good. Do I need to repeat the steps you noted in your first note (of your last two notes) or only those in the last note?

I'll try to get to this today but I have lots of yard work on my wife's todo list. I assume I should uninstall that "connector ensemble" that I downloaded from the MySQL site and installed on my laptop?

Thanks....RDK

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Retrieving MySQL data from Windows Excel

Tue Jun 11, 2019 6:35 am

I followed the instructions in Dougie's note above:
To get Windows connected to MariaDB with ODBC I've started with the MSI installer.
https://downloads.mariadb.com/Connector ... -win64.msi

[Win]+[R] run odbcad32
Add your MariaDB data source with the server name, port, userid and password.

Note: you may need to install https://downloads.mariadb.com/Connector ... -win32.msi (for Excel).

Then follow Microsoft's instructions at: https://support.office.com/en-us/articl ... 091c067953


I can get as far as setting up the "User DSN" for MariaDB, (Win]+[R] run odbcad32) but when I try the "Test DSN" button it rejects my credentials:
[ma-3.1.1]Access denied for user '[email protected]' (using password[: YES)
where me is the id I use on my Pi when I run a MySQL session. 192.168.0.103 is the address of my Win 10 Pro laptop
I have specified the following in the "Create a new Data Source to MariaDB" form:
  • TCPIP on
    Server Name: 192.168.0.166
    Port: 3306
    User Name: me
    Password: the one I use when accessing MySQL on the Pi
As per a previous commentin this conversation I have added the following line to /etc/mysql/mariadb.conf.d/50-server.cnf

Code: Select all

# listen on all addresses
bind-address = 0.0.0.0
What am I doing wrong or have not done? I did not try to follow Dougie's comments in the note prior to the one I quoted above.

Thanks for any further assistance...RDK
ps...I have also rebooted the Pi ...

User avatar
DougieLawson
Posts: 35381
Joined: Sun Jun 16, 2013 11:19 pm
Location: Basingstoke, UK
Contact: Website Twitter

Re: Retrieving MySQL data from Windows Excel

Tue Jun 11, 2019 6:57 am

Have you created a user that's allowed from any LAN address. The default is localhost only.

https://www.cyberciti.biz/tips/how-do-i ... erver.html
Note: Having anything remotely humorous in your signature is completely banned on this forum.

Any DMs sent on Twitter will be answered next month.

This is a doctor free zone.

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Retrieving MySQL data from Windows Excel

Tue Jun 11, 2019 8:24 am

Dougie….I had added 0.0.0.0 to allow all hosts and even created a remote user with 192.168.0.%, BUT forgot to use that user id in the dns creation step.. Thanks for the wakeup, the dns is now created. Next to actually get to the MariaDB and extract some data. More later....RDK

User avatar
RDK
Posts: 244
Joined: Wed Aug 13, 2014 10:19 am
Location: Wyoming and France

Re: Retrieving MySQL data from Windows Excel

Tue Jun 11, 2019 12:55 pm

Folks....using Excel 2016 and it is now retrieving data from MariaDB on a Raspberry Pi. Thanks for all of your assistance....RDK

Andyroo
Posts: 3376
Joined: Sat Jun 16, 2018 12:49 am
Location: Lincs U.K.

Re: Retrieving MySQL data from Windows Excel

Tue Jun 11, 2019 3:55 pm

Glad you got it working.

Sorry my ‘fix’ caused a delay.
Need Pi spray - these things are breeding in my house...

Return to “Beginners”