Penn State College of Agricultural Sciences
SEARCH PENN STATE Search Penn State

Convert Excel file to FileMaker Pro 4


FileMaker Pro version 4 (FMP4) has the ability to open an Excel file and convert it to FileMaker's format. Note: other higher versions of FMP can do this as well. These steps focus on FMP4 specifically.

 

NOTE: Steps to convert the Excel file are very simple. You can simply open the Excel file using FMP4 and the file will be converted. But, if you want to print labels or move the data into an existing FMP4 mailing list file, you will need to follow the additional steps.

 

Convert and Excel file to FMP4
Add Appropriate Field Names
Import Data
Import Data to ML4v1

Convert an Excel file to FMP4

Note: You should have saved the Excel file to a known location on your computer. You will need to locate it with FMP4.

  1. Open the FileMaker Pro 4 application. Click Cancel in the opening dialog box.
  2. From the File menu choose Open.

    Note: The Files of type: drop-down list show its default choice of FileMaker Files (*.FP3, *.FM). You need to switch this to show EXCEL (*.XLS, *.XLW) instead. The Excel choice is the last one in the list.

  3. Click the drop-down triangle for the Files of type: drop-down list.
  4. Scroll to the bottom of the list of choices.
  5. Select the EXCEL (*.XLS, *.XLW).
  6. Click the drop-down triangle for the Look in: drop-down list. Navigate to the folder where you have stored the Excel file. You should "see it" if you have switched the Files of type: drop-down list to show Excel files.
  7. Select the Excel file and click Open.
  8. The Name Converted file: dialog box should appear. The word "Converted" will be added to the end of the file name. You can change this if needed.
  9. When ready, click Save.

    Note: The Excel file will open in FileMaker. You will see a List view of the data. The default field names will be f1, f2, f3, etc. You can begin to use the Excel data in FileMaker at this point.

    Note: If you need to import this data into an IT FileMaker file/list, you can continue with the next sections.

Top of Page

Add Appropriate Field Names

KEY Note: If you are planning to import your data into a ML4v1.fp3 file, you should skip these steps and go to section Import Data to ML4v1.

Note: Field names are "generic" when an Excel file is opened in FMP4. They will start with the letter "f" for field and then have a number assigned to them, f1, f2, f3, etc. If you plan to import this data into another FileMaker file, you should open THAT file and verify its field names. You can then re-name the converted Excel data field names to match.

  1. Look at the data in the "converted" file. Then using a piece of paper, make a list of the f1, f2, etc. fields and what data they contain. Use your best judgment at this point. The "converted" data may have more information than you need. Only make a list of the data you really need transferred. Example, if f1 has the person's first name, list it as f1 ... First Name. If f2 has their phone number, list it as f2 ... Phone No.

  2. From the File menu choose Open. Open the IT FileMaker file that will receive the imported data. Enter DEVELOP as the password.
  3. From the File menu choose Define and then select Fields.
  4. Scroll to the top of this list.
  5. Scroll through this list. Make note of the REAL field names that match what you have already written down. If needed, scratch out what you wrote and replace with the new name.

    Note: Some IT field names will have a period (.) as part of the name. Example: you may see First.Name instead of First Name, Zip.Code instead of Zip Code.

    Note: Here is a list of standard IT field names without the period (.).

    Prefix Name, First Name, Middle Initial, Last Name, Suffix Name, Title, Business Name
    Street, City, State, Zip Code
    Phone No., Business Phone No.

  6. When you have a list of the field names you need, click Done to close the Define Fields dialog box.

    Note: If the converted data did not break out a person's name into First Name, Last Name, etc., you will not be able to import this data into the Full Name field in an IT database, The Full Name field is a calculated field and you can't import into calculation fields. In these cases, we recommend that this data be imported into the Last Name field.

  7. From the Window menu choose the "Converted" file. If you don't see the "Converted" file in the Window menu's list, use the File ... Open command to re-open the "Converted" file.
  8. In the "Converted" file, from the File menu choose Define and then select Fields.

    Note: You should see the list of f1, f2, f3 fields.

  9. Highlight (click ONCE) a field that you plan to re-name. Example: f1.
  10. In the Field Name box (above the Create button), select the name of the field again by clicking and dragging over the name.
  11. Type in the name of the new field. (Note: If you start to type the name and the Save button is grayed out, you should start over again with Step 10.)
  12. When you have the new name entered, click Save. This name should now appear in the Field Name list.
  13. Repeat Steps 9 - 12 to rename all the needed fields.
  14. When finished, click Done.

    Note: When doing the import in the next section, it is the found set of data that will be imported. If needed, you can go through the records and delete any extra records at this time.

Top of Page

Import Data

  1. If the IT FileMaker file that will receive the imported data is still open, from the Windows menu, switch to this file.
    If you had closed this file, from the File menu choose Open. Open the Enter EDIT as the password.
  2. From the File menu choose Import/Export and then select Import Records.
  3. Highlight and open the "Converted" file.

    Note: You should now see the Import Field Mapping dialog box. When a name on the left is matched up with a name on the right AND there is a black, right arrow in the middle column, the data will be imported. Since you have re-named the field names, you should be able to use Step 4 to automatically match the correct fields.

    Note: In the upper right corner of the Import Field Mapping dialog box, look for the View by: drop-down list.

  4. From the View by: drop-down list choose Matching Names.

    Note: The fields should now match and have the black, right arrow in the middle column. If all looks OK, go to Step 5.

    Note: If some fields didn't match, you can click and drag the fields on the right to match up with the fields on the left. Be sure that there is a black, right arrow in the middle column as well. If not, click in the middle column until the arrow appears.

  5. Click Import. Click OK.
  6. Review the data. It should be ready to go.

Top of Page

Import Data to ML4v1

Note: Since IT Mailing List version 4 files are a "multi-file" relational mailing list program, you need to download

  1. Click this link to download the Convert-Transfer files and How To.
  2. Click Save. Save the file to your desktop.
  3. Then, double click on the TransfertoML4v1.exe. Click OK twice to extract the files.
  4. Open the TransfertoML4v1 folder.
  5. Open and print the TransferProcess.pdf file.
  6. Follow its steps to transfer the "Converted" FMP4 Excel file to your ML4v1 FMP4 files.

Top of Page

 

How To Convert Excel file to FileMaker Pro 4
created 01-03-07 vcv, updated 04-02-2008 [vcv];

 

 

 

Penn State University College of Agricultural Sciences