3 Replies Latest reply on Jun 21, 2011 8:23 AM by philmodjunk

    Transposing Data on Import to FileMaker



      Transposing Data on Import to FileMaker


      I would like to import data that is currently stored in an Excel spreadsheet in the following format:

      ID V1 V2 V3 V4
      ABC123 7 1 10 4
      ABC124 8 2 11 5
      ABC125 9 3 12 6

      Where each entry has a unique value in the ID field, V1, V2, V3... are different variables.

      One of the problems that we have is that in some instances an entry may have a different set of Variables (say, V5, V6, and V7, or only V1 and V3).  In order to convert these data into a relational database, I would like to store them in FileMaker in the following way:

      ID Variable Value
      ABC123 V1 7
      ABC123 V2 1
      ABC123 V3 10
      ABC123 V4 4
      ABC124 V1 8
      ABC124 V2 2
      ABC124 V3 11
      ABC124 V4 5

      This really isn't a "transpose" operation as in Excel.  Rather, it is creating a unique entry for each combination of ID and Variable. Does FileMaker have a simple way to do this operation?

        • 1. Re: Transposing Data on Import to FileMaker


          i don't think its possible,

          in order to import an Excel file into filemaker, the excel formal has to be the same as the filemaker table


          • 2. Re: Transposing Data on Import to FileMaker

            Are the different variables in different columns in excel or the same column?

            Usually this kind of "transformation" is handled by importing the data into a temp table then processing the records via a script to put them into the data table you want.

            If they are in the same column one way to address  the issue would be to add an indicator column to your spreadsheet and import that column as well. (ie add a column called Var_Names then V-1-2-3 or V-4-5-6 etc) then you can import the excel into a temporary new table and then process those records via a script creating records in a ID_VariableS table(the one you way said wanted to have it). You can use some case calculations to set the VariableName based on the numbers in the different positions in the indicator field.

            If they are in seperate columns then you can simply import them into a new temp table then process  the records to create new records looping through each column and setting the VariableName.

            Most things are possible however often these "transform" processes need an indicator as to how to process disparate records. Because you are working with Excel it shouldnt be too hard for you to add a column that gives a script program some indication of "how" to process the record.



            • 3. Re: Transposing Data on Import to FileMaker

              Assuming that each column, v1, v2, v3, etc is a differrent instance of the same data, you can use a series of imports. Import records the first time matching columns 1 and 3 to to the ID and value fields in your table, then use Replace field contents to load the variable field with the column header, v1. Then repeat the same process, but match columns 1 and 4, 1 and 5 and so forth until all the data is imported.

              If this is something you will do more than once, you can script this process.