MySQL for Excel provides several options to create and manage MySQL connections. You must open a connection to a MySQL server before you can configure global options, add a new schema, or perform operations that move data between Excel worksheets and MySQL tables. This section describes how to add or modify connections to MySQL.
Overview; Download and install the MySQL for Excel; Connect to your MySQL. Currently, MySQL for Excel is not available for Excel for Mac. This blog shows in great detail how to connect MS Excel to MySQL: Excel 2011. Answered May 19, 2016 Author has 3.7k answers and 18.4m answer views.
As the following figure shows, the MySQL for Excel task pane displays connection actions by default when it opens.
Figure 3.1 MySQL for Excel: MySQL Connections
Description of MySQL for Excel Connection Elements
You can use MySQL for Excel or MySQL Workbench to add new MySQL connections. Adding new connections is not permitted when MySQL Workbench is open.
To add a new connection, click New Connection in the MySQL for Excel task pane to open the MySQL Server Connection dialog. Connection names must be unique. An alert icon (!) indicates that an option value is required. The figure that follows shows the connection dialog with the Parameters tab selected.
Figure 3.2 MySQL for Excel: Add a New MySQL Connection Dialog
For each connection, provide the connection details, click to confirm the MySQL connection is valid, and click to save the new connection. The type of connection you create can vary depending on the configuration of the server, the client host computer, and the level of security you want. MySQL for Excel supports the following connection types:
Basic connections. A basic connection is either unencrypted or encrypted (in MySQL 8.0, SSL is enabled by default) and the connection is made using standard TPC/IP, which is the default connection method in MySQL for Excel to connect to the MySQL RDBMS. Basic connections are easy to configure, particularly if MySQL for Excel and the MySQL server are on the same host computer or operate within the same local area network. To configure a basic connection, set the Connection Method option to
TCP/IP (standard) and use the Parameters tab to configure the connection.
SSL connections. Both the MySQL server and the client must be configured to enable SSL encryption (see Using Encrypted Connections). To configure this type of connection, set the Connection Method option to
TCP/IP (standard) and use the Parameters tab to configure the basic connection. Next, select the SSL tab to identify the appropriate files. MySQL Server uses the PEM format for certificates and private keys. In addition to providing the paths to certificate files, you can specify the SSL mode to use for your connection. The following table describes each Use SSL option value and indicates which files are required.
Table 3.1 Use SSL Option Values
SSH connections. SSH tunnels permit you to connect to a MySQL database from behind a firewall when the MySQL server port is blocked. To configure this type of connection, set the Connection Method option to
Standard TCP/IP over SSH and use the Parameters tab to configure the connection.
Additional considerations:
You can use MySQL for Excel or MySQL Workbench to edit existing MySQL connections.
I've macOS High Sierra 10.13.3. Using the package installer I installed the most recent and the.After setting up a test database in MySQL and verifying that it all works well using Sequel Pro, I setup a user DSN in iODBCAdministrator.app for that database. The iODBC test of the DSN fails with: IM003iODBCDriver ManagerSpecified driver could not be loadedIf I move the driver files from /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib to , I get the previous IM003 error followed by this additional error: 00000iODBCDriver Managerdlopen(/Users/name/libmyodbc5w.so, 6): no suitable image found.
Did find:/Users/name/libmyodbc5w.so: mach-o, but wrong architecture/Users/name/libmyodbc5w.so: stat failed with errno=17If I open iODBCAdministrator64.app it works. But when I switch to Excel 2016 and I go on Data Get External Data From Database and I select the same DSN file, I keep getting the usual: IM003iODBCDriver ManagerSpecified driver could not be loadedAny ideas? Excel 2016 v16.10 is definitely 64-bit only.The iODBC libraries are 'fat' binaries containing both 32-bit and 64-bit, and only the 64-bit portion of the iODBC libraries is loaded when the 64-bit Excel 16.10 (or iODBC Administrator64.app, or any other 64-bit app) calls.That said -I think your issue is with the mysql-connector ODBC driver, not with the iODBC driver manager.
This can easily be confirmed by downloading and testing with any of our own; the provides easiest setup with optimal compatibility with all 32-bit and 64-bit ODBC applications. (Free two-week trial license is.)Randomly moving library files around is not usually recommended; please put the libraries you moved from /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib to back in their originally installed location.One current question is the libmyodbc5w.so. Output of file /usr/local/mysql-connector-odbc-5.3.10-macos10.13-x86-64bit/lib/.so is likely to be revealing.The full details of the DSN configuration are also likely to be relevant. Checking these requires review of four configuration files, to start -. /Library/ODBC/odbc.ini.
/Library/ODBC/odbcinst.ini. /Library/ODBC/odbc.ini. /Library/ODBC/odbcinst.ini. Hello and thanks for your answer. At the moment I have not yet found the solution. Hello,I've made the changes you indicated (symbolic link /.odbc.ini = /Library/ODBC/odbc.ini, /.odbcinst.ini = /Library/ODBC/odbcinst.ini, and use of the path instead of the name of the driver), but nothing changed: in Excel I still get the 'Specified driver could not be loaded', while in the iODBCAdministrator64.app all works fine.In all honesty, please note I had not made any particular customisations, so these things derive from the standard installation of MySQL Connector ODBC 5.3.10 or iODBC Administrator 3.52.12. First thing - I think there was a misunderstanding of my earlier.
Symlinks should be —. /.odbc.ini = /Library/ODBC/odbc.ini( not = /Library/ODBC/odbc.ini). /.odbcinst.ini = /Library/ODBC/odbcinst.ini( not = /Library/ODBC/odbcinst.ini)There are a couple other oddities in the output you provided. Thank you for the script.It all tests OK and am able to get the SQL window in Mac Excel but when I type a simple SQL query mine gives me an error codeuse ShareHistorySELECT. FROM Holdingerror messageMySQLODBC 8.0(a) Drivermysqld-5.5.44-MariaDB-logYou have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT. FROM Holding' at line 2.I have tried different syntax on the select statement to no success.any suggestions. I will try that.Somehow I think I tried it.Doesnt hurt to recheck.The interesting thing is why the 0.Will post how I go.On Thu, 18 Oct.
2018, 8:53 am Ted Thibodeau Jr,[email protected]: - Looking a little closer, I think your syntax error is a simple missing semicolon. This is what you said you executed - use ShareHistory SELECT. FROM Holding I think that should be - use ShareHistory; SELECT. FROM Holding — You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub, or mute the thread. I checked but this is not the case.I have written visual basic code and it seems to work fine.It is only when I have to get the data and place it into the spreadsheetthat this quirk happensConnection string in VB (on a different database than the previousconnStr = 'INSERT INTOBankImport(TranDate,TranAccount,TranDescription,TranDebit,TranCredit,TranType)values' & ' (' & sTranDate & ',' & sTranAccount & ',' &sTranDescription & ',' & fTranDebit & ',' & fTranCredit & ',' &sTranType & ')'works a treat. I have come to the realisation that it is how it is shownwhen excel sets up the connection.
As long as you know it is there itbecomes an inconvience. If you set it up on the PC then yo get to view thequery save it and open it up on the mac with no problemsSELECT BankImport0.ID, BankImport0.TranDate, BankImport0.TranAccount,BankImport0.TranDescription, BankImport0.TranDebit,BankImport0.TranCredit, BankImport0.TranType, BankImport0.ExpenseIDFROM BankTransact.BankImport BankImport0 limit 30.Thanks for the helpSometimes it is better to accept some deficiencies as long as you have an asolution that works. On Thu, 18 Oct 2018 at 09:26, Ted Thibodeau Jr.@.
wrote:The 0 in the query which works comes from its FROM clause, which createsan alias targeting the qualified tablename, obviating the preceding USEstatement -FROM ShareHistory.Holding Holding0Many tools and DBMS would have that read -FROM ShareHistory.Holding AS Holding0I wonder if there are multiple Holding tables in the catalogs madeavailable by this MariaDB instance? You might find this works just as well-SELECT.
FROM ShareHistory.Holding—You are receiving this because you were mentioned.Reply to this email directly, view it on GitHub, or mutethe thread. I am not sure I understand your report.
If by '64-bit works and non-64 NOK' you mean 'Excel 2016 works with the 64-bit MariaDB ODBC 3.1 Driver, and not with the 32-bit MariaDB ODBC 3.1 Driver', this is as expected. 64-bit ODBC apps such as Excel 2016 cannot load 32-bit ODBC drivers.It's best not to point to details in an external and locked silo. If my comment above does not answer your concern, please post whatever details you provided on the other forum to this thread, along with summary (or, if permitted, full text) of responses you received there. Thanks for still keeping eyes on this previously closed thread.My case is almost the same as OP except it is MariaDB, i.e. EXCEL(v16.16.8 = 64bit) cannot connect via ODBC to MariaDB in LAN. But testing within iODBC.app(64-bit) was successful, but change to non-64bit iODBC.app was NOK(IM003 error), also I cannot add user DSN via this app (apparently due to the driver not recognized).My basic settings:. MariaDB connector installed.
iODBC administator GUI installed. odbc.ini & odbcinst.ini configured. OS: MacOS (10.13.2;. EXCEL 2016 v16.16.8 = 64bitMariaDB ODBC 3.1 driver, which is 64-bit(checked with file -N): /Library/MariaDB/MariaDB-Connector-ODBC/libmaodbc.dylibHere are some screenshots:. OK with iODBC-admin.app (64-bit)2.NOK with 1.
OK with iODBC-admin.app EXCEL ODBC gets same error messageIf I understand correctly, the route goes like that:EXCEL's ODBC calls iODBC manager, within it, calls DSN, DSN uses MariaDB driver to communicate the DB server in LAN.What I observed is, EXCEL(64) calls IODBC manager(non-64) instead of 64-bit, while only the latter works and is able to connect to the MariaDB server. So the key question here is, why EXCEL calls non-64bit iODBC manager?
PS: LibreOffice has no issue, also no bring-up any ODBC manager.(I cannot help thinking that if Microsoft is again discriminating EXCEL for macOS in terms of its functionalities = evil again.)PS: This issue has been reported at mariaDB@jira, both for Excel2019@macOS 10.14 and for [email protected], external url removed as you suggested.Please let me know if you need any further info.
Comments are closed.
|
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
January 2023
Categories |