If you have your customer's data in a Microsoft Excel spreadsheet you can import all the main information. This article will show steps to take in order to import customers from Microsoft Excel into CLIP. We will assume you are new to CLIP and do not have customers already setup.
- Open Microsoft Excel with the sample template we have provided either at the bottom of this article or click here. Basically, you will need to get your existing customers information into the Microsoft Excel spreadsheet provided.
- Insert a blank row above row number 1. To do this, right click the mouse on the number 1 in the upper left hand corner then select "Insert".
- Starting with the column labeled A, find the column that has the customer number.
- If you do not have one from your old data you will need to create one. Do this by right clicking your mouse at the top of column A and select "Insert". Type the word "ID" in the AI field. In the A2 field, type the number 100. In the A3 field, type the following formula "=sum(A2+1)". Using the small square box in the lower right corner of the A2 field, click and hold down your mouse button and slowly drag the box down the A column until you reach your last customer and then let go of the mouse. Now each customer should have it's own number starting with the number 100.
- If you have an existing customer number (must be a number and smaller than 100,000), find the column that it is located in and right click on the letter at the top of the column and select Cut. Then go to the column labeled A and right click on the top of the column and select Paste. The numbers should all be in the first column now.
You will need to make or move your data into the following columns. Every column must be made even if you don't have any information for it. The columns must be in the exact order as shown in the template. You must put the name of each column across the number 1 row. This will create a placeholder if you do not have data for that column. Legend: D = Date Format 01/01/2015 C = This can contain letters and numbers N = This can only contain numbers The number after the (C) or (N) tells how many characters that field can hold Column A= ID (This is the customer number, this is a mandatory that a unique number be filled in)
Column B= Property Name (where the property actually is located. Normally this will be Last name, First name for residential-what the crews will see)
Column C= Bill Name (Use normal name format First & last name)
Column D= Bill Care of (If an extra address line is needed otherwise Clip will ignore blanks when printing)
Column E= Bill Address (Billing street address)
Column F= Bill City and State (You will need to combine the two fields together if you have them separate. To do this in Microsoft Excel, move the City to Column X and Move the State to Column Y. In the F2 cell type this formula "=X2&”, “&Y2". Now drag the cell down to fill in the column all the way to the bottom. Column G= Bill Zip
Column H= Tax ID (This will normally be the same as the State-This is a mandatory field)
Column I= Property Address Column J= Property City Column K= Property State Column L= Property Zip Column M= Home Tele Column N= Work Tele Column O= Fax number Column P= Email Address Column Q= Credit Limit (This must be a number-if you do not want to use this feature put 99999 in all customers fields) Column R= Service Charge (This must be either a Capitol Y or N- This is a mandatory field) Column S= User Defined Date (Optional date) Column T= Custom Characters (Optional info from your excel sheet that is not cover with one of the other fields- up to 10 characters long) Column U= Type (Optional to tell what type of customer ie I-irrigation, M=mowing –up to 5 characters long) Column V= R or C ( Must have either a capitol R or C- This is a mandatory field ) Column W= WP Code (Optional info from your excel sheet that is not cover with one of the other fields- up to 10 characters long) Column X= Customer’s cell phone, or pager number (15 characters max). Column Y= Goes into the customer’s “More phone numbers tab as phone #1 (15 characters max) Column Z= Goes into the customer’s “More phone numbers tab as phone #1 (15 characters max) Column AA= Date that the customer started being a customer Column AB= Last date that the customer had something happen (use century date 8 character date) Column AC= Map longitude code. Column AD= Map latitude code. Column AE= Map code if you are using the map numbers from your map. Column AF= Customer’s default Programs and Rounds chemical square footage. Column AG= Customer’s Default crew for Programs and rounds. Column AH= Customer’s default routing number for Programs and Rounds. Column AI= Customer’s default crew for jobs. Column AJ= Customer’s default route number. Column AK= Customer’s statement note (50 Characters long), Column AL= Customer’s User defined field 1 no numbers. Column AM= Customer’s User defined field 2 no numbers. Column AN= Customer’s User defined field 3 no numbers. Column AO= Customer’s User defined field 4 no numbers. Column AP= Customer’s User defined field 1 only numbers. Column AQ= Customer’s User defined field 2 only numbers. Column AR= Customer’s User defined Date type field 1 only numbers and /’s in century format (use century date 8 character date). Column AS= Customer’s User defined Date type field 2 only numbers and /’s in century format (use century date 8 character date). Column AT= Customer’s User defined Date type field 3 only numbers and /’s in century format (use century date 8 character date). Column AU= Customer’s User defined Date type field 4 only numbers and /’s in century format (use century date 8 character date).
Once the Microsoft Excel sheet is configured properly, save the file as a .csv file format. In order to do this, in Microsoft Excel, click File then Save As. At the bottom, change the "Save as type" to CSV (comma delimited *.csv format). Change the name of the file to "customer" and save it in the C:\CLIPv folder.
Close Microsoft Excel.
Open CLIP, click Files, Other Utilities, Import from Excel then select "Yes".
Locate the Excel file you made, select "Ok" once you have found it.
CLIP will start the import process. The amount of customers you have will determine how long it will take.
Go into File Maintenance / Customer List and check the data. If any data is in the wrong place you will either need to go through and manually correct it or correct the Excel sheet. If this was a new CLIP program just installed, erase / uninstall and reinstall the CLIP program then run the import program again. If you were adding customer to a copy that already has information in it you will need to restore your back up and rerun the import process.
Tips for New Users—Keyboard Usage, Conventions and Terms
If you are already familiar with the Windows Operating system, you can skip this section. If you are new to computers and software, please take a few minutes to read the following. It will help you understand the rest of this manual.
The <Enter> key is the same as the <Return> key on some keyboards. The <PageUp> and <PgUp> keys are the same. The <Page Down> and <PgDn> keys do the same function.
The <Control> (or <Ctrl>) key is called a "modifier." It is only used in conjunction with another key. Holding it down as you press another key modifies the regular function of the second key.
Example: If you have text selected and hold the <Ctrl> key down and press the letter <C>, the computer will interpret it as the "copy text" command and will place the selected text on your Windows CLIPboard.
The <Alt> key is another modifier used only in conjunction with another key.
When the word "Click" is used in this manual it refers to pushing your left mouse button. You may also tab (or use the arrow keys) to the desired field and press <Enter> to accomplish the same function.
You will notice as you move around in CLIP that several different keys often function similarly. For instance, in several screens you may select an option by using your arrow keys or <Tab> to move the highlight to that option and press <Enter>. Or you may just type in the letter that is underlined in that option while holding the <Alt> key down to move directly into the next screen (such as “A” to Add Customer in File Maintenance).
When you are in edit mode you should always use the <Tab> key to move from field to field. A mouse is generally a slower method of selecting an item, but if you are only making a few edits to a screen, it is often faster to use a mouse to jump down to a field than it is to use your <Tab> key.
You can find where you are on the screen by locating the blinking vertical line. This line is called a cursor.
Answering questions CLIP asks:
Quite often, CLIP will give you a suggested (default) answer in a field where it is asking you to enter information. This is to speed your work along by allowing you to just press <Enter> rather than clicking or typing.
Let us know if you need further assistance.