Excel can't find FileMaker Pro Files

[Doug Mitchell]

I am trying to import data from a FileMaker database. I go to Data
and use the command "Get Data from FileMaker Pro" and when I navigate
to the file all FileMaker files are greyed out. If I try and export
to Excel from FileMaker everything is OK. Since I am trying to import
into Excel to an existing file it is a pain to not be able to use the
Data command. Is there something simple that I am missing?

Doug Mitchell
Macintosh Powerbook G4 1.67 ghz running OSX 10.4.11
Excel 2004
FileMaker Pro 8

 

[Jim Gordon MVP]

Hi Doug,

My hunch is that you have not yet installed an ODBC driver to allow the
connection to be made.

Details here:
http://www.actualtechnologies.com/filemaker.php

-Jim

I am trying to import data from a FileMaker database. I go to Data
and use the command "Get Data from FileMaker Pro" and when I navigate
to the file all FileMaker files are greyed out. If I try and export
to Excel from FileMaker everything is OK. Since I am trying to import
into Excel to an existing file it is a pain to not be able to use the
Data command. Is there something simple that I am missing?

Doug Mitchell
Macintosh Powerbook G4 1.67 ghz running OSX 10.4.11
Excel 2004
FileMaker Pro 8

--
Jim Gordon
Mac MVP

MVPs are not Microsoft Employees
MVP info

 

[the3rdParty]

Doug / Jim

Its been a few months since I tackled this but as far as I rememeber:

1. The menu item Data/Get External data/Import from Filemaker Pro
interacts directly with native FM files BUT only if they are FM5 or 6
(ie have the fp5 extension). Files from FM 7 or 8 are not accessible
this way.

2. To get access to FM7 & 8 files from XL you need to install an ODBC
driver, configure it and then use Data/Get External Data/New Database
querey.... This sounds simple but I found it very difficult to get
the thing working with FM8 / XL2004 (both programmes running on a
local machine, no server involved) and help / reference was hard to
find, but I did manage in the end, using drivers available for free
download. I didnt use the drivers from actual technologies referenced
by Jim. Great care has to be taken in the configuration of the
driver, you need to install MS Querey and the ODBC client driver from
FM (if they are not installed already) and you need to be sure that
the database is correctly prepared.

I receommend you read the following:
http://www.filemaker.com/downloads/documentation/fm8_odbc_jdbc_developer.pdf

and

http://filemaker.custhelp.com/cgi-b...nBfc2VhcmNoX3RleHQ9b2RiYw**&p_li=&p_topview=1

Post back here if you get stuck.

Beware that there are a lot of drivers around that are designed to
take data in the other direction (xl to FM).

Cheers

JW

 

[the3rdParty]

Doug

I was digging around in my old emails and somone from actual
technologies sent me the following, which I recall was v helpful:

"Thank you for contacting us. Our drivers let you use FileMaker (or
Excel) as a front end to other databases via ODBC. We don't offer a
product for using FMP as a data source, mainly because FileMaker Inc
makes their own driver available for free.

Even though the FileMaker ODBC driver isn't our product, we do have a
set of instructions we give to people who ask us about it:

1. Download the "FileMaker 7 ODBC/JDBC update (Mac)" from the
FileMaker website:

http://www.filemaker.com/odbc

2. FMP 7 only (FMP 8 already has this installed): Follow the
instructions in the included document "FM xDBC Update Read Me
(English).pdf". To summarize, you'll copy the xDBC.fmplugin file and
the xDBC Support folder (located in the "xDBC Plug-in" folder) into
the following folder:

/Applications/FileMaker Pro 7/Extensions/

3. Copy the SequeLink.bundle file (located in the "ODBC Client
Driver" folder) to the following folder: /Library/ODBC

4. Then you'll use the ODBC Administrator to add the driver. Press
the Drivers tab, then press the Add button. Specify "SequeLink" for
the driver name, and the following path for the Driver file name
(you'll need to manually enter the path to the driver, since the ODBC
Administrator won't allow you to browse inside a bundle):

/Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk18.dylib

5. Using the ODBC Administrator, you'll need to manually enter the
DSN parameter values, since the driver doesn't include a DSN setup
assistant. Press the User DSN tab and then press the Add button, and
enter the following Keyword / Value pairs:

Host (IP address of the server - example: 192.168.0.100)
Port 2399
ServerDataSource (name of FMP database without the extension -
example: Inventory)
LoginID (optional)
Password (optional)

6. You will need to enable ODBC sharing within FileMaker:
a. Start FileMaker and open the database you specified for
"ServerDataSource" in step 4
b. Select the menu FileMaker Pro -> Sharing -> ODBC/JDBC...
c. On the FileMaker ODBC/JDBC Settings dialog, turn the ODBC/JDBC
Sharing ON
d. Select your database from the "Currently open files" list
e. Select "All Users" in the "ODBC/JDBC access to file" area
f. Press OK
g. Restart FileMaker

7. You will also need the iODBC frameworks. These are available from
www.iodbc.org, or, since you've installed the Actual Technologies ODBC
drivers, then you already have them.

8. You should then be able to query your FMP database from Excel by
selecting Data->Get External Data->New Database Query "


This is the best advice anyone sent me. I remember I had problems
with getting the odbc administrator to connect to the FM database and
it was a combination of login & passwd requirements (needed to be
defined in the DSN) and detail related to User vs System DSN
definition. I could only get it to work if teh DSN was defined under
'System'

Good luck

James

 

[the3rdParty]

Doug

I also used this doc to guide the instalation of the various necessary
components on the FM side:

http://www.fmpromigrator.com/support/fmpro_migrator/fmpro_migrator_howto_create_fmp7_dsn_macosx.html

Note that when you establish a connection to the DSN you need to use
the User name "Admin", no passwd

James

 

[Doug Mitchell]

Doug

I also used this doc to guide the instalation of the various necessary
components on the FM side:

http://www.fmpromigrator.com/support/fmpro_migrator/fmpro_migrator_ho...

Note that when you establish a connection to the DSN you need to use
the User name "Admin", no passwd

James

James,

Thank you. I will try these. For what I am doing I can always export
form FileMaker to Excel and then cut and paste to the spreadsheet I
need to use.

Doug

 

[the3rdParty]

If you dont need frequent / continuous consultation this may well be
the easiest way ahead, Remember that if you export from FM in dbf
format you get the field names in row 1 of the spreadsheet which is
useful if you have lots of fields.

What I found frustrating about getting XL to get data from FM via ODBC
was the knowledge that under OS 9 (remember that?) I set it up in a
matter of minutes.....


James