1 Reply Latest reply on Jun 6, 2014 6:45 AM by philmodjunk

    Importing from Excel, with field changes?



      Importing from Excel, with field changes?


           I need to import periodically from an Excel spreadsheet, but the data in the spreadsheet is not in exactly the same format I want in FMP.  For example, spreadsheet has an address of "123 N Main St" and I want this to go into FMP table as street number "123" and street name "N Main St". 

           Is there a way I can automate this in FMP or must I massage the spreadsheet first and create additional columns?

           (I am using FMP 13.  I have not used FMP since v4, so am very rusty!)

        • 1. Re: Importing from Excel, with field changes?

               Option 1

               Import the data as it is found in Excel and use Replace Field Contents with the calculation option to move data from the combined address text field into separate fields for each part of the address.

               Option 2

               Map this column to a global text field. Set up the separate address fields as auto-enter calculations that extract the correct text from this field. When you import, be sure to click the check box for enabling auto-enter options during import.

               The calculation for street number might be:

               LeftWords ( AddressField ; 1 )

               The one for the rest of the text might be:

               RightWords ( AddressField ; WordCount ( AddressField ) - 1 )

               these calculations can be used in either option, but assume a consistent data entry format used in entering the data that you are importing. If the data is not in a consistent format, it may not be possible to come up with calculations that automatically parse every possible address into the separate fields.