Solved

Importing data into FileMaker from Excel

Posted on 2011-02-11
7
1,388 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 24

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 24

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

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 24

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
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…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

911 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now