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.
TROUBLESHOOTING
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.
