Excel 2008 > Data > Get External Data >

[cjcj01]

Version: 2008
Operating System: Mac OS X 10.5 (Leopard)
Processor: Intel

I am importing medical records from Filemaker into Excel via the Excel 2008 > Data > Get External Data > Import from Filemaker Pro menu item.

However some columns, typically those populating data from calculation fields in filemaker or those holding decimals contain random data samples from across the database but unrelated to what is actually held in those records.

i.e. if I export direct from filemaker I get the correct data, if I import via the Get External Data Sources menu function in Excel I get randomised data in some columns.

This function of Excel could be very powerful as it acts supposedly like an ODBC connection allowing regular querying of the data and refreshing directly from the database source rather than having to export data sets out from Filemaker which only show one instance of the data at a particular point in time.

But the menu item is useless unless it can accurately draw data in from Filemaker. Has anyone else had problems with these functions and are there any workarounds any one has found?

Thanks

 

[Shane Devenshire]

Hi,

Sorry, I don't have Filemake and i don't have 2008 in front of me, but is
there an alternate choice under Data, Get External Data, New Database Query?
If so try that.

 

[cjcj01]

Hi Shane,

Thanks for your reply.

Yes, I've tried that, in fact, between you writing and now I've written a whole blog post about how to connect Filemaker and Excel via ODBC and use that very function.

<http://filemakerproandexcelodbcsolution.blogspot.com/>

The problem is that it worked once. Then Query lost the ability to somehow see my DSN even though nothing had changed from one day to the next as far as I was concerned. I set the whole thing up again but had to set up the DSN from within MS Query this time for it to recognise the DSN. It worked once again, I went to bed, opened it up in the morning and it now won't work at all.

I've tried on Leopard and Tiger on two different MacBooks and they are both now returning the same result, however I have never successfully got the ODBC connection working on Tiger.

The original Get External Data > Import from Filemaker Pro is returning Japanese or Madarin characters as well as random data if I try that via Filemaker Pro 9 or 10 and none of the criterias that Excel allows you to use can be set as it just returns as error.

From my perspective this function with Excel is completely Beta and untested.

The get data from external data source is inconsistent and unreliable and with no instructions or documentation it isn't even possible to work out where the problem lies i.e. in Excel, ODBC Administrator, the SequeLink driver, Filemaker Pro or somewhere in the underlying iODBC framework.

 

[cjcj01]

Ok - now kudos to this guy over at filemaker

<http://forum-en.filemaker.com/fm/board/message?board.id=FM-en-4&message.id=14824>

The key is to first download the iODBC administrator - come on Filemaker, why don't you say this?

And Microsoft - the data import from Filemaker Pro is flawed. Get rid of the menu item if it doesn't work. It's been a problem since 2001.

Here's the instructions you need to get this working via ODBC:

1. I downloaded the iODBC Administrator at:

<http://download.cnet.com/iODBC-Driver-Manager-Administrator-SDK/3000-2070_4-51225.html>

2. I installed it, and it was placed inside my Hard Drive -> Applications -> iODBC (folder)

3. I put in my FileMaker Pro 10 disc, opened the xDBC directory and copied the SequeLink.bundle into the Hard Drive -> Library -> ODBC (folder)

4. I launched FileMaker Pro 10, created a new database file "Test.fp7" with one field (Name) and one record (TSGal)

5. I pulled down the File menu and selected "Sharing -> ODBC/JDBC..."

6. I turned on ODBC Sharing, selected "Test.fp7" in the lower left, and set ODBC/JDBC access to "All Users", and clicked "OK".

7. I launched iODBC Administrator and selected the tab "ODBC Drivers".

8. I clicked "Add a driver", and entered the following information:

Description of the driver: myODBCdriver

Driver file name: /Library/ODBC/SequeLink.bundle/Contents/MacOS/ivslk20.dylib
(Note: Be sure to manually type this in. Do not paste! )

Click "OK".

9. Click on the System DSN tab, and click the "Add" button.

10. I entered the following information.

Data Source Name (DSN): myTESTDB1

a. Keyword: Host
Value: 127.0.0.1
(Click Add)

b. Keyword: Port
Value: 2399
(Click Add)

c. Keyword: ServerDataSource
Value: Test
(Click Add)

Click OK.

11. Now, you should be set up. Click the "Test" button. This should return everything was successful. If not, then let me know what is the error message, and we'll proceed from there.