How to Manipulate an Export File to Import into TLS Requirements: Microsoft Excel Experience Advanced Computer Skills 1. Export your client information from your current database program to Microsoft Excel in an ASCII Comma Delimited Format. *If you need assistance exporting from your current database program, please contact customer support for that program. 2. Open your data file in Microsoft Excel. 3. A wizard box will usually appear asking about the format of your data file; select the Delimited option and click Next. You will now have to select the delimiter options that will best separate the data that is displayed in the Data Preview box, and click Finish. 4. After your file is brought into Excel, you will need to arrange your data in the columns and fields that TLS requires using the following field listing: Import fields to be used and their order . . . CLIENT Cell Name Field Name Example Data A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX HONORIFIC_TITLE (Must Be Completed) FIRST_NAME (Must Be Completed) MIDDLE_INITIAL LAST_NAME (Must Be Completed) SUFFIX NICKNAME CLIENT_DESIGNATIONS (Comma OK) _SOCIAL_SECURITY BIRTH_DATE SEX_M_OR_F MARITAL_STATUS SMOKER SEND_MAIL_TO PRIMARY_ADDRESS (Comma OK) PRIMARY_BOX PRIMARY_CITY PRIMARY_STATE PRIMARY_ZIP PRIMARY_COUNTRY (Other Than U.S.A.) _HOME_PHONE _HOME_PHONE_2 _MOBILE_PHONE _OTHER_PHONE _PAGER HOME_FAX PERSONAL_EMAIL FIRM_NAME (Comma OK) JOB_TITLE_DESCRIPTION FIRM_STREET_ADDRESS (Comma OK) FIRM_OTHER_ADDRESS (Comma OK) FIRM_CITY FIRM_STATE FIRM_ZIP FIRM_COUNTRY (Other Than USA) FIRM_PRIMARY_PHONE _FIRM_PHONE_EXTENSION _BUS_PHONE_2 _BUS_PHONE_2_EXT _BUS_FAX _BUS_FAX_2 _BUS_MOBILE _BUS_MOBILE_EXT _BUS_PAGER _BUS_PAGER_EXT _BUS_EMAIL ADVISOR_1_NAME (Comma OK) ADVISOR_TITLE LETTER_CLOSE (Comma OK) MARKETING_SEQUENCE MARKETING_DATE Mr. Jonathan T Sample Jr. Jon CFP 111-11-1111 4/15/40 M Married N Client/Prospect Business 111 E. Avenue, Unit 5 P.O. Box 111 Dayton OH 45402-4300 Field Size Limit 10 20 1 30 10 15 20 12 8 1 10 1 25 50 20 30 2 15 10 937-382-5555 20 20 20 20 20 937-867-3433 20 [email protected] 50 Organization Masters, Inc 100 President 35 120 Main Street, Ste. 100 50 50 Dayton 30 OH 2 45402-4300 15 10 937-444-9000 20 504 5 20 5 937-444-9100 20 20 20 5 20 5 50 Edwin P. Morrow, CFP 50 President 45 Sincerely, 20 Individual Prospect 30 1/1/00 12 AY AZ SEMINAR_GROUP_NAME SEMINAR_DATE 50 12 SPOUSE Cell Name Field Name Example Data Field Size Limit BA BB BC BD BE BF BG BH BI BJ BK BL BM BN BO BP BQ BR BS BT BU BV BW BX BY BZ CA CB CC CD CE CF CG CH CI CJ HONORIFIC_TITLE FIRST_NAME MIDDLE_INITIAL LAST_NAME SUFFIX NICKNAME _SOCIAL_SECURITY BIRTH_DATE SEX_M_OR_F SMOKER DESIGNATIONS ANNIVERSARY_DATE _HOME_PHONE _HOME_PHONE_2 _MOBILE_PHONE _OTHER_PHONE _PAGER PERSONAL_EMAIL FIRM_NAME (Comma OK) JOB_TITLE_DESCRIPTION FIRM_STREET_ADDRESS (Comma OK) FIRM_OTHER_ADDRESS (Comma OK) FIRM_CITY FIRM_STATE FIRM_ZIP FIRM_COUNTRY (Other Than USA) FIRM_PRIMARY_PHONE FIRM_PHONE_EXTENSION _BUS_PHONE_2 _BUS_PHONE_2_EXT _BUS_FAX _BUS_FAX_2 _BUS_MOBILE _BUS_MOBILE_EXT _BUS_PAGER _BUS_PAGER_EXT Mrs. Anabelle T Sample 10 20 1 30 10 15 12 8 1 1 20 8 20 20 20 20 20 50 35 35 50 50 30 2 15 10 20 5 20 5 20 20 20 5 20 5 Annie 222-23-3444 4/15/35 F N CPA 12/3/65 [email protected] Montgomery School Principal 1200 Vandalia Pike, #8 Dayton OH 45405 937-456-8767 Note: There are only three columns that must be completed to import your data file (column A, B, and D). The remaining columns may be left blank. Commas are acceptable in the indicated fields only and they must not be typed in at the beginning of a field. Preparing the Cells The first thing to change on your spreadsheet is the width of the data cells so they are easier to read. To change the width of the cells, click on the blank corner box between Column A and Row 1 (see figure below). Once you do this, the entire spreadsheet should be highlighted. Go to Format-ColumnAutoFit Selection. After the column width adjusts, click anywhere on the spreadsheet to de-select the cells. Arranging your Data in Excel The following information is provided to assist you with arranging your data into the proper columns that TLS recognizes. For further information on using Microsoft Excel, please consult the Help menu in Microsoft Excel. Inserting a Column - Place your cursor on the column header to the right of where you would like to insert a new column. Right-click on your mouse and choose Insert. For example, if you would like to insert a column between C and D, place your cursor on the column header with the letter D, right-click and select Insert. Deleting a Column - Place your cursor on the column header you want to delete. Right-click on your mouse and choose Delete. For example, if you would like to delete column D, place your cursor on the column header with the letter D, right-click and select Delete. Continued on next page… Separating Data in a Column - If you have data that is combined into one column, you can separate this data by using the Text to Columns feature in Excel. First, you will need to Insert the appropriate amount of columns needed for your data. Your columns should be inserted to the right of the column that you are separating. Then, you need to click on the header (letter) of the column that you want to separate and go to Data-Text to Columns. A wizard box will usually appear asking about the format of your data file; select the Delimited option and click Next. You will now have to select the delimiter options that will best separate the data that is displayed in the Data Preview box, and click Finish. Find/Replace Data - You can edit specific text within your spreadsheet and replace it automatically by selecting Edit-Find. You can do this for your entire spreadsheet or a specific column. In the Find what: box, type in the incorrect text you wish to edit and click Replace. Next, type in the corrected text in the Replace with: box and click Replace All. For instance, if you have Mr listed in the Honorific column of your spreadsheet without a period, you can edit all of these entries at once by typing in Mr in the Find What: box. In the Replace with: box you should type Mr. and click Replace All. 5. Once your data has been arranged in the correct order, you will need to save your file. Click on File-Save As. The Save As type should read Comma Delimited (csv). Please remember to make note of your file name and its location. Once your file has been saved and Excel is closed, you will be ready to import into TLS. 6. Open TLS. (If you are using a network version, you will need to log in as the administrator.) Click on File-Close. 7. When your client information screen is closed, click on File-Get External Data-Comma Delimited Data. 8. TLS will now prompt you to backup your files. You may indicate Yes or No to the backup depending on your choice. We do recommend that you perform the backup if you currently have data in your system. 9. When your backup is completed, TLS will prompt you to locate your Excel import file. After your file has been located, you will receive the following message letting you know that the import process is about to begin. Click OK to begin, or Cancel to quit the import routine. 10. Once you click OK, your files will begin to import into TLS. When completed, you will receive a message that the import session was completed successfully. Click OK and go to File-Open to return to the Client/Prospect screen in TLS.