Jump to content
The ORIGINAL FileMaker Community - Forum - Online Business Apps & Software Forum
lucidr3v3ri3

Cannot properly import data into FileMaker Pro v8.5

Recommended Posts

lucidr3v3ri3

Hello all,

 

I'm new to the forum, so please excuse my brevity in introduction. I have a client who uses FileMaker 8.5 on four computers (3 PCs running XP Pro and 1 iMac) and one XP Pro user accessing the DB using FileMaker Pro 11.

 

The problem I'm having is about driving me bonkers as I've been trying different things half the day. We did an export of all of the Records (9,094) using the following Source Fields:

 

- Member ID (unique identifier serial number)

- First Name

- Last Name

- Address

- City

- State

- ZIP

 

Out of the 9,094, 397 of them have new Addresses (and therefore potentially City, State, and Zip). The problem I'm having is that I cannot import data that was exported earlier back into the database. I've tried this in .XLS, .fp7 (table), .CSV, and other formats. What happens is, after matching the fields, I get error messages. I don't want to add new records, because all 397 of the desired entries are simply updates to one or more existing fields. If I attempt (under Import Action) to Update existing records in found set, I consistently receive the following message:

 

There are more records in the data source than in the target table. Not all records were imported.

 

This will consistently tell me that only 1 record has been created/updated, regardless of whether I try 1, 20, or all 397. Generally speaking, immediately following this, multiple other existing records disappear, though the total number - 9,094 - remains the same.

 

Initially, the export included the above mentioned Source Fields and some others (Spouse First Name, Organization, etc.) but since these are, for all intents and purposes, unchanging, I removed the superfluous columns hoping to see a difference. No luck.

 

If I attempt (under Import matching records in found set) to have the records to be imported match the unique identifier (the Member ID, which is static based on user), it usually will update just the first entry user, and only if that user is selected (tells me only 1 records has been created/updated). Oftentimes, this will destroy multiple other existing accounts and the data will no longer show up in search.

 

I've even matched up the Source Fields in the spreadsheet/.fp7 tables to be identical to the Target Fields (which shouldn't be necessary), and I've tried all different combinations of forcing the Import (i.e. just the Address field, all 5, etc.). No dice - same result, and little progress.

 

I can import everything as a new record, but then I have duplicates and two different addresses for each Member.

 

It just seems like we never had this issue before and, according to the forums and help out there, it should be very simple. I'm pretty much stumped here and I have clients needing to get this information put in properly so they can move forward.

 

It is possible that the export of the data for all users was done from the version 11 Pro of the software, but it's connected to the same database as everyone else, and it's exporting into .XLS, so it doesn't seem like this would be causing the issue. I've had the same results trying these procedures from both a PC running v8.5 and the PC running v11.

 

Any help at all would be greatly appreciated. I'll be chugging away on this thing until I get it, but I'm about out of options here as far as I know what to do.

 

Thanks a bunch!

Ben

Share this post


Link to post
Share on other sites
Helimanick

Hello Ben, Welcome to the forums!

 

I just read over your post. Are you trying to export records from Filemaker on one computer and import them into another computer running filemaker? If so, Filemaker wasn't really meant to work this way. Filemaker allows you to share a database with multiple computers on a local network so all users accessing the database always view the most current data available. There is no need to export data from one filemaker to another. Just dedicate one computer on the network as the Host computer that would need to have the Filemaker file open when the other computers are wanting to access the Filemaker database. Then on the other computers select File:Open Remote and select view Local Hosts. Your Filemaker host computer should show up on the left then after clicking on it, it should show a list of any files that are open and have FileMaker Network turned on in them. So essentially only one computer is opening the database locally and it is sharing it to all other computers over the local network. You can give each person their own login info to make things a little more secure also by going to File:Manage:Security on the host computer and defining account names and passwords.

 

I hope this helps if you have any further questions just post away. I will do my best to answer them.

Share this post


Link to post
Share on other sites
lucidr3v3ri3

Thanks for the response.

 

Actually, the data was exported from the same database - just one computer connected to it. The data is then sent to some third-party company that checks against a national database listing a directory for changed addresses. Any alterations are sent back in the same format.

 

What I'm asking (I think is pretty simple). I guess I could step back and just ask the obvious - does FileMaker have an issue with importing data into a table with existing records?

 

Example: In the Member ID field, member #49 has a home address listed. Member #49 needs her address updated from either an .fp7 table or .xls spreadsheet. When I attempt to import data from the Address Source Field in either format, it won't update the existing address. Does this simply not work (existing data can't be updated), or is something just not working properly?

 

I don't see anybody else having this issue, so it's starting to make me wonder if I'm doing something wrong, or if there is an issue with the database. It really does seem like I should be able to just import data from an .xls or .csv formatted file, but it just won't allow me to.

 

Thanks!

Ben

 

Hello Ben, Welcome to the forums!

 

