1 Reply Latest reply on Jul 9, 2010 8:49 AM by philmodjunk

    Importing CSV and Manipulating Data Imported

    Solution69

      Title

      Importing CSV and Manipulating Data Imported

      Post

      hi, I am new to FM and so far its day 2 of it being installed at work.  I am trying to ease my pain with working alongside csv files and have managed to get data to import nicely into filemaker.

      I could however use some help from the community here.

      lets say i have a set of fields

      field1 is called retail_price

      field2 is called cost_price

      field3 is called product_name

      field4 is called xport_prod_name

      for field 2, if I have an imported data of n/a is there a way for me to set it so that field 1 + xx%

      additionally for field 4, I would like to format it so that field4 = field3 however the text is formatted differently and all spaces are converted over to a dash "-" i.e. this-had-some-spaces-but-now-has-dashes

      also, lastly is there a way to automate this with a script for each csv file i import in the future so that way i can click a button and have it perform this for me?

      kind regards,

      solution69

        • 1. Re: Importing CSV and Manipulating Data Imported
          philmodjunk

          "for field 2, if I have an imported data of n/a is there a way for me to set it so that field 1 + xx%

          additionally for field 4, I would like to format it so that field4 = field3 however the text is formatted differently and all spaces are converted over to a dash "-" i.e. this-had-some-spaces-but-now-has-dashes"

          Both of these can be done with auto-entered calculations set on field 2 and field 4. To define an auto-entered calculation, find the field's definition in Manage | Database | Fields, double-click it, select the auto-enter tab and click the calculation option.

          For field 2, you could use: If ( self = "n/a" ; "1 + xx%" ; self )
          For field 4, you could use: Substitute ( self ; "-" ; " " ), however, this will also replace any hyphens that weren't originally a space with spaces also. Since you want field 3 to equal field 4, you may want to just discard this field and use field 3 in its place.
          For both of  these auto-enter calculations, also clear the "do not replace existing value..." check box.

          To see these auto-enter calculations do their job during import, you have to select the check box that enables all auot-enter options during import. If you have any other fields that are set up with auto-enter options that you don't want to take place during import, you'll need to use a slightly different approach that uses Replace Field contents to update them after the import has taken place.

          "also, lastly is there a way to automate this with a script for each csv file i import in the future so that way i can click a button and have it perform this for me?"

          Imports can be scripted, you'll find a step for importing records with all the same options as you see when you choose import records. You can even calculate a file path and use it in a variable to specify a file location if you need to. There are a lot of options so it's a good idea to read up on import records in the help system and to make back up copies just before testing your script so that you can recover easily from an import script that doesn't do what you expected.