9 Replies Latest reply on Feb 22, 2012 2:25 PM by philmodjunk

    Trying to import a CSV file

      Summary

      Trying to import a CSV file

      Product

      FileMaker Pro

      Version

      11

      Operating system version

      Windows 7 64bit

      Description of the issue

      I am trying to import a CSV file into Filemaker Pro 11. I have just started to use Filemaker.Is there a way to keep the source fields to the target fields to not change each time? I am also having trouble finding where I can match records. The field "= Match records based on this field" is not highlighted and I can't select it. What do I need to do to be able to set up matching fields?
      Thank you in advance

      Steps to reproduce the problem

      Do File Import and then try to select matching fileds.

      Expected result

      Match fields on input field criteria.

      Actual result

      I get duplicate records.

      Workaround

      None

        • 1. Re: Trying to import a CSV file
          philmodjunk

          Is there a way to keep the source fields to the target fields to not change each time?

          Create a script that uses this same tool to import your records. The script will retain your field to column mapping you specify.

          The field "= Match records based on this field" is not highlighted and I can't select it. What do I need to do to be able to set up matching fields?

          Please describe what you had done immediately previous to attempting this and also explain what results you want from this import with the matching option. Where there any records in your table yet? Did you have any records in your found set at the time you tried this? Was it mapped to a column in the csv file yet?

          • 2. Re: Trying to import a CSV file

            I really appreciate your answer. 

            I was able to create a script and this time I selected a numeric field to match on instead of First and Last name. That worked correctly.

            Maybe you can answer another question. I have about 12 fields in the table that contain dates. If the date is present in a field I name the field with 2 alphas (such as 20090112 would equal AP). I then need to move the alpha fields, without spaces for no data present, to another field in the table. Does this sound doable in a script? Would I just create intermediate fields with results and then concatenate the ones that don't have spaces? 

            Thanks

            Mark Zeserson

            • 3. Re: Trying to import a CSV file
              philmodjunk

              Huh?

              Sorry, but I don't follow what you are trying to do. How does the number 20090112 produce the letters AP? You speak of naming the field with these two letters, is this a second text field then with these values?

              "intermeditate fields and then concatenate the ones that don't have spaces" means what? What would you concatentate?

              You can certainly copy data from one field to another. Set Field will do it for one record. Replace Field contents can do it for all records in your found set. I just can't picture the details of what you are trying to do.

              • 4. Re: Trying to import a CSV file

                Below is what the input looks like. The field is the name of the field in the table, then the values.

                Field               JN                   N                       S                                AP

                Value1          19980114       20000406                                        19990304

                Value2                                                         20100304

                 

                The result would be:

                First value1               JN, N, AP

                Second value2           S

                Can I use tempporary fields in the script and then concatenate them and leave out the spaces? the fileds to hold the resul;ta is another table field.

                • 5. Re: Trying to import a CSV file
                  philmodjunk

                  JN & N & S & AP will concatenate the contents of those four fields. The results for value 1 and value 2 would be:

                  value 1: 199801142000040619990304
                  value 2: 20100304

                  I don't know if that's what you want for "concatenate" or not. It's not clear to me whether JN, N , S and AP are field names or data.

                  Calculation fields could be defined as

                  If (Not isEmpty ( JN ) ; "JN" )

                  define one for each of the 4 fields shown and you can produce concatenation calculations that combine the values (if any) returned from these calculation fields.

                  • 6. Re: Trying to import a CSV file

                    JN, N, S, and AP are field names, but they also get put in the result field. So value 1 would end up as JN, N, AP (no S) and value 2 would end up as S. Edu would be the result field. Could I do if (NotisEmpty (JN), Edu = "JN,". What this actually is is that the field names are education classes people in the United States Power Squadrons take. If they passed the class the date is put in the field. I am then creating a roster listing that has a field Edu (Education) and lists all the classes they passed. In other programming languages I have used over the years I could do Move "JN" to Edu (starting in a position). The position would be like a subscript to that field. I'll try an example below.

                    AP = 20000212  JN = 20020506  N = 20040812 for one person. The Edu field would be AP, JN, N

                    S = 20100203 for another person. The edu field would be S

                    So I need to be able to build the Edu field depending on what dates have values (NotEmpty), but need to move to the Edu field depending on what a was moved before.

                    In my old COBOL programming days I woulod do

                    IF JN not equal space MOVE "JN," to Edu (X). X would be the position to move it.

                    Thanks for all your help.

                    • 7. Re: Trying to import a CSV file
                      philmodjunk

                      What this actually is is that the field names are education classes people in the United States Power Squadrons take. If they passed the class the date is put in the field.

                       

                      In which case "value 1" is student 1 and "value 2" is student 2? Each record has a date field for each class they might take and you want a list of the classes they have taken? That's the assumption that my last suggestion waas based on. It could be further simplified to be:

                      Define the EDU field as a calcualtion field that returns text as its return type. Use this expression:

                      If ( Not IsEmpty ( JN ) ; "JN " ) & If ( Not IsEmpty ( N ) ; "N " ) & Not IsEmtpy ( S ) ; "S " ) & Not IsEmpty ( AP) ; "AP" )

                      For the first record, that returns JN N AP. For the second: just S

                      It works but is awkward to work with as searches of your data, sorts or changes to the classes offered all will create problems for you.

                      If this were my database, I would make each such piece of data a record in a related table where you have three fields:

                      StudentID, DateTaken, Class

                      And this relationship:

                      Students::StudentID = ClassesTaken::StudentID

                      For student 1, You would have three records with JN entered in Class,  with a date taken of 1/14/1998 in one record, "N" in Class with date taken of 4/6/2000 in the next record and AP entered into class with date taken of 3/4/1999. I would not use text fields for the date the class is taken, but would use date fields. All three of these records would have the same studentID as an ID number in the student table. If I needed to list all classes taken in a row, I'd deifne a calculation in students like this:

                      Substitute ( List ( ClassesTaken::Class ) ; ¶ ; ", " )

                      • 8. Re: Trying to import a CSV file

                        Thanks again for all your information. I am away for a few days so I will try these when we get back. 

                        Gor the statement: If ( Not IsEmpty ( JN ) ; "JN, " ) & If ( Not IsEmpty ( N ) ; "N, " ) & Not IsEmtpy ( S ) ; "S, " ) & Not IsEmpty ( AP) ; "AP, " ) where does the result get put, as in where does the "JN, " go?

                        I need to put the result in another table field call Edu. The previous person did this manuall because he had a previous verion of Filemaker. I like to automate things. 

                        The other table is possible. I could use the input file to populate another table. I'll try that first.

                        • 9. Re: Trying to import a CSV file
                          philmodjunk

                          Edu would be defined as a calculation field. The above expression would be its calculation.