I just read over your post. Are you trying to export records from Filemaker on one computer and import them into another computer running filemaker? If so, Filemaker wasn't really meant to work this way. Filemaker allows you to share a database with multiple computers on a local network so all users accessing the database always view the most current data available. There is no need to export data from one filemaker to another. Just dedicate one computer on the network as the Host computer that would need to have the Filemaker file open when the other computers are wanting to access the Filemaker database. Then on the other computers select File:Open Remote and select view Local Hosts. Your Filemaker host computer should show up on the left then after clicking on it, it should show a list of any files that are open and have FileMaker Network turned on in them. So essentially only one computer is opening the database locally and it is sharing it to all other computers over the local network. You can give each person their own login info to make things a little more secure also by going to File:Manage:Security on the host computer and defining account names and passwords.

 

I hope this helps if you have any further questions just post away. I will do my best to answer them.

Share this post


Link to post
Share on other sites
Helimanick

Hello Ben,

 

I have my best luck using TAB separated files (.tab) when trying to import records. I would say make sure that you are matching the MemberID field with the MemberID field you are trying to import. This requires clicking the icon between the Source Fields and Target Fields so it matches records based on this value. The icon should look something like this with arrows pointing in both directions. So it will match records with the same ID and update any info that has changed in them. Since you are sending the exported data off to some other third party to be analyzed/modified I cant say for sure they might mess up something with how your data is entered in the exported file. But yes you are right this should work. There should not be any more records in the returned data as they are just changing data that is already there and not adding anything new correct?

Share this post


Link to post
Share on other sites
Helimanick
does FileMaker have an issue with importing data into a table with existing records?

 

Not usually. I do something similar to what you are trying to do with a database of over 50k records.

 

Does this simply not work (existing data can't be updated), or is something just not working properly?

 

Existing data can be updated but the file being imported needs to have the same amount or records that is currently in the database. If it has more records then those will be ignored or can be added as new records by checking the box. Make sure under import action you select "Update matching records in found set" and select the ID field as the match field. This should work without any problems. But since the file is being modified by an outside source the problem could lie with them. I dont know what service you are using or if they even offer some developer backed. But another route would be to implement a some type of plugin into your database that can connect to this service and filemaker would update the records for you instead of exporting and importing every time you want to update records. Exporting and Importing just seem like too much work. Make Filemaker work for you so you dont have to work for it.

Share this post


Link to post
Share on other sites
AHunter3

I think probably the fact that you first EXPORT data is a red herring and that what you've got is simply a problem IMPORTING data. Mostly likely you have one or more fields with field validation and with the validation option of ALWAYS verify the data instead of just during data entry.

Share this post


Link to post
Share on other sites
lucidr3v3ri3

Excellent. I'll try the TAB separated values and see if that can make a difference. I'll report back. Thanks for the quick responses!

Share this post


Link to post
Share on other sites
lucidr3v3ri3

Okay. I've got the third party that exported the data into .xls exporting it now into .tab or .txt (tab separated).

 

Talk to me about the field validation. Presumably, you mean within FileMaker. Can I turn off this validation? Where would I go?

 

Thanks in advance!

 

I think probably the fact that you first EXPORT data is a red herring and that what you've got is simply a problem IMPORTING data. Mostly likely you have one or more fields with field validation and with the validation option of ALWAYS verify the data instead of just during data entry.

Share this post


Link to post
Share on other sites
Helimanick

Yes he is talking about from within FileMaker.

 

For the field validation you goto File:Manage:database or Command+Shift+D on a Mac (Ctrl+Shift+D on Windows). You then select the table you are exporting data out of. Then you can quickly look in the right column (Options/Comments) and see any field says "Always Validate". If any do you can double click on them, then select the validation tab up top and change it to "Only during data entry".

 

Hope this helps you out with your importing issue.

Share this post


Link to post
Share on other sites
lucidr3v3ri3

So I assume that if I'm importing data from a Tab delimited file and I have it set to "Only during data entry" and have the box checked that says "Allow user to override during data entry," it shouldn't matter where the data to be imported came from (i.e. if it was exported from the database while it was forcing file validation on the Field), right?

 

I ask because in this client's database, it was already set on each of the Fields I'm trying to import into as it should be as far as field validation is concerned.

 

Yes he is talking about from within FileMaker.

 

For the field validation you goto File:Manage:database or Command+Shift+D on a Mac (Ctrl+Shift+D on Windows). You then select the table you are exporting data out of. Then you can quickly look in the right column (Options/Comments) and see any field says "Always Validate". If any do you can double click on them, then select the validation tab up top and change it to "Only during data entry".

 

Hope this helps you out with your importing issue.

Share this post


Link to post
Share on other sites
Helimanick

So you are saying all fields have not been changed to Always Validate so the importing issue can not be a validation problem.

 

Hmm...

 

Well can you export the data from FileMaker in Tab-Separated format and open up the file in a text editor. Then edit a few records from within the text editor (being careful to not change the structure of the file i.e. deleting tabs and returns) and save the file. Then try importing the few changes you made back into FileMaker. If this works fine then I would say the problem lies in the data you get back from the third party then.

 

Also maybe try searching for one record and export just that one record to a .xls or whatever format you use to send to the third party. Then send it off and see if it comes back changed in any way. How is the third party matching records on their end and deciding which records need changed/updated?

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

Terms of Use