Pushing data from Excel to remote MySQL database on a Mac
I’ve been beefing up my Excel skills recently, and stretching both Excel and myself to get it to do more. This time, my task was not terribly complicated, but not an everyday, ordinary thing either: I wanted to create a table in Excel and push the contents to a MySQL server outside of my local network. Doesn’t seem like it should be that hard, right? I expected it to be much easier, but in the end, I got it to work, but not before really coming to terms with the reality that Excel is definitely a Windows-first tool. Here were my steps:
- Set up the MySQL server (in my case, Ubuntu, with a plain vanilla install) and the basic database architecture, complete with users. Don’t forget to flush privileges at the end. If it’s a new install, you might need to apt-get some tools, such as vim and ufw.
- Set up the mysql-secure-installation package.
- Set up your firewall/router to handle the traffic. I put it on a non-standard port, and then tested from terminal to make sure I could log into the server and connect.
- In the MySQL configs, I needed to set the bind-address setting to 0.0.0.0 and the port to the non-standard port I selected in Set 3.
- In MySQL, as the root user, in addition to the “normal” users I had set up, I also needed to add users with privileges specific to the IP address I was connecting from (both remote, and local). This was the main place I had problems.
- My Ubuntu box uses ufw for its software firewall, so I had to explicitly enable the firewall, allow the traffic on the non-standard port, and reload the firewall. Now I could switch over to my laptop.
- In Excel, when you add a new database connection, Microsoft informs you (I upgraded to Office 2016 for this, oh well) that while SQL connections are supported natively, you need to buy an ODBC driver to connect to open databases such as MySQL, Postgres, etc. I bought the driver from Actual Technologies. (Important: it didn’t work as I had hoped, and ultimately, I didn’t need it.)
- I finally the right tool: https://www.sequelpro.com/ YES! This is exactly what I needed. After some tinkering with the setup and testing — and then referencing my error logs on the server to troubleshoot — I was able to do just what I wanted: push data easily from Excel to the database.
If you have a different tool, or know a better way to do this, I’d love to hear back from you to include it in my notes for future reference. But until Microsoft decided to get serious about supporting its data-loving Mac users, this is the best solution I could find. (BTW, I didn’t want to run the database on my Mac, which would have made it easier, because ultimately I’ll have a web service connecting to the database.)