Importing Data Into Clients & Profits

Applies to: [x] C&P Classic 3.x, 4.x [x] C&P Pro 3.x, 4.x [x] Job Tracker 3.x, 4.x [x] C&P SQL 1.x [x] My C&P! 1.x

SUMMARY: An explanation of the issues involved with importing data from your old accounting software into Clients & Profits. All versions of Clients & Profits have the ability to import data as a custom programming service. Any kind of data (whether from commercial software programs, minicomputers, or mainframes) can be imported. Data can be imported on a one-time basis (e.g., to start a new database using existing historical information) or on a routine basis (e.g., getting a "feed" from a corporate accounting system). All kinds of data can be imported. If you're converting from one system to Clients & Profits, you could export your clients, vendors, jobs, etc. as text files. These text files are imported into your new Clients & Profits database, preserving your agency's history. For technical reasons, it is unlikely that we can export your data for you. Not every software program offers the ability to export data. If your software can't export, we can't extract its data into Clients & Profits. Some accounting programs export data only with a separately-priced utility; contact the program's manufacturer for details. Don't expect a lot of help exporting your database, since many companies are less than enthusiastic about helping you switch systems. Cost and Availability Because every situation is different, there is no standardized importing utility, code, or procedure; importing is considered on a case-by-case basis, and billed at an hourly rate for time and materials. Rough estimates can be provided based on amount of data you wish to import, as well as its complexity. Importing is not an end-user feature, so is available only from Clients & Profits, Inc. The importing process from start to finish is usually time-consuming. There's a lot of coordination between our staff and your computer staff coordinating field definitions, exporting the data, practicing the import, and debugging the imported data. How long importing takes depends on: -- how much data is being imported -- how much process the exported data needs to match Clients & Profits -- how much cooperation we get from your computer or MIS staff Because of its specialized nature, expect to spend at least $1,000 exporting even a small amount of data. Exporting larger amounts of data will cost more. What Kind of Data Can Be Imported? If you can export your existing data as tab-delimited or comma-delimited text files, then the data can be imported into Clients & Profits. The flexibility of your existing system determines mostly what you can export into Clients & Profits. PC-based databases, like FileMaker and FoxPro, can easily create tab-delimited text files. Minicomputer systems and mainframes may need special programming to create PC-readable export files. The only real limitation to exporting/importing is in reconciling the files and fields between your existing system and Clients & Profits. The file structure in Clients & Profits is generally fixed, meaning new fields can't be added. This means you're existing data will need to somehow conform to the fields available in Clients & Profits. For example: If the client number in your existing system is 15-characters long, your client numbers won't fit into Clients & Profits' 10-character client number. Reconciling the two system's file formats and fields is the most difficult part of the importing process. In most cases, not all of the detail from your existing system can be imported into Clients & Profits, simply because the fields aren't included. In these cases, you will need to decide where the information can be stored (using one of the fields in Clients & Profits), or whether it can be dropped altogether. Commonly-Used Files and Fields In Clients & Profits, most fields are character-based fields. This means they can contain both letters and numbers, as well as dashes, asterisks, and other punctuation marks. All character fields can contain both capital and lower-case letters, unless otherwise noted. Number fields can contain only numbers, and not letters or other symbols. Below is a short listing of commonly-used files and their fields. When data is exported from your existing system, it must be formatted to fit into these specifications: File: CLIENTS Field Type Length Account number Character 10 (all capitals) Name Character 30 Address 1 Character 30 Address 2 Character 30 Address 3 Character 30 Contact name Character 30 Phone number Character 15 File: VENDORS Field Type Length Account number Character 5 (all capitals) Name Character 30 Address 1 Character 30 Address 2 Character 30 Address 3 Character 30 Contact name Character 30 Phone number Character 15 File: JOBS Field Type Length Job number Character 10 (all capitals) Job name Character 40 Client number Character 10 (all capitals) Description Character 300 Job type Character 10 Status Number 2 Closed Boolean 1 (a checkbox) Start date Date   Due date Date   File: JOB TASKS Field Type Length Job number Character 10 (all capitals) Task code Character 4 (all caps; must match the task table) Task description Character 40 Estimate amount Number Estimate hours amount Number   Due Date Date   Resource Character 10 Start date Date   File: TASK TABLE Field Type Length Task code Character 4 (all caps; must match the task table) Task description Character 40 Each file has more fields than listed above. A complete file and field listing is not available, due the proprietary nature of the Clients & Profits database. However, we will work closely with your technical staff to best accommodate your existing information. Where To Start The export/import process requires the work of many people in your organization. The production and accounting staff needs to first decide just what information should be exported. Then your computer staff needs to decide the best way to export the data. Finally, everyone needs to choose a date on which to break off the old system and use Clients & Profits. 1 Decide what information will be exported from your existing system. Initially, you'll need to review your existing system's capabilities with your accounting, production, and computer staff. You'll want to reach a consensus on just what information should be exported into Clients & Profits. Be sure to export only information that is accurate, as well as has value to the agency. Then, work with your computer staff (or consultant) to devise some way to create the export files. 2 Define a field list for each file. Make a detailed list of the different files that could be exported (e.g., clients, vendors, jobs, etc.). Then, for each file, describe each field and the information it contains. Be sure to include the field length and any special characteristics of the field (e.g., all caps, etc.). Also, include a record count for each file. Once reviewed, fax this specification to Clients & Profits, Inc. at (619) 945-2365. 3 Coordinate your export files with the file structure inside Clients & Profits. We will review your specifications to determine how your information best fits into a Clients & Profits database. This step typically involves several calls between our staff or your staff to reconcile the various files and fields. A cost estimate will be provided once the file specifications and record counts are determined. 4 Export the data from your existing system. The data should be exported as tab-delimited text files readable by any spreadsheet or database. 5 Clients & Profits, Inc. imports the data. 6 Clean-up the imported data. This step involves reviewing the imported data for errors, as well as establishing relational joins between files (e.g., jobs and tasks, clients and jobs, etc.). 7 The new database is returned for proofing. Once the database has been approved, it can be used for new work. Tips for Creating Export Files For best results, your export files should be created as follows: -- Your export files should be tab-delimited text files. -- Each record should end with a carriage return. -- These files should not be fixed-length records; do not pad fields with spaces or zeros. -- These files should be readable by any spreadsheet, database, or word processor. This step ensures that they are truly text files, and not an application-specific document. -- Control characters should be stripped out. -- Records counts should be reviewed after exporting. This is an important step to verify that the proper number of records were imported. Sending Your Exported Files Your exported text files can be sent on CD, Zip, or Jaz cartridges. Do not use optical cartridges or tapes, since we do not have this equipment. -- Your files can be included on PC or Macintosh disks. To compress your export files, use WinZip or Stuffit. If you use a different compression program, be sure to make your files self-expanding; otherwise, we won't be able to work with your data. Once your data is imported, it will be verified then returned on disk or cartridge. This database will be the one you'll use with Clients & Profits.