Here's how I got Excel to query FileMaker 11 Advanced via ODBC on the Mac.
0. Get background from http://filemakerproandexcelodbcsolution.blogspot.com/
1. From the FileMaker 11 Advanced CD, open the xDBC folder, and the ODBC Client Driver Installer, and run the FileMaker ODBC.mpkg. This will install the FileMaker ODBC.bundle in the /Library/ODBC Folder
2. From the http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/Downloads, Download the appropriate Mac OS X SDK, for my 10.5 Mac I installed iodbc-sdk-3.52.7-macosx-10.5.dmg.
3. I launched FileMaker Pro 11, created a new database file "Untitled.fp7" with one field (Name) and two records "josh" and "Bob".
4. A dialog comes up asking 'Do you want the application "fmxdbc_listener.bundle" to accept incoming network connections?" - I said Allow.
5. I pulled down the File menu and selected "Sharing -> ODBC/JDBC..."
5. I turned on ODBC Sharing, selected "Untitled.fp7" in the lower left, and set ODBC/JDBC access to "All Users", and clicked "OK".
7. I launched iODBC Administrator and clicked the "Add" button and selected the FileMaker ODBC driver and clicked Finish.
8. Then the FileMaker DSN Configuration window pops open. Click continue. Then enter a name for this DSN - I entered "Untitled"
9. For which host it defaults to localhost - be sure to check the box for "Connect to host to obtain the names of available databases"
10. In the next screen if all is good the name of the database "Untitled" should appear in the drop down, if not check the above steps again. Click continue. And then done. You are back in the iODBC Administrator - go to the System DSN tab to see your connection.
11. In Excel, open a new file and go to Data > Get External Data... > New Database Query...
12. Select System DSN from the tab at the top and select the DSN you created in the ODBC Administrator. Select your data source and press Test.
13. Enter in your username and password for your database, which by default is Admin with no password.
14. If you do get a connection, congratulations, you can press OK and carry on through into writing a SQL query by clicking OK. You should get the user pass dialog again - enter Admin with no password.
15. The Microsoft query tool comes up - click the show tables button to show the window that has the Untitled table. Click the add table button.
16. Click the SQL View button - it should have "SELECT Untitled.name FROM Untitled" - click Test! - you should see "josh" and "bob". Click the Return Data button.
17. The Returning External Data to Microsoft Excel dialog comes up - click properties, and uncheck "Use Lis Manager", then in Data Layout uncheck "Include Field names" and click ok, and then click ok again.
18. Voila! You have live data from FileMaker in Excel.
0. Get background from http://filemakerproandexcelodbcsolution.blogspot.com/
1. From the FileMaker 11 Advanced CD, open the xDBC folder, and the ODBC Client Driver Installer, and run the FileMaker ODBC.mpkg. This will install the FileMaker ODBC.bundle in the /Library/ODBC Folder
2. From the http://www.iodbc.org/dataspace/iodbc/wiki/iODBC/Downloads, Download the appropriate Mac OS X SDK, for my 10.5 Mac I installed iodbc-sdk-3.52.7-macosx-10.5.dmg.
3. I launched FileMaker Pro 11, created a new database file "Untitled.fp7" with one field (Name) and two records "josh" and "Bob".
4. A dialog comes up asking 'Do you want the application "fmxdbc_listener.bundle" to accept incoming network connections?" - I said Allow.
5. I pulled down the File menu and selected "Sharing -> ODBC/JDBC..."
5. I turned on ODBC Sharing, selected "Untitled.fp7" in the lower left, and set ODBC/JDBC access to "All Users", and clicked "OK".
7. I launched iODBC Administrator and clicked the "Add" button and selected the FileMaker ODBC driver and clicked Finish.
8. Then the FileMaker DSN Configuration window pops open. Click continue. Then enter a name for this DSN - I entered "Untitled"
9. For which host it defaults to localhost - be sure to check the box for "Connect to host to obtain the names of available databases"
10. In the next screen if all is good the name of the database "Untitled" should appear in the drop down, if not check the above steps again. Click continue. And then done. You are back in the iODBC Administrator - go to the System DSN tab to see your connection.
11. In Excel, open a new file and go to Data > Get External Data... > New Database Query...
12. Select System DSN from the tab at the top and select the DSN you created in the ODBC Administrator. Select your data source and press Test.
13. Enter in your username and password for your database, which by default is Admin with no password.
14. If you do get a connection, congratulations, you can press OK and carry on through into writing a SQL query by clicking OK. You should get the user pass dialog again - enter Admin with no password.
15. The Microsoft query tool comes up - click the show tables button to show the window that has the Untitled table. Click the add table button.
16. Click the SQL View button - it should have "SELECT Untitled.name FROM Untitled" - click Test! - you should see "josh" and "bob". Click the Return Data button.
17. The Returning External Data to Microsoft Excel dialog comes up - click properties, and uncheck "Use Lis Manager", then in Data Layout uncheck "Include Field names" and click ok, and then click ok again.
18. Voila! You have live data from FileMaker in Excel.
Comments