spacerspacer
Free help: USER GUIDE } WHAT'S NEW
Home > Support > FAQ/Tech Index > Importing Data into Clients & Profits
spacer


Importing Data Into Clients & Profits



Applies to:

[x] C&P Classic
[x] C&P Pro
[x] Job Tracker
[x] Enterprise
[x] My C&P!

See also:
C&P System Manager News



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 the amount of data you wish to import, as well as its complexity. Importing is not an end-user feature, so it is available only from Clients & Profits Worldwide

The importing process from start to finish is usually time-consuming. There's a lot of coordination between our staff and your computer staff regarding 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 processing 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 that your 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 systems' 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 Worldwide at (760) 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 and 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 Worldwide 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.

-- Record 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-ROM, or uploaded via our FTP server.

-- Your files can be included on Macintosh or PC formatted CD-ROMs.

To compress your export files, use Stuffit or WinZip. 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.



For subscribers: VIDEOS } FAQs/TECH NOTES } LEARN
     Sign up for Clients & Profits news:
     © Clients & Profits, Inc. w e  l o v e  a d v e r t i s i n g