People Goal 6 - Part 1: Prepare your spreadsheets for import

Document created by Kedar on Jan 29, 2015Last modified by communitymanager on May 12, 2015
Version 26Show Document
  • View in full screen mode

 


Let's work on getting your existing data ready for import into FileMaker Pro. We'll start by assuming that your data is in spreadsheet format (if not, ask the New User Group for advice).

Having your data in spreadsheet format doesn't guarantee that it's ready to import. Spreadsheets can be set up in many different ways, depending on what you want to communicate with them. When you import spreadsheet data into FileMaker Pro — and especially when you import into an existing table — your data must be organized in a specific manner:

 

  • Each column of your spreadsheet (or at least the ones you need to import) should match a specific field in your FileMaker Pro table.

  • You should have separate spreadsheets (or worksheets) for the fields in your main table and the fields in your secondary table.

 

  • The first row of each spreadsheet should be a header, identifying what each column represents.

  • Each subsequent row of your spreadsheet will become a record in the destination table.

 

 


What if you only have one spreadsheet?

 

Instead of starting out with a main data spreadsheet and a secondary data spreadsheet, it’s very possible that you just have one spreadsheet that combines your data in one place. You’ll need to separate your data into two spreadsheets, one containing only those columns that match up to fields in your solution’s main table, and the other containing the columns that match up to fields in your secondary table. Ask the New User Group for help with this if you need it.


 

What makes a good spreadsheet?


Here’s an example of a good spreadsheet. This is data intended to be imported into the secondary table (Activities) of the example solution. 

pasted-image.png


The first step that will make the import go smoothly is to make the first row a header row. Each cell in this row should contain the name of a field in your table, corresponding to the data in the column. Including this row will make lining up your import very easy.

 

Next, make sure that each column only contains one data type. For example, column B (Activity Date) only contains valid dates, not words or numbers.

 

Finally, because every row in your spreadsheet except the header will become a record in your FileMaker Pro table, make sure that each row represents only one record. Don’t mix the data from multiple records in one row, or spread the data for one record over multiple rows.



 

What makes a bad spreadsheet? (example 1)

 

In the example below, the first row will fail as a header row. While the spreadsheet has a header row, it doesn’t appear until line 7. FileMaker Pro can only interpret a header row when it appears as line 1.  Additionally, Rows 2 to 7 will be converted into records containing junk data. This spreadsheet is easy to fix: just delete rows 1 to 6.


pasted-image-2.png

 

 

What makes a bad spreadsheet? (example 2)

 

In this example, a single record takes up more than one row. Since FileMaker Pro imports each row as a separate field, this won't work.

Additionally, different kinds of data appear in the same column. Since FileMaker Pro imports each column to a specific field, it won't work for this reason either.

 


This spreadsheet must be reorganized so that each column contains only one data point (Activity Date, Activity Time, etc). If you can’t add a single header row that describes the data consistently, your spreadsheet isn’t ready for import.

 

pasted-image-3.png


 

What makes a bad spreadsheet? (example 3)

 

Sometimes when people prepare spreadsheets, they don’t populate columns consistently. Here the Status column only has the value “Done” for the first record with this status. The rows below it are assumed to have the same status even though the cell is empty. Then “To Do” appears for the first record with this status, and again, the cell is empty for the records below it.

 

FileMaker Pro will interpret the empty cells literally, as blank values. If you want a value to be imported, you must fill in each empty cell with the appropriate value. Note that some empty cells — perhaps missing the time or activity notes — could be valid.

 

A quick way to fix Column A in Excel is to use the Fill Handle, which allows you to drag a selected value downwards into other cells.

pasted-image-4.png

 


What makes a bad spreadsheet? (example 4)


You can think of this as the complement to example 3. There are only two records represented here, but the notes for those records occupy multiple lines.

 

The simplest solution  is to combine the notes for each record so they are stored in one cell, rather than three for the first record and two for the second one.

 

If you needed to keep the notes separate, you could import them into a separate table which you would connect relationally to your secondary table, but that’s beyond the scope of this training.


bad example 5.png




Goal:

 

Produce a single spreadsheet for all your main table data and a single spreadsheet for your secondary table data, cleaning up and rearranging the spreadsheets so that they are ready to import into your solution.

 

If you have multiple spreadsheets of main table data, you should combine them into a single spreadsheet (see step 3 below). The same is true for secondary table data.

 

 

Steps:


 

If you need to modify your spreadsheets, here’s how we recommend that you proceed:

 

  1. Create a new spreadsheet (or worksheet) where you assemble your cleaned-up data.

    This leaves your original data untouched in case you need to refer back to it. Think of the new spreadsheet as a clean workspace.

  2. Make sure that the first row of your spreadsheet is a header row, where the column names match the field names in your FileMaker Pro table.

    In this case, you would simply delete rows 1 to 6.

    Not this:

    pasted-image-2 copy.png
    But this:
    pasted-image copy.png

  3. Paste data from your original spreadsheet into your working spreadsheet.

    If you have more than one spreadsheet containing data that you want to import into your main table, copy and paste each one into your working spreadsheet.

  4. If any columns are calculated, remove them unless you need to import them as data.

    A better approach than importing a calculated column would be to create a similar calculation field in your FileMaker Pro table. If you need help with this, start a discussion in the New User Group.

  5. Make sure that you have one record per row:

    Not this:
    pasted-image-4 copy.png
    But this:
    pasted-image-5 copy.png


    And not this:
    pasted-image-8.png
    But this:
    Untitled 2.png

  6. Make sure that the data in each column has the appropriate format, based on the destination field in your solution. People sometimes enter data in inconsistent ways. For example, in a Due Date column, one cell might say “1/1/2016” and another one “Tomorrow”. If your column is going to be imported into a date field in your solution, it must be in a valid and consistent date format.

    Not this:
    pasted-image-5.png

    But this:
    pasted-image-6.png
  7. Make sure that columns are populated consistently. It’s OK to have empty cells as long as they are supposed to be empty. (See bad example vs good example).

    Not this:But this:
    pasted-image-6 copy.png

 

Repeat this process for spreadsheets containing data that you want to import into your secondary table.



      

      

Attachments

    Outcomes