2 Replies Latest reply on Jan 19, 2016 7:46 PM by jcondle

    How to use lookup when importing data from a csv file.

    jcondle

      I am importing data from cvs file.

      The data is coded. The data comes from ambulance runs.

      There is a lookup table with the followiing

       

      Rig lookup table

      CityCode   City     EmsCode     Rig

      numb          txt        numb           txt          type

      2082         PGH     17221         Eng 16    value

       

      in the main CSV file are the following fields

      rig1 17221     imported as text but the data dictionary says they are numbers

      rig2 18221

      rig3

      rig4

       

      Ok on import I want to change the numbers to txt designations.

      I have tried setting the rig field to text and then set up a lookup on import with Rig Lookup table

      I have tried setting the rig field to numeric and then set up a lookup on import  Rig Lookup table

      It just show the rig1 code 17221, it is like nothing is executing

       

      Thanks in advance, I have been on this site and google for hours trying to figure this out.

       

      Joe

        • 1. Re: How to use lookup when importing data from a csv file.
          macwombat

          Hi Joe, its not 100% clear what you are trying to achieve, so I'll try to interpret it - if I have it wrong, I apologise.  Even if I haven't got it completely right, this might be of help to you.

           

          You are importing a field called rig1 which might have a value in it, for instance 17221.

           

          After import you would like to have a rig field that will use the value 17221 and lookup the Rig Lookup table to input the value into the rig field of Eng 16.

           

          The key to making this work will be the relationship between the main table (that you import the csv into) and the Rig Lookup table.  There needs to be a link between the two that allows the value in a record in the field:  rig1 (say 17221) to relate a record in Rig Lookup via EmsCode numb.

           

          Its important when using lookups and importing data to make sure you click the "Perform auto-enter options while importing" checkbox in the Import Options (see screenshot)

           

          Screen Shot 2016-01-20 at 1.25.15 PM.png

           

          I've attached a sample file and sample csv that show how it is setup.

           

          HTH, Chris.

          • 2. Re: How to use lookup when importing data from a csv file.
            jcondle

            I am trying to expand by FM knowledge with this.   From what you have written I think you understand.  Basicly csv file  has all the ambulance data in it.  The different fire, ambulance rigs are coded with integers that exist in a lookup spreadsheet that I turned into a filemaker table.  It is list of integers with rig names beside them.  All I am trying to do is replace the rig ID integers with their text names.  I did click the preform auto-enter button.  I was confused about what type the fields should be.  The data comes from a data dump of all 2015 data.  We are trying with filemaker to build a local database we can use for research.  Thanks for the response.  I have downloaded the zip file. I will look at and hope to have an "A Ha" moment.

             

             

            Joe