Solved

Importing data into FileMaker from Excel

Posted on 2011-02-11
7
1,424 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

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…
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…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

829 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