Importing customer list into CLIP from Excel
1. Open Excel
2. You will need to get your customers information into the Excel sheet.
3. Insert a blank row above row 1. To do this Right Click your mouse on the number 1 in the upper left hand corner. Then select “Insert”
4. Starting with the column labeled A find the column that has the customer number in.
a. 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 the A column and select insert. Then type the word ID in the A1 field. Then in the A2 field type the number 100. In the A3 field type the following formula =sum(A2+1). Then 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 then let go of the mouse. Now each customer should have it’s own number starting with the number 100.
b. If you have an existing customer number (must be a number and up to 99,999) 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 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.
5. 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 You must put the name of each column across the number 1 row.
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 EC= 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 Excel, move the City to Column X and Move the State to Column Y. In the F2 cell type this formula =X2&”, “&Y2. Use the described method above to drag the formula down to all the customers.
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)
6. Save the file as a .csv file. Go to File-Save As. At the bottom change the “Save as type” to CSV (comma delimited)(*.csv). Change the name of the file name to customer and save it in the C:\Clipclas folder.
7. Close Excel
8. Open Clip
9. Go to Files-Other Utilities- Dot prompt
10. Type Do qb_cimp with “PT”
11. Hit Enter
12. Click Yes to having a backup- If this is a brand new install you do not need to have a backup yet otherwise you must make a backup first
13. The process will automatically start. This could take some time. When it is done you will be returned to the command line.
14. Hit Escape
15. Go into File Maintenance and check the data
16. If any data that is in the wrong places you will either need to go through and manually correct it or you will need to correct the excel sheet. If this was a new Clip just installed, erase and reinstall the Clip program then run the import program again. If you were adding customers to a copy that already has information in it you will need to restore your back up and rerun the import process.