Solved

Importing data into FileMaker from Excel

Posted on 2011-02-11
7
1,408 Views
Last Modified: 2012-05-11
Hi, I am converting Access database into FileMaker.  I have exported all of the data from Access into Excel, have gone through the import process, but am still having issues.
In Import Records -> File... command, after I choose my Excel workbook, I cannot use "Match records based on this field", because it is grayed out.
Also, when I try to just simply add new records, it gives me the same amount of blank records, which I have to delete afterwards.  I feel like I am not using the Imort function properly, although I am following the tutorial steps.
Please advise.
0
Comment
Question by:yballan
  • 3
  • 3
7 Comments
 
LVL 25

Expert Comment

by:Will Loving
ID: 34871938
I think what you are wanting to do is an Import that creates a table at the same time. To do this, click Import and select your file and worksheet. One you are in the field matching screen, click the "Target" pop-up menu above the right-hand column and select "New Table...".

Then in the Lower Right corner of the Import Field Matching, click "Don't import first record (contains field names)". This will cause FileMaker to create a new table with the fields named the same as the first row in your spreadsheet. If your first row is data rather than field names, consider changing the spreadsheet before you import it to have the first row have field names. It's much faster to do it that way then to change them after FileMaker names them all f1, f2, f3, etc.

Once table is created and all records imported, you can change the name of the table - which defaults to your spreadsheet name - to something more appropriate. If you are importing multiple tables, do the procedure above with each one and then build the relationships between the tables as required to match your Access database.
0
 

Author Comment

by:yballan
ID: 34872960
Dear willmcn,
Thank you for your reply.  I am actually trying to add new data to existing table.
I followed your instructions, but that creates a new table with new info only.
I want to be able to augment my existing table, and also be able to update the existing table with new data after matching the records based on my ID field.
0
 
LVL 25

Expert Comment

by:Will Loving
ID: 34873165
If you are wanting to import using "Matching Fields" then you need to make sure that column headings in your spreadsheet match the fields in FileMaker. You can either change them in FileMaker or in your spreadsheet (or even in Access). if the field names don't match exactly you have to line them up manually, which it's easy to make mistakes doing if you have a lot of fields (speaking from experience!).

In your original post you said:  

I cannot use "Match records based on this field", because it is grayed out.

I think what is missing is that you have neglected to check the box in the lower left corner of the Import Field Mapping dialog that says:  "Don't import first record (contains field names)". Clicking this checkbox should allow you to set the Arrange by setting to "Matching"
0
ScreenConnect 6.0 Free Trial

Want empowering updates? You're in the right place! Discover new features in ScreenConnect 6.0, based on partner feedback, to keep you business operating smoothly and optimally (the way it should be). Explore all of the extras and enhancements for yourself!

 

Author Comment

by:yballan
ID: 34873469
Hi, willmcn,

I do have the "Don;t import fiest record" checked.  
I just realized that if I check "Update matching records in found set", it will let me choose "Match records based on this field.
Does this mean that everytime I want to update the database by importing .xls file, I must first perform a find?
0
 
LVL 1

Accepted Solution

by:
savoieadam earned 250 total points
ID: 34873562
No it does this for you.
When you have "Update matching records in found set" selected, it performs its own find to match the records that correspond to your source & target fields that you selected with  = "Match records based on this field".

0
 
LVL 25

Assisted Solution

by:Will Loving
Will Loving earned 250 total points
ID: 34873622
So, this sounds like a clarification of what you are trying to do. If what you want is to simply update existing records with the data from Access, rather than add new records, then you do need to use the "Update matching records in found set" and select at least one matching field, usually the record ID. As savoieadam notes, FM will only import/update records that actually match on the matching field(s) ignoring the rest in the import file.
0
 

Author Comment

by:yballan
ID: 34873639
Yes, thank you, this is what I needed to do!
0

Featured Post

ScreenConnect 6.0 Free Trial

Explore all the enhancements in one game-changing release, ScreenConnect 6.0, based on partner feedback. New features include a redesigned UI, app configurations and chat acknowledgement to improve customer engagement!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question