Free help: USER GUIDE
Home > Support > FAQ/Tech Index > Exporting Data from Clients & Profits

Exporting Data from Clients & Profits

Applies to:

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

Related links:


SUMMARY: How to export data from Clients & Profits to other applications.

Clients & Profits can export data to other applications, letting you send clients, jobs, costs, accounting information, and other data to databases, spreadsheets, and word processors.

This tech note explains how to export data from Clients & Profits and provides tips and troubleshooting guidance for exporting. THE CLIENTS & PROFITS HELPDESK DOES NOT PROVIDE TECHNICAL SUPPORT FOR EXPORTING DATA. Exporting data is very technical -- and is time-consuming and error-prone. It should only be performed by experienced computer users who are familiar with relational databases and spreadsheets.

Clients & Profits can export data in three ways:

1 Any report can be printed to disk, creating a tab-delimited text file.

2 All records from a selected data file (e.g., clients) can be exported using Database Utilities.

3 Selected records can be exported from custom reports.

The importance of the Data Dictionary

Successful exporting involves correctly mapping data from Clients & Profits into another application. This means, for example, that the job number field in Clients & Profits imports into an equivalent job number field in the other database or spreadsheet. All of the data that's being exported from Clients & Profits has to be mapped into an equivalent field in your other application or else the import will fail -- the data from C&P won't end up in the right spots.

The Clients & Profits data dictionary documents the files and fields available for exporting. It contains descriptions for most commonly-used fields.

Exporting data from Clients & Profits reports

Any report can be "printed to disk" instead of to a printer, which creates a text file that can be opened by any database or spreadsheet. A report can be printed to disk instead of the screen or printer by clicking on the disc icon on the top of any report window. From within Clients & Profits X, choose File > Print Destination > Export.

If a report is exported, a text file will be saved to your hard disk containing the contents of the report. The format of this text file defaults to DIF. Often changing the export format to a different format, such as to text tab-delimited, can alleviate formatting problems caused when a report is exported in DIF format, such as repeating columns, two records in the same row, etc. To change the export format go to Setup Utilities > Export Formats and choose the format you want.

When the text file is opened, the spreadsheet will be formatted with rows and columns based on the content of the report. The text file contains only the body of the report; the report's page headers, subtotals, graphics, and page footers are skipped (this is a limitation in how a C&P report can export).

When a report is printed to disk, you'll be prompted to name the report and decide where it should be saved (e.g., on your hard disk, on the file server, inside a folder, etc.). The name of the exported report (called technically a "print file") is user-defined, so it can be anything that's meaningful to you. Be sure to add a .TXT or .DIF extension to the file if you're using Windows so that it can handle the file correctly.

Once a report has been exported, the file you've named can be opened by Microsoft Excel or some other database. The report's data will be displays in a new, untitled worksheet. This worksheet can be reformatted and edited any way you desire, then saved.

Some notes regarding exported reports:

  • Changing the exported data in the worksheet doesn't affect the data in Clients & Profits.

  • Because exported reports are tab-delimited, they can't be imported into word processing documents.

  • Exchanging data is one-way; data can't be imported from an edited worksheet back into Clients & Profits.

Exporting complete data files using Database Utilities

The Clients & Profits database is comprised of over 50 individual data files. Each data file contains a particular kind of record, such as clients, vendors, jobs, job tasks, costs, G/L accounts, journal entries, etc. All of these data files are stored together in a single Clients & Profits database file on your file server (the one that typically ends with the .DF1 extension).

The complete contents of a single data file -- all of the records that it contains -- can be exported using Clients & Profits' built-in Database Utilities. The Database Utilities window features an "export" button that lets you send records from Clients & Profits to a spreadsheet-compatible text file. Unlike exported reports, which you can't customize, you can choose precisely which fields you wish to export using Database Utilities. This also makes it more complicated (and prone to user errors) since you have to do the programming. So you'll use Database Utilities for those times when you want to export the entire client file, the chart of accounts, your jobs, etc.

To export the complete contents of a data file:

1 Choose Setup > Utilities > Database Utilities.

The Database Utilities window opens, listing the database's data files.

2 Click the Export button.

The Export Data dialog box opens, prompting you to select the specific fields you wish to export.

3 Choose an export file format by clicking on the format name.

The exported data can be saved into a variety of different text file formats, including DIF, SYLK, Lotus 1-2-3, tab-delimited, or comma-delimited. Microsoft Excel, as well as most spreadsheets, will open any of these listed text file formats. The SLYK format is the most reliable way to export row/column data to Excel, and it is recommended. If the format you choose doesn't open in the spreadsheet properly, you'll need to experiment with the other formats until you find the one that works best.

4 Enter the name of the "main file" that you're exporting, such as CLIENTS.

The data dictionary lists the names of each data file. If you aren't sure about the name of a data file, you'll need to refer to the data dictionary. If the data file name isn't entered correctly, you won't be able to export the data.

5 Enter the names of the fields from the main file that you wish to export.

All fields can be selected within a main file under Export Data > Load Fields From File Format. You can also choose any number of individual fields from a main file. Enter the field names (e.g., J_NUM) that you want to export. The order in which fields are entered here determines the column they'll appear in the spreadsheet. (Once the data is imported into a spreadsheet, you can of course rearrange the columns.) The data dictionary lists the names of each field in a data file, as well as its function. Refer to the data dictionary for these field names. If the field names aren't entered correctly, you won't be able to export the data.

6 Click Start.

You'll then be prompted to name the export file. The export file can have any kind of name.

7 Enter the export file's name and choose the location where it should be saved, then click OK.

Some notes regarding exporting complete files with Database Utilities:

  • The "select using search" checkbox shouldn't be checked or else all records may not be exported.

  • Exporting data with Database Utilities is far faster than printing a report to disk.

Exporting data from custom reports

The Report Writer included with Clients & Profits Pro can export data just as easily as it can print a custom report. (Custom reports are not available in Clients & Profits Classic. If you're using this version, you'll need to export data from Database Utilities or by printing a report to disk.) The steps to create a custom report that saves export files are identical to any other custom report.

To export data using a custom report:

1 Choose Setup > Custom Reports.

2 Choose Reports > New

3 Select the "spreadsheet file" option, then click OK.

4 Double-click on the fields you wish to include in the export text file from the Select Fields window, then click Finish.

5 Enter the report's queries.

6 Click the Export button, name the text file, then click OK.

Once you've created a custom report specifically for exporting data, you can routinely export data more quickly by choosing Setup > Custom Reports then choosing Reports > Export. You'll be prompted to open your custom "export" report, confirm its selections, then save the new text file.

For answers to frequently-asked questions about custom reports, click here.

Exporting tips and warnings

  • Don't double-click on the exported text file to open it in your spreadsheet; instead, choose Open from your spreadsheet's File menu to open the text file from the application.

  • Don't modify a report's parameters. Even though "export" is an option in the report parameters, the data won't be exported correctly because the report wasn't formatted to export. Changing this option can interfere with the report and the data won't print correctly.

  • Data interchange features like publish and subscribe is not supported by Clients & Profits.

  • Importing data (except for time entries and payroll) is not recommended because of the lack of error-checking. No technical support is provided for importing data by the C&P Helpdesk.


When I double-click on the exported text file, it won't open in my spreadsheet.

Because the export file was created by Clients & Profits, the Mac OS or Windows thinks the file belongs to C&P. So when you double-click on it, the system tries to open it with Clients & Profits.

Column headings and subtotals don't appear in my spreadsheet when I open the exported text file.

Column headings, subtotals, and page footers aren't exported by Clients & Profits. Instead, only the records themselves are exported.

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