Forum Discussion

Mike's avatar
Mike
Qrew Member
4 years ago

Editing Imported Data

I imported 2000 clients who have first and last names in a single field.  But I have my name fields as 2 separate fields, first & last.  Is there ANY WAY to do a mass edit where I can automatically split the names into 2 names??  Or am I looking at hours of manual editing!?  Not a huge deal, it would be worth it.

Thank you as always!

-Mike

------------------------------
Michael Brumfield
------------------------------
Data import and export
Formulas and functions
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    np
    Make two new formula fields.

    [First Name]

    formula
    Left([Name]," ")

    [Last Name]
    formula Right([Name]," ")

    Observe if the data looks correct.

    Change each of those two fields to be a text field type.

    Observe if the data looks corerct.

    Delete the old name field.


    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------
    • Mike's avatar
      Mike
      Qrew Member
      4 years ago
      HOLY COW okay trying this now THANK YOU MARK!!!!

      ------------------------------
      Michael Brumfield
      ------------------------------
    • Mike's avatar
      Mike
      Qrew Member
      4 years ago
      It worked!!!!  You're a genius!   Wow, that is so cool -- THANK YOU, MARK!!!!

      ------------------------------
      Michael Brumfield
      ------------------------------
    • Mike's avatar
      Mike
      Qrew Member
      4 years ago
      Okay, WELL, I was going to try to implement this strategy with the addresses too because that's the other issue -- names and addresses -- the addresses were imported as "127 Washington Street, Naperville, IL, USA" -- do you know of way to get the address to transfer as well?? I had to pick either 1st street, 2nd street, city, state or zip to import it to, so I created it's own "import address" field, but now I'm not sure what to do with it.

      ------------------------------
      Michael Brumfield
      ------------------------------
      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        4 years ago
        Parse out the separate field using formulas and then set up a saved table to table copy to do a "one time" merge" of the the table into itself on record ID and map the formula fields into the real address fields.


        I assume that you have an empty address field and then  some field that has the imported data.  If you threw the whole string into Street one for example then the field list when you are building a formula will include that sub field.  

        You can user the part formula to extract out the parts.

        Part([Import address: Street1],1,",")

        the 1 means get the first part from the left, as defined by the comm as a separator.  So that would be the Street.

        Part 2 would be the next part so that would be the city.


        ------------------------------
        Mark Shnier (YQC)
        Quick Base Solution Provider
        Your Quick Base Coach
        http://QuickBaseCoach.com
        mark.shnier@gmail.com
        ------------------------------