How To Create An Export Report - Delimited

For this example, we will use a Common Separated Value, or CSV report, but Collect! is capable of creating a simple ASCII text file that may be read by many different programs. It is very useful to be able to produce this type of file with data from your Collect! database. Then the information may be read in a Spreadsheet program, or used with mail merge for printing labels or specially formatted letters in a Word Processing program.

Collect! supports most comment delimeters like: , ; | (@>)

A CSV report needs:

• Column Headings to divide the data into columns.
• Printable Information from the database to list under each heading
• Report Options set to print to Spreadsheet or Other with a *.csv filename in the Path field.

This document shows a report snippet that may be used to create a CSV report. Explanations are given for each part of the report.

Column Headings

The report snippet shown below prints a line of headings before going into a Contact and a Debtor loop. There must be a heading for each field that you are including in the report. Check whether or not the software you are exporting requires the headings to be unique.

Two uses of these headings:

• If you open your report's output in a Spreadsheet program, these headings will be the names of the columns in your Spreadsheet.
• These names will be displayed in your Word Processing program, if you create a template using this report's output as the data source.

Fields are taken from the Debtor form and columns are named so that they match the field names. You may want to use different headings and different fields in your CSV report. This is only an example of the possibilities. We are using no line feed to organize the data, but you can place the entire header and output on a single line.

@no line feed

"Unique Identifier",
"First Name",
"Last Name",
"Courtesy Title",
" Address 1",
"Address 2",
"City",
"State",
"Zip",
"Suffix",
"Country",
"Business Phone",
"Business Fax",
"Home Phone",
"Home Fax",
" Email Address",
"Owing",
"Account",
" Client Name"
@line feed

Each heading is placed on a separate line and the entire list is enclosed in the @NO LINE FEED and @LINE FEED command.

It is not necessary to wrap all headings and fields with quotes, but it must be done on any fields that may contain commas in the data line Name, Address, or any Currency Fields.

If your third party software does not need a header row, you can exclude it.

This completes the Heading section.

Printable Information

Next, the report must have a way of pulling information from your database. This will depend on your requirements. As an example, this report uses a Contact loop to look for In Progress Letter contacts due today or earlier. Then it uses a Debtor loop to check for Address OK on each debtor.

@co where (@co.do = no) where (@co.ty = letter) where (@co.dd <= @d)

@de where (@de.ao = x) where (@de.fi = @co.fi)

@no line feed

"@de.fi",
"@de.na",
"@de.na",
"@de.le",
"@de.ad<",
"@de.a1<",
"@de.ci",
"@de.st",
"@de.zi",
"@de.ge",
"@de.cou",
"@de.po",
"@de.ce",
"@de.ho",
"@de.ot",
"@de.em",
"@de.ow",
"@de.ac",
"@de.cl"
@line feed

@de

@co

To use other delimiters, simply replace the commas with the delimiter you wish to use.

As in the Heading section, each field code is placed on its own line, enclosed in the @NO LINE FEED and @LINE FEED. The entire list is enclosed in the CONTACT and DEBTOR loops.

It is important to note how the lists are constructed. Each value is placed on its own line and the entire list is enclosed in the @NO LINE FEED and @LINE FEED command. Before the @NO LINE FEED there is no blank line. After the @LINE FEED there is always a blank line.

Copy the entire snippet into a new Report Definition form to try it out. Then set the report's Report Options.

Report Options

Before printing the CSV report, options may be set in the report's Report Options form.

To access the report's Report Options, select the OPTIONS button when you are viewing the Report Definition form for your report. Then set the following options.

Destination

Select Other as your Printing Destination. Other does not alter your formatting.

The destination of Spreadsheet can be use if you format the header and output properly. If you place the header on one line, there needs to be a minimum of 4 spaces between each column header. For the output on a single line, Collect! will treat each field like a column. Whether you use the no line feed option like above or place the header and output on one line, you must remove the quotation marks and commas. Collect! will automatically fill them in. In Spreadsheet mode, Collect! will only output CSV.

File Name

Enter a path and filename for the report's output. With a destination of Other, you can use any file extension that your third party software supports. With a destination of Spreadsheet, the filename should have a *.csv extension. A relative path may be entered here, for example,:

.\styles\mailmege.csv

Redirect To

If you selected OTHER for your printing destination, you have the option of redirecting your file to another program for processing. Enter a valid name and path for that other program's template here. Please refer to How To Mail Merge From Collect! To Word for a detailed example using this feature.

Summary

An export report may be read by many different programs. Produce this type of file with data from your Collect! database and use it in a Spreadsheet or Word Processing program.

An export report needs:

• Column Headings to divide the data into columns (if your program needs them).
• Printable Information from the database to list under each heading
• Report Options set to print to Spreadsheet or Other with a *.csv filename in the Path field.