Wednesday, September 19, 2018

Separate the Firstname and Lastname on the CSV File

User has contact records that does not have a First Name and Last Name. When user created the record, the Full Name was entered in the Contact Name field. User wants to update the First Name and Last Name of the records.
To accomplish this, separate the Firstname and the Lastname through the use of a formula in Excel then perform a CSV Import to update the records.

First step is to export the Contact list from NetSuite.
     1. Navigate to Lists > Search > Saved Searches > New.
     2. Select Contact.
     3. In the Criteria tab > Standard sub tab, set required filters (e.g. First Name is empty)
     4. In the Results tab > Columns subtab, click Remove All.
     5. Add Name.
     6. Click Preview.
     7. Click Export - CSV.
     8. Save the CSV file.

Second part is to separate the First name and Last name on the CSV file.
     1. Open the CSV file.
     2. Create two new columns for the First Name and Last Name.
     2. For the First Name, use the formula =LEFT(A1,FIND(" ",A1,1)-1).
     3. Replace A1 with the cell where the Contact Name is located.
     4. For the Last Name, use the formula =RIGHT(A1,LEN(A1)-FIND(" ",A1,1)).
     5. Replace A1 with the cell where the Contact Name is located.
     6. Save the CSV file.

Third part is to reimport using the CSV file.
     1. Navigate to Setup > Import/Export > Import CSV Records.
     2. Select Relationships in the Import Type field.
     3. Select Contacts Only in the Records Type field.
     4. Click Select.
     5. Locate the CSV file, then click Open.
     6. Click Next.
     7. Click Update for Data Handling.
     8. Click Next.
     9. In the Field Mapping section, set the following:

     10. Click Next.
     11. Provide an Import Map Name.
     12. Click Save & Run.


No comments:

Post a Comment