Unable to import data correctly

[rcacciato]

I have a database of US airports, identified by their three-letter IATA codes (e.g. JFK, ATL, ORD, LAX, etc.). The database includes enplanement information provided by the FAA. I set this up in 2021 so I included the latest year available of this data, 2019. The FAA has just published 2023 data and I want to import that, but my import doesn't work.

I've set up a tab delimited file with column A = IATA code and column B = emplacements. My original database has 512 records.

This is what I've tried:

First Attempt:

1. I select all records.

2. I then select:

    File > Import Records > File

3. I select the tab delimited file and get the import field mapping dialog (screenshot attached).

4. I select Update matching records in found set.

5. I click on the arrow next to iatacode to "Match records based on this field". The two-headed arrow appears.

6. I leave the "Add remaining data as new records" box unchecked

6. I click on import. I check Perform auto-enter options while importing, then click Import.

7. I get this result:

    Total records added/updated: 0

Second Attempt:

I do the same as above except in step 6 I check the "Add remaining data as new records" box.

When I do the import I end up with 1014 records (the new file has 502 records so the original 512+502). Instead of updating the existing records with the new data, it created a whole new set, even though the match field data matches.

I've done imports many times before and I've never had this issue. This seems odd. Does anyone have any idea what's going on?

Thanks in advance.

[comment]

Hard to say without seeing the actual data in both files.

Based on what you describe, it seems that the data in the first column of the source file does not match the data in your iatacode field. Perhaps your existing codes have a trailing space or something similar.

 

[rcacciato]

No, they're all 3 letters. I checked to make sure and the field types are correct, IATA code is text and the emplacements field is number. The data from the FAA had commas (as in 53,505,795 for Atlanta) which I thought might be the problem. I was hoping that eliminating the commas would fix it but it still doesn't work. 

FYI I'm using Filemaker 16.0.6.600 on MacOS Sonoma. Might it be some weird incompatibility? I don't think so since everything works... I hope not.

[comment]

Can you post the two files here? 

 

[LaRetta]

6 hours ago, rcacciato said:

FYI I'm using Filemaker 16.0.6.600 on MacOS Sonoma. Might it be some weird incompatibility?

There have been many reports of issues with Sonoma on older FM versions.  A search turns up this one on importing tab-delimited, although it doesn't specifically solve your problem:

 https://community.claris.com/en/s/question/0D53w00006LYrmRCAT/using-mac-sonoma-i-cant-import-tab-text-files-in-fmp-advance-from-ver-15-to-19

You might consider upgrading until you can produce your files for Michael.  It would be good to also update your profile for us.  😃

[rcacciato]

Hi, thanks for your input. Unfortunately the database has confidential information so I'm not in a position to share it here. I'll just have to do it the old fashioned way--typing it in as I go. Thanks in any case for your help.

[comment]

Could you not make a copy of your file and remove all the confidential information, leaving only the two fields relevant to the import?

I assume the file you got from FAA is available to the public.

 

[rcacciato]

Yes, but then I'll have to reimport everything and I'm afraid I'll run into a similar problem. The FAA info is available but there is other info in the database. Let me know what you think.

So I did a test which worked. I created a new file with two fields, 3 letter airport code (text) and passengers (numbers). I did an import of the airport codes and left the passenger field empty. Then I did another import of the passenger data using the airport code field as the match. It worked--the passenger data was imported correctly. So it doesn't look like an incompatibility question. I was wondering if It wasn't working because the layout I was on did not include the fields being imported so I tried it on a layout which has the relevant fields, but that didn't work. I wonder what else could be going on.

Thanks.

Edited October 28, 2024 by rcacciato
moved "thanks" to the end which wa in the middle because both responses were merged.

[comment]

10 minutes ago, rcacciato said:

Let me know what you think.

I think we need to diagnose the problem before suggesting a cure. I don't know how to do that without being able to reproduce the problem - hence the request to see your files, or at least a copy that exhibits the same behavior